Question: I have a ref cursor defined as
an OUT parameter and I am trying to get a receiving PL/SQL to accept
the ref cursor. When I try to fetch the ref cursor into my
receiving program I get the ORA-06504 error.
Answer: The oerr utility shows
this for the ORA-06504 error:
ORA-06504: PL/SQL: Return types
of Result Set variables or query do not match.
Cause:
Number and/or types of columns in a query does not match declared
return type of a result set variable, or declared types of two
Result Set variables do not match.
Action:
Change the program statement or declaration. Verify what query the
variable actually refers to during execution.
First, it is important to note that a ref cursor is not a cursor, it
is a pointer to a cursor, and the cursor, in turn, points to a
PL/SQL collection (array). Here are some
ref cursor examples.
Dr. Tim Hall provides this example of using a PL/SQL ref cursor as
an OUT variable and reading it with another PL/SQL program:
CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/
The resulting OUT cursor (p_recordset) can be referenced from
PL/SQL as follows.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);
LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;
/
The number of values (3) match, and the OUT and IN are both of type
sys_refcursor.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
