Home » SQL & PL/SQL » SQL & PL/SQL » How to declare a cursor with dynamic query
How to declare a cursor with dynamic query [message #351741] Thu, 02 October 2008 15:29 Go to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
CURSOR cur_one IS
SELECT column_three
FROM tab_two;

For SELECT part, since I do not know the name of column and table name, it would be a dynamic query.

The problem is dynamic query can only be executed in BEGIN block. How to resovle the problem?
Re: How to declare a cursor with dynamic query [message #351763 is a reply to message #351741] Thu, 02 October 2008 21:45 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You can use REFCURSOR.They are made for it.

The problem is dynamic query can only be executed in BEGIN block. How to resovle the problem?


Which query executes before BEGIN block??


Regards,
Rajat
Re: How to declare a cursor with dynamic query [message #351765 is a reply to message #351741] Thu, 02 October 2008 22:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Fortunately for you, Oracle PL/SQL has a large documentation. It can be found e.g. online on http://tahiti.oracle.com/.

PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL
concentrate on OPEN-FOR keyword

The detail syntax with many example links can be found in Chapter 13 PL/SQL Language Elements, OPEN-FOR Statement.

I am just curious how much "dynamic" is the solution you want to implement.
Re: How to declare a cursor with dynamic query [message #351771 is a reply to message #351741] Fri, 03 October 2008 00:40 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at T.Kyte's print_table function.

Regards
Michel
Previous Topic: how to use DBMS STATS
Next Topic: Trigger to call a procedure that loads updated and insert records
Goto Forum:
  


Current Time: Tue Dec 06 12:30:06 CST 2016

Total time taken to generate the page: 0.09666 seconds