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 privileges
when 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.