Solved

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

Posted on 2011-02-20
8
900 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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

831 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