My Oracle Support Banner

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

Last updated on NOVEMBER 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


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

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


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.