PL/SQL: Fetching from a Cursor that is passed between two functions

I have a quick question about fetching results from a weakly typed cursor and was wondering if anyone had come across this problem before?

My set up is as follows;

Inner 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;

Calling 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 test harness code is;

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

I'm not really sure what is causing this to occur. The error is happening in my test code, but I've used this exact same method hundreds of times before and not encountered this problem. The only difference now being, that the cursor is being passed back up through two functions instead of just one.

Does anyone have any idea what the problem might be here? I've Googled around for it and all I can find are suggestions to strongly type the cursors, which is not an option for me unfortunately.

Thanks for any help anyone can give, cheers.

Answers


I can replicate your problem, and it seems like an Oracle bug to me. From Googling the error I found this discussion of the same problem on OraFAQ.


Need Your Help

Activesync Protocol integration

ios iphone activesync

I am implementing AES Protocol on my iOS application. I am implementing folder sync command and i getting 200 response code with 341 length of data. When i am trying to convert the data in NSString...

UITableView in UIScrollView not responding to first tap after scroll

ios objective-c uitableview uiscrollview

I'm experiencing a strange issue where my UITableView is not properly responding to taps after the scroll view it is contained within is scrolled at all. I say properly because it is highlighting t...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.