Re: variable and cusor definition

In article <nLo27.13054$Kf3.140536_at_www.newsranger.com>, Tony says...
>
>How to pass a variable or parameter into a column name or table name into the
>cursor definition(such as 'CUSROR get_date IS SELECT &2 from &1') (not talking
>about in where clause or subselect statement).
>
>Thanks.
>
>

sigh, no version.

I'll guess 8i and up.

Then a dynamically opened ref cursor is the way to go:

procecure p( p_cname in varchar2, p_tname in varchar2) is

   type rc is ref cursor;
   l_cursor rc;
begin

   open l_cursor for 'select ' || p_cname || ' from ' || p_tname;

   loop

       fetch l_cursor into ...;
       exit when l_cursor%notfound

See
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a77069/10_dynam.htm#4376

for details. In 8.0 and before, you must use DBMS_SQL for that -- see http://technet.oracle.com/doc/oracle8i_816/server.816/a76936/dbms_sql.htm#998100    

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp