Dynamic Cursor (REF CURSOR) and Fetch problem...

[wellster34]

Hello,

I have a dynamically created select statement and store it in VARCHAR2 variable. Now, I need to open and execute it to fetch the values.

DECLARE
...
TYPE cv_type IS REF CURSOR;
TEST_CURSOR cv_type;
...
BEGIN
...
OPEN TEST_CURSOR FOR v_select_string;

I'm good to here but I have not figured out to fetch it. I have tried the FOR testing_cur IN TEST_CURSOR LOOP....END LOOP;

I get the PLS-00221: 'TEST_CURSOR' is not a procedure or is undefined


Any suggestions?


The situation I have is that since this is a dynamic sql select statement, I can not hard code the variables so that I could do the FETCH TEST_CURSOR INTO ....


Thanks for your time,

 

[carp]

You will probably need to build a record that matches the anticipated structure of your cursor:

TYPE my_record_type IS RECORD(col1 VARCHAR2(10),....);
my_record MY_RECORD_TYPE
.
.
.
OPEN TEST_CURSOR FOR v_select_string;
LOOP
FETCH test_cursor INTO my_record;
EXIT WHEN test_cursor%NOTFOUND;
.
.
.
END LOOP;
CLOSE test_cursor;
.
.
.

 

[wellster34]

Hi,

Do you know if you can create the TYPE part dynamically? Or is it hardcoded in the DECLARE...

TYPE my_record_type IS RECORD(col1 VARCHAR2(10),....);


My situation is that I can not hard code the TYPE due to the input table can change on the fly?

 

[HimanB]

Hi,
If you are using Oracle 9 then you may make use of the new datatypes ANYDATA, ANYTYPE and ANYDATASET
For example refer to following URL:

http://www.quest-pipelines.com/pipelines/plsql/tips.htm#MARCH

HTH
Regards
Himanshu

 

[lewisp]

With DBMS_SQL you can use DESCRIBE to work out how many columns you are returning in a dynamic cursor.