PL/SQL Indirect referencing a cursor table
Posted on 2009-02-19
I'm creating a PL/SQL routine that will reside in a "Master" schema. It will be called by various other schemas.
It needs to reference tables that are in the calling schema.
Two questions really.
1) I need to create a cursor based on the calling schema's table:
cursor test is select distinct ID from calling_schema.mytable ;
Typically if it wasn't for the cursor, I would use Execute Immediate - something like:
MyVar = 'cursor test is select distinct ID from ' || schema_parameter ||'.mytable ';
Execute Immediate MyVar;
But I have this nagging feeling that the cursor would not be available to be utilized if I did it this way.
What's the best way to accomplish this
2) In a general sense, if I need to use the calling schema's tables, is it neccessary to pass the calling schema's name as a parameter, or , is there a more efficient way to do this?
Do I need to mention the calling schema's name at all, or will it assume the table, if it doesnt exist in the Master schema, exists in the calling schema?