I cloned this from your example
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
The first prece complies with out errors, but the second section fails with this error.
You did not declare this in your example:
- Code: Select all
SQL> @q5_refcursor
Procedure created.
get_stores (p_state = 'CA', p_recordset => l_cursor);
*
ERROR at line 7:
ORA-06550: line 7, column 16:
PLS-00201: identifier 'P_STATE' must be declared
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 10, column 11:
PLS-00201: identifier 'V_CURSOR' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
SQL> spool off
*************************************
- Code: Select all
CREATE OR REPLACE PROCEDURE get_stores (p_state IN stores.state%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_recordset
FOR
SELECT
stor_name,
stor_address,
city
FROM
stores
WHERE
state = p_state
ORDER BY city;
END;
/
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_stor_name stores.stor_name%TYPE;
l_stor_address stores.stor_address%TYPE;
l_city stores.city%TYPE ;
BEGIN
get_stores (p_state = 'CA', p_recordset => l_cursor);
LOOP FETCH
l_cursor INTO l_stor_name, l_stor_address, l_city;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stor_name || ' | ' || l_stor_address || ' | ' || l_city);
END LOOP;
CLOSE l_cursor;
END;
/