I'm working with oracle 11g R2.


I am unable to preform dynamic SQL in SchemaB package against SchemaA tables. SchemaB is being called by the owner of SchemaB.


I have a package/procedure that has the rights to preform a selection statement on another schema's table.

So I can call the below with no issues.

select * from SchemaA.TableA where blah

I need to be able to run this call from dynamic SQL, because the table from SchemaA could be any of 10. I created the below SQL. I get an error

ORA-01031: insufficient privilegeswhen I run the same statement but withexecute immediate


v_query varchar2(2000); 
v_query := 'select id, value1, value2 from SchemaA. ' || TableName || ' where id = ' || id;

execute immediate v_query ;

I know that the package does not get the user roles, so I even put the following code into the signature of my package AUTHID CURRENT_USER ; which changed nothing.

What other permissions do I need to set in order to do this?

OR if you have another solution for dynamic table selection, that would be great too.

  • Are you saying that you can do the static SQL statement in the procedure successfully? Or that you can do the static SQL statement outside the procedure at the command line successfully? Commented Oct 21, 2016 at 13:14
  • Are you creating the stored procedure in another schema (let's call it SchemaB), and executing it as the user SchemaB, or something else?
    – Philᵀᴹ
    Commented Oct 21, 2016 at 13:15

1 Answer 1


You're doing something wrong. Test case follows...

Create schemaa and schemab:

SQL*Plus: Release Production on Fri Oct 21 14:28:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> create user schemaa identified by "schemaa";
SP2-0640: Not connected
SQL> conn / as sysdba
SQL> create user schemaa identified by "schemaa";

User created.

SQL> grant connect, resource to schemaa;

Grant succeeded.

SQL> create user schemab identified by schemab;

User created.

SQL> grant connect, resource to schemab;

Grant succeeded.

Login as schemaa, create two tables & grant select on the first to schemab, but don't grant anything on the 2nd table:

SQL> conn schemaa/schemaa;

SQL> create table satable ( aaa integer ) ;

Table created.

SQL> insert into satable values ( 1 );

1 row created.

SQL> grant select on satable to schemab;

Grant succeeded.

SQL> create table satablenograntsforb( aaa integer ) ;

Table created.

Login as schemab, create the stored procedure & then test it with the table we have permission to select from:

SQL> conn schemab/schemab;

  2  IS
  3  v_query varchar2(2000);
  4  BEGIN
  6  v_query := 'select aaa from schemaa. ' || TableName ;
  7  execute immediate v_query ;
  9  END;
 10  /

Procedure created.

SQL> exec schemabproc('satable');

PL/SQL procedure successfully completed.


As you can see, it executed ok.

Let's try on the table we don't have permission on:

SQL> exec schemabproc('satablenograntsforb');
BEGIN schemabproc('satablenograntsforb'); END;

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCHEMAB.SCHEMABPROC", line 7
ORA-06512: at line 1

Let's grant and re-test:

SQL> conn schemaa/schemaa

SQL> grant select on satablenograntsforb to schemab;

Grant succeeded.

Now we should be able to select from the 2nd table without error:

SQL> conn schemab/schemab 

SQL> exec schemabproc('satablenograntsforb');

PL/SQL procedure successfully completed.


Conclusion: There's something else happening. Note that the procedure above runs with AUTHID DEFINER, which is the default.

  • Thanks for this. I reset all the permissions and things are working well know.
    – Omnia9
    Commented Oct 24, 2016 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.