Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to deal with a variable in a Cursor in PL/SQL

Posted on 2011-02-20
8
Medium Priority
?
916 Views
Last Modified: 2012-05-11
Have a procedure like this

create or replace p1(t1 in varchar2) is
cursor c is
 select col1 from t1;

begin
  for v in c loop
    DBMS_OUTPUT.PUT_LINE(v.col1);
  end loop;
end p1;

It complained
PL/SQL: SQL Statement ignored
PL/SQL: ORA-00942: table or view does not exist
SInce it's in declare section, how to deal with it??
0
Comment
Question by:jl66
  • 4
  • 3
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34939979
Didn't you already ask this question?

You cannot do this.  You need to use dynamic SQL and execute immediate.
0
 

Author Comment

by:jl66
ID: 34939982
Even in PL/SQL declare section?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1080 total points
ID: 34940025
See if this helps I do declare a rowtype based on the core table.

I think you need to know the output before you call this.
create or replace procedure p1(t1 in varchar2) is
	myCur sys_refcursor;
	mysql varchar2(100);
	myRec dual%rowtype;
begin
  mySql := 'select dummy from ' || t1;
  open myCur for mySql;
  LOOP
    FETCH myCur INTO myRec;
    EXIT WHEN myCur%NOTFOUND;
      dbms_output.put_line(myRec.dummy);
    END LOOP; 
  close myCur;
end p1;
/

show errors

exec p1('dual');

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jl66
ID: 34940026
Actually this is different from the previous question. I tried to use the trick. It did not seem to work this time. ANy workaround??
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34940034
Are you looking for cursors based on any number of columns from any tables?
0
 

Author Comment

by:jl66
ID: 34940670
Yes. It does seem different from the above.
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 920 total points
ID: 34942829
http://www.dbforums.com/oracle/1005101-passing-table-name-parameter-stored-procedure.html
SQL> create or replace function select_any_table ( p_table_name in varchar2 )
  2  return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for 'select * from ' || p_table_name;
  7    return rc;
  8* end;
SQL> /

Function created.

SQL> var x refcursor
SQL> exec :x := select_any_table('DEPT')

PL/SQL procedure successfully completed.

SQL> print x

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Open in new window

0
 

Author Closing Comment

by:jl66
ID: 34944829
Thank both of you very much
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

916 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