Re: Seeing results of stored procedure

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 15 Jan 2003 06:31:17 -0800
Message-ID: <b03rbl029so@drn.newsguy.com>

In article <pl292v8mhla5l1p3iqgpfenjb1jlm0rgu9_at_4ax.com>, Harry says...
>
>On Fri, 03 Jan 2003 16:45:36 +0000, rajXesh <member11038_at_dbforums.com>
>wrote:
>
>>
>>Add a : at the start of the name of trhe cursor when you call the proc
>>for eg
>>SQL> exec Get_Facility.Get_Facility_By_ID(2290, :facility_out_cur);
>>
>>-- rajXesh
>
>OK, I did that, and now I get:
>
>SQL> variable facility_out_cur REFCURSOR;
>SQL> exec Get_Facility.Get_Facility_By_ID(2290,:facility_out_cur);
>
>PL/SQL procedure successfully completed.
>
>SQL> print :facility_out_cur;
>ERROR:
>ORA-24338: statement handle not executed
>
>
>SP2-0625: Error printing variable "facility_out_cur"
>
>What's this telling me?
>
>Thanks,
>Harry Boswell
>

that you didn't open the ref cursor in the procedure (or you did but then closed it before exiting the procedure).

consider:

ops$tkyte_at_ORA817DEV> create or replace package demo_pkg   2 as
  3 type rc is ref cursor;
  4

  5          procedure p1( x in out rc );
  6          procedure p2( x in out rc );

  7 end;
  8 /

Package created.

ops$tkyte_at_ORA817DEV> create or replace package body demo_pkg   2 as

  3          procedure p1( x in out rc )
  4          as
  5          begin
  6                  null;
  7          end;
  8
  9          procedure p2( x in out rc )
 10          as
 11          begin
 12                  open x for select * from dual;
 13          end;

 14 end;
 15 /

Package body created.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> variable x refcursor
ops$tkyte_at_ORA817DEV> set autoprint on
ops$tkyte_at_ORA817DEV> exec demo_pkg.p1( :x )

PL/SQL procedure successfully completed.

ERROR:
ORA-24338: statement handle not executed

SP2-0625: Error printing variable "x"
ops$tkyte_at_ORA817DEV> exec demo_pkg.p2( :x )

PL/SQL procedure successfully completed.

D
-
X

ops$tkyte_at_ORA817DEV>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Jan 15 2003 - 08:31:17 CST