ORA-24338 When attempting to return a Refcursor across a Database link (Doc ID 750126.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms

When calling a remote stored procedure which returns a refcursor across a
database link, ORA-24338 Error is got when attempting to access the returned cursor.

This can be demonstrated by using below Example :
Below Package is created in Local Database :

create or replace package getrefcursor as
type empcur is ref cursor;
procedure getemps(ecur out empcur);
end getrefcursor;
/

create or replace package body getrefcursor as
procedure getemps(ecur out empcur) is
sqlstmt varchar2(2000) := 'select * from v where deptno = :1';
v_dept number := 30;begin open ecur for sqlstmt using v_dept;
end getemps;
end getrefcursor;
/

var abc refcursor;
set autoprint on

17:03:45 SQL> execute getrefcursor.getemps(:abc);
PL/SQL procedure successfully completed.


ORA-24338  Error can be reproduced by calling Package getrefcursor that uses Ref Cursor from a Remote Database using Database link as shown below :


Connecting in Remote Database and creating a Synonym for Package getrefcursor ,


17:08:47 SQL> create synonym rprc for getrefcursor@ora10g;

Synonym created.

 

/* Here Database link 'ora10g' on Remote Database connects to Database where Package getrefcursor exists */

var abc refcursor;
17:09:40 SQL> set autoprint on
17:09:41 SQL> exec rprc.getemps(:abc)
BEGIN rprc.getemps(:abc); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement


ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "abc"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms