I'm working with oracle 11g R2.

Issue:

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

Background:

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

Code:

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.