Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-02-20
8
Medium Priority
?
919 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

581 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