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.

+1


source to share


1 answer


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 .



+2


source







All Articles