Re: PL/SQL function core dumps when returning un-assigned REF CURSOR variable

A copy of this was sent to "Adrian Bowen" <adrian_at_raptor.win-uk.net> (if that email address didn't require changing) On Mon, 16 Aug 1999 17:54:01 +0100, you wrote:

>I have a PL/SQL function in a stored package that returns a REF CURSOR
>variable. I've noticed that if I attempt to return from the function before
>opening the cursor (which I want to do if for example the parameters to the
>function are not acceptable), my session aborts with a core dump. (8.0.5
>under Solaris 2.6).
>
>My question is: What should I in fact return from the function to indicate
>the equivalent of a 'no rows found' result?. I have tried to find the
>answer in the manuals, honest.
>
>The relevant declarations are roughly as below:
>
>... in the package spec ...
>
>TYPE rec_typ IS RECORD ( c1 my_table.col_1%TYPE , c2 my_table.col_2%TYPE ) ;
>TYPE refcur_typ IS REF CURSOR RETURN rec_typ ;
>
>... in the package body ...
>
>FUNCTION my_func ( my_parameter IN VARCHAR2 ) RETURN refcur_typ IS
> my_cursor refcur_typ ;
>BEGIN
> RETURN ( my_cursor ) ; /*- Session core dump here */
>END
>
>Grateful for any suggestions.
>
>Adrian Bowen
>

\

I confirmed this does not work and opened a bug (966296) for it (it should not fail as it is).

A workaround is to not use a function but to use a procedure as:

SQL> create table my_table ( col_1 int, col_2 int );

Table created.

SQL>
SQL> create or replace package demo_pkg   2 as
  3
  4 TYPE rec_typ IS RECORD ( c1 my_table.col_1%TYPE , c2 my_table.col_2%TYPE ) ;
  5 TYPE refcur_typ IS REF CURSOR RETURN rec_typ ;   6
  7 procedure my_proc( my_parameter in varchar2, my_cursor in out refcur_typ ) ;
  8
  9 end;
 10 /

Package created.

SQL>
SQL> create or replace package body demo_pkg   2 as
  3
  4 procedure my_proc( my_parameter in varchar2, my_cursor in out refcur_typ )   5 IS
  6 BEGIN
  7 RETURN;
  8 END;
  9
 10 end;
 11 /

Package body created.

SQL> 
SQL> variable x refcursor
SQL> 
SQL> exec demo_pkg.my_proc('x', :x)

PL/SQL procedure successfully completed.

SQL> print :x
ERROR:
ORA-24338: statement handle not executed

That would be the expected behaviour...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 16 1999 - 15:23:55 CDT