[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


PL/SQL Indirect referencing a cursor table

Posted on 2009-02-19
Medium Priority
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?

Question by:GNOVAK
  • 3
  • 3
LVL 13

Expert Comment

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

Author Comment

ID: 23683330
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.
LVL 13

Expert Comment

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

and use REF cursor
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 23685212
I've never used REF cursor - what's the typical syntax/usage?
LVL 13

Accepted Solution

sonicefu earned 1500 total points
ID: 23685317

Author Comment

ID: 23981068
please close.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question