CODEWITHSUNDEEP
oracle-databasestored-procedures
user509925
user509925

Reputation: 101

oracle "table or view does not exist" from inside stored procedure

the scenarion is like this...

I HAVE a namespace XXX where I created some tables and some stored procedures...

THEY HAVE a namespace YYY where they created some tables...

THEY GRANTED XXX access to their tables, so when I conect to SQL Developer using XXX connection, I can do: 

SELECT * FROM YYY.TableA

But if I try to run that very same statement from inside a stored procedure (either simple stored procedure or a package), the stored procedure does not compile. It happens to a lot of sp's. Is there any other permission that I have to ask for??? I'm running sp's like this:

CREATE OR REPLACE PROCEDURE PRC_SOMESP(
) AS BEGIN
END PRC_SOMESP;

Procedures that does not access YYY tables do compile well.

Thanks in advance.

After Justin Cave response, I'm trying to add "AUTHID CURRENT_USER" sentence to the sp's but getting the same "table or view does not exist" result: 

CREATE OR REPLACE PROCEDURE PRC_PROC1( PARAMETERS... )  
AUTHID CURRENT_USER  
AS  
    MYVAR NUMBER;  
BEGIN  
    STATEMENTS...
END PRC_PROC1;  

CREATE OR REPLACE PACKAGE PKG_PROC2  
AUTHID CURRENT_USER  
AS  
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE PRC_PROC2( PARAMETERS... )  
END PKG_PROC2

Should I check anything else???

Upvotes: 10

Views: 32093

Answers (4)

user3682728
user3682728

Reputation: 465

In my case

THEY HAVE a namespace YYY where they created some tables... THEY GRANTED XXX access to their tables,

But for YYY.tables they don't grant table insert's right for XXX enter image description here

So I can query that table but when compile package will have an error in the stored procedure. ora-00942 table or view does not exist.

Omg how can I came across this 11 years, 5 months ago 's problem, but anyway wish my answer maybe useful for someone who just has the same problem as me.

tldr; please check the target table 's privileges for XXX user,try add Insert priv.

Upvotes: 0

jbgisser
jbgisser

Reputation: 1

I just had this same problem. I'm not a DBA but the way it was explained to me is "The basic thing is that your personal role privileges aren't in effect within the stored procedure."

I was advised to qualify the name of the SP with the owner of tables, like this:

CREATE OR REPLACE PROCEDURE yyy.PRC_PROC1( PARAMETERS... ) etc

This worked in my case in my dev environment. My environment only has one namespace so I'm not sure this will solve the OP's question but hopefully will help to move this issue forward for the next 18K people that look up this question ;-).

Also, when I put my SP into production, I will need to remove the qualifier and our installation software will run create under the proper authority.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Most likely, the problem is that the grant was done via a role. Privileges granted to a user are not available in a definer's rights stored procedure (the default).

In SQL Developer, it is relatively easy to verify that this is the problem. If you run the command 

SET ROLE none

and then run the SELECT statement, I would expect that you would get the same ORA-00942 error.

Assuming that is the case, the solution would generally be to ask that the owners of the tables in the YYY schema to grant access to the tables directly to you rather than granting access via a role. Barring that, you could define your stored procedure as an invoker's rights stored procedure by adding AUTHID CURRENT_USER to the declaration. That would that the caller of the procedure would need to have access to the underlying objects but it would allow your procedures to make use of privileges granted through a role.

If you want to create an invoker's rights stored procedure, you will also need to refer to the table name using dynamic SQL in order to defer the privilege check to runtime. So you would have something like

CREATE OR REPLACE PROCEDURE PRC_SOMESP 
  AUTHID CURRENT_USER
AS 
  l_cnt pls_integer;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM yyy.TableA' INTO l_cnt;
END PRC_SOMESP;

if you wanted an invoker's rights stored procedure that queried the TableA table in schema XXX.

Upvotes: 15

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

In your stored procedures on schema XXX if you access tables from YYY schema, make sure you fully qualify them:

select count(1) from YYY.TableA;

Other thing to consider is casing (in case you are mixing upper and lowercase in your Oracle identifiers).

One last thing: post the error you are getting. It will be easier to help you that way.

Upvotes: 0

Related Questions