We help IT Professionals succeed at work.

PL/SQL Indirect referencing a cursor table

GNOVAK
GNOVAK asked
on
Medium Priority
799 Views
Last Modified: 2013-12-07
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?

Comment
Watch Question

Commented:
you can create synonyms for the tables of calling schema then you will not be in need of mentioning the schema name.

Author

Commented:
Any way to do it without adding a synonym?
I would rather not add one more layer to the process if I can help it.

Commented:
then you can send a string ('userName.TableName'), this is the simplest way.

and use REF cursor

Author

Commented:
I've never used REF cursor - what's the typical syntax/usage?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
please close.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.