Re: Help,Declare a cursor in stored procedure.

From: Ricardo Rocha <rrocha_at_usagate.net>
Date: 1997/03/15
Message-ID: <01bc311b$095fe9d0$6564c7d0@rrocha>#1/1

At first sight, a cursor variable (TYPE...REF CURSOR RETURN...) would look suitable for your requirement.

However, since the actual cursor implementation requires that the table exists at compile time, you might want to use DBMS_SQL to dynamically retrieve the desired records.

The following anonymous PL/SQL block shows how to use dynamic cursors with DBMS_SQL. As usual, good old "emp and dept" tables are used...

DECLARE

• Variables my_cursor INTEGER; rows_fetched INTEGER;

	my_empno	emp.empno%TYPE;
	my_ename	emp.ename%TYPE;
	my_dname	dept.dname%TYPE;
BEGIN


	--

	-- 1. Drop previous table, if any

	--

	DECLARE
		No_such_table	EXCEPTION;
		PRAGMA EXCEPTION_INIT(No_such_table, -942);
	BEGIN
		my_cursor := dbms_sql.open_cursor;

		dbms_sql.parse(
			my_cursor,
			'DROP TABLE temp_table',
			dbms_sql.v7
		);

		dbms_sql.close_cursor(my_cursor);
	EXCEPTION
		WHEN No_such_table THEN
			NULL; -- Ok, table may not exist yet...
	END;




	--

	-- 2. Create table from query

	--

	my_cursor := dbms_sql.open_cursor;

	dbms_sql.parse(
		my_cursor,
		'CREATE TABLE temp_table AS ' ||
			'SELECT empno, ename, dname ' ||
			'FROM emp, dept ' ||
			'WHERE emp.deptno = dept.deptno (+)',
		dbms_sql.v7
	);

	dbms_sql.close_cursor(my_cursor);




	--

	-- 3. Retrieve records from new table

	--

	my_cursor := dbms_sql.open_cursor;

	dbms_sql.parse(
		my_cursor,
		'SELECT empno, ename, dname FROM temp_table',
		dbms_sql.v7
	);



	-- This code section tells Oracle the expected type and length

	-- for each selected column

	dbms_sql.define_column(my_cursor, 1, my_empno);
	dbms_sql.define_column(my_cursor, 2, my_ename, 10);
	dbms_sql.define_column(my_cursor, 3, my_dname, 14);



	-- Execute SELECT statement

	rows_fetched := dbms_sql.execute(my_cursor);



	-- For each record returned...

	WHILE (dbms_sql.fetch_rows(my_cursor) = 1) LOOP
		-- Fetch each column into appropriate program variable
		dbms_sql.column_value(my_cursor, 1, my_empno);
		dbms_sql.column_value(my_cursor, 2, my_ename);
		dbms_sql.column_value(my_cursor, 3, my_dname);

		-- Display returned values
		dbms_output.put_line(
			to_number(my_empno) || ': ' ||
			my_ename || ' (' || my_dname || ')'
		);
	END LOOP;

	dbms_sql.close_cursor(my_cursor);

END;
.
/ Received on Sat Mar 15 1997 - 00:00:00 CST