Ora-00942: Table or View Does Not Exist (Works When a Separate SQL, But Does Not Work Inside a Oracle Function)

ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)

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) 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)

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

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: table or view does not exist' only when running within a Stored procedure

Sounds like an issue with select privileges granted via a role, rather than directly to the schema. See ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function).

PL/SQL: ORA-00942: table or view does not exist. Inside Function

The table you're selecting from belongs to user hawk.

User, which uses the function, should be granted the SELECT privilege (on that table). I presume that you did that (as SELECT itself works OK, but not as part of a function) - via some role. However, that won't work - you should grant the privilege directly to user, not via role.

PL/SQL: SQL Statement ignored, PL/SQL: ORA-00942: table or view does not exist

Line 49 refers to ds.md_account_d. The package belongs to DMA, so probably DS needs to

grant READ on MD_ACCOUNT_D to DMA;

along with any other privileges that are needed.

PL/SQL: ORA-00942: table or view does not exist V$SQL

Database dictionary related or system tables (v_$sql in this case) are owned by Oracle sys user and needs special privileges to access them. You need to login to oracle database as sysdba user or get those privilages (your DBA might help you with this) to get access for the data dictionary views.

As mentioned in this article

The problem is that procedures don't respect roles; only directly granted rights 
are respected. So, that means that table_owner has to regrant the right to select

So, try the following to grant the SELECT on all dictionay view so that you can use it in your pl/sql blocks.

grant select any dictionary to USERNAME

---

Related Topics