?
Solved

bind variable in query to sys_refcursor

Posted on 2012-04-11
4
Medium Priority
?
823 Views
Last Modified: 2012-04-11
I have a simple query to select a table into a ref_cursor but I keep receiving either an "invalid table_name" or a "bad bind variable" when executing


Please show me the correct syntax:

PROCEDURE GET_TABLE ( pTName in varchar2, RESULT out sys_refcursor) as

vQuery varchar2(75);
BEGIN
  vQuery:='select  * from :1';
 
     open RESULT for vQuery using pTName;





END GET_TABLE;
0
Comment
Question by:Focker513
  • 2
4 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 37834447
you can't use bind variables to specify objects,  only values within those objects (i.e. column values)


vQuery:='select  * from ' || ptname;

open RESULT for vQuery;
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 37834454
Table names cannot be bind variables.

try:

vQuery:='select  * from ' || pTName;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 37834468
the purpose of bind variables is save parsing for similar queries


select * from table1;

is not the same query as

select * from table2;

you have to validate both tables do, in fact, exist, resolve different sets of synonyms, different sets of permissions,
if the objects are actually views rather than real tables, then different parsing within the views, etc.


binds are for things like this...

select * from table1 where column1 = :x;

Now I can query that multiple times for different values of x.
the objects and columns didn't change, so it's the same query, same synonyms, same permissions, same parsing.

Only the referenced value is changeable, therefore it is bindable
0
 

Author Closing Comment

by:Focker513
ID: 37834480
Thanks for the quick the responses and the deeper explanations.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

592 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