Solved

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

Posted on 2011-02-20
8
904 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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 270 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 77

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 230 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

680 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