ORA-00942:表或视图不存在(当使用单独的sql时有效，但在oracle函数中不起作用) [英] ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)

问题描述

当我有一个像select * from table1这样的sql语句时，它很好用，但是一旦将其放入函数中，我就会得到:

When I have a sql statement like select * from table1, it works great, but as soon as I put it into a function, I get:

ORA-00942: table or view does not exist 

如何解决这个问题?

推荐答案

您可以查看以下几项.根据您的问题，函数所有者似乎与表所有者不同.

There are a couple of things you could look at. Based on your question, it looks like the function owner is different from the table owner.

1)通过角色进行授予:为了在另一个用户的对象上创建存储过程和函数，您需要直接访问对象(而不是通过角色进行访问).

1) Grants via a role : In order to create stored procedures and functions on another user's objects, you need direct access to the objects (instead of access through a role).

2)

默认情况下，存储过程和SQL方法与 所有者(而不是当前用户)的特权.

By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user.

如果您在模式A中创建了一个表，在模式B中创建了该功能，则应该了解一下Oracle的Invoker/Definer Rights概念，以了解可能引起此问题的原因.

If you created a table in Schema A and the function in Schema B, you should take a look at Oracle's Invoker/Definer Rights concepts to understand what might be causing the issue.

http://download.oracle. com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809

这篇关于ORA-00942:表或视图不存在(当使用单独的sql时有效，但在oracle函数中不起作用)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！