It's a restriction of database links:
"Using a cursor variable in a server-to-server remote procedure call (RPC) causes an error. However, you can use a cursor variable in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway."
http://docs.oracle.com/database/121/LNPLS/cursor_variable.htm#LNPLS01312 Also see MOS note 451938.1. Run it in SQL*Plus and you'll see an exception when you try and fetch the cursor:
SQL> create table t (
2 x int
3 );
Table created.
SQL>
SQL> insert into t select rownum from dual connect by level <= 5;
5 rows created.
SQL>
SQL> create or replace function f
2 return sys_refcursor as
3 cur sys_refcursor;
4 begin
5 open cur for select * from t;
6 return cur;
7 end;
8 /
Function created.
SQL>
SQL> var c refcursor
SQL> exec :c := f;
PL/SQL procedure successfully completed.
SQL> print :c
X
----------
1
2
3
4
5
SQL> exec :c := f@loopback;
PL/SQL procedure successfully completed.
SQL> print :c
ERROR:
ORA-24338: statement handle not executed
To get around this you could:
- Change the remote function to fetch all the results into a collection. Then pass the array over the db link
- Move the function to the local database. Change the query to call the table(s) using the db link:
SQL> create or replace function f
2 return dbms_sql.number_table as
3 rws dbms_sql.number_table;
4 begin
5 select *
6 bulk collect into rws
7 from t;
8
9 return rws;
10 end;
11 /
Function created.
SQL>
SQL> declare
2 l dbms_sql.number_table@loopback;
3 begin
4 l := f@loopback;
5 for i in 1 .. l.count loop
6 dbms_output.put_line(l(i));
7 end loop;
8 end;
9 /
1
2
3
4
5
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace function f
2 return sys_refcursor as
3 cur sys_refcursor;
4 begin
5 open cur for select * from t@loopback;
6 return cur;
7 end;
8 /
Function created.
SQL>
SQL> exec :c := f;
PL/SQL procedure successfully completed.
SQL> print :c
X
----------
1
2
3
4
5