Creating a cursor dynamically

From: <kelvinw_at_asymetrix.com>
Date: Wed, 28 Jul 1999 01:04:39 GMT
Message-ID: <379e53dd.1648467771@news>

Hi evveryone,

I open up a very simple cursor as follows:

Declare

	sCmd	varchar2(500);
	iCount	int;
	TYPE 	curvar_type IS REF CURSOR;
	curvar1	curvar_type;
	tname	varchar2(80);

Begin         

	Open curvar1 For Select table_name from user_tables;


--	sCmd := 'Open curvar1 For Select table_name from user_tables';


--	ACMSPExecute( sCmd );



	Loop
		FETCH curvar1 into tname;
		EXIT WHEN curvar1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( 'table_name: ' || tName );
	END Loop;


--	sCmd := ' Close curvar1' );


--	SPExecute( sCmd );


	Close curvar1;

End;
/

Where SPExecute() is a stored procedure that open up cursor and execute the input command string dynamically.

In MSSQL, I can open up a cursor by doing something like Select @sCmd = 'Open curvar1 For Select table_name from user_tables' Execute( sCmd )

equivalent to Oracle:
sCmd := 'Open Open curvar1 For Select table_name from user_tables'; SPExecute( sCmd);

to get a cursor opened dynamically. And close it with sCmd := 'Close curvar1';
SPExecute( sCmd );

Basically, I need to create a cursor on the fly. I have tried that method didn't work using the oracle dynamic SQL. I wonder if it is possible to do something like that in Oracle. And how to do it?

Any help is appreciated. Received on Tue Jul 27 1999 - 20:04:39 CDT