Solved

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

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

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 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 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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now