PL / SQL: fetch from a cursor that is passed between two functions
I have a quick question about getting results from a weakly typed cursor and was wondering if anyone has encountered this problem before?
My setup is like this:
Internal function;
create or replace FUNCTION A_CURSOR_TEST_INNER
(
varCursor OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
varStatus NUMBER;
BEGIN
OPEN varCursor FOR
SELECT docid
FROM DOCUMENT_TABLE;
RETURN 0;
END;
Call function;
create or replace FUNCTION A_CURSOR_TEST_OUTER
(
varCursor OUT SYS_REFCURSOR
)
RETURN NUMBER
AS
varStatus NUMBER;
BEGIN
varStatus := A_CURSOR_TEST_INNER(varCursor => varCursor);
RETURN 0;
END;
Test harness code;
DECLARE
varCursor SYS_REFCURSOR;
v_Return NUMBER;
BEGIN
v_Return := A_CURSOR_TEST_OUTER(varCursor => varCursor);
DECLARE
docid_ NUMBER;
BEGIN
IF(varCursor %ISOPEN) THEN
LOOP
FETCH varCursor INTO docid_ ;
EXIT WHEN varCursor %NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' docid_:' || docid_ );
END LOOP;
CLOSE varCursor ;
END IF;
END;
END;
The error I get if I run my wire harness test code,
ORA-06504 PL / SQL return types Result. The specified variables or the query does not match
I'm not really sure what is causing this. The error is in my test code, but I have used this exact method hundreds of times before and have not encountered this issue. The only difference is that the cursor is passed back through two functions instead of one.
Can anyone understand what might be here? I have searched for it and all I can find are suggestions for heavily typing cursors, which unfortunately is not an option for me.
Thanks for any help you can give, cheers.
source to share
I can replicate your problem and it looks like an Oracle bug to me. From a google bug I found this discussion of the same issue on OraFAQ .
source to share