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.
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.
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
OPEN p_recordset FOR
WHERE deptno = p_deptno
ORDER BY ename;
The resulting OUT cursor (p_recordset) can be referenced from
PL/SQL as follows.
SET SERVEROUTPUT ON SIZE 1000000
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);
INTO l_ename, l_empno, l_deptno;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
The number of values (3) match, and the OUT and IN are both of type
Get the Complete
Oracle SQL Tuning Information
The landmark book
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
for 30% off directly from the publisher.