A short post to note an Oracle database bug I encountered recently. Whilst testing some code the exception:
ORA-01001: invalid cursor
was produced. The error was originating from a pipelined function. Tracking things down it seems that Oracle will throw this exception when the following occur:
- A PL/SQL routine passes out a ref cursor variable
- The ref cursor passed out uses a pipelined function
- The pipelined function itself has a ref cursor as as input parameter
Having replicated the problem on Linux, AIX and WIndows I believe it is not platform specific. It occurs in database versions 11.2 and 12.1. Earlier versions have not been tested and so may be affected too.
The bug appears to be similar to Bug 2968664 : ORA-1001 IN FUNCTION CALLED FROM SQL WHICH FETCHES FROM A REFCURSOR PARAMETER but that bug is noted to be fixed in version 10.1.0.1.
I created a simple test package, imaginatively named pipeline_test, for Oracle Support to replicate the problem. The package contains 3 callable routines:
- run_test1 – a routine with an OUT ref cursor parameter that matches the criteria noted earlier. Calling this routine throws ORA-01001
- run_test2 – a routine that opens a ref cursor similar to run_test1 but this routine consumes that cursor rather than passing it out to a caller. This routine does not generate ORA-01001.
- run_test3 – A routine that calls run_test1 to obtain a ref cursor and then consumes it, similar to run_test2. This routine does generate ORA-01001.
The package and test script are available for download. The setup.sql file creates the package and collection object required by the pipelined function while the run.sql script is a basic SQL*Plus script to call the test routines.
For my situation I was to work around the problem by substituting a collection for the ref cursor that the pipelined function used. I had some luck with hinting the query that used the pipelined function with the MATERIALIZE hint.
I’ll update this post with the details that I get back from Oracle Support. At the moment they have acknowledged that there is a problem and they are investigating.
Update 01 May 2015: Oracle have created a new bug, Bug 20405099, to track this problem. However, access to the bug details on Oracle Support is restricted so you’ll have to take my word on it. 🙂
Mark Hoxey 