Skip to Main Content
  • Questions
  • Unable to Retrieve sys_refcursor values from remote function

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Sri.

Asked: June 23, 2016 - 11:43 am UTC

Answered by: Chris Saxon - Last updated: June 23, 2016 - 2:13 pm UTC

Category: Developer - Version: sqldeveloper 3.2.20.09

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: The real question is why are you NOT blogging

You Asked

Hi,

i have created a function in DB1 that returns a sys_refcursor as output which is giving the result as desired in DB1. But when other database DB2 is trying to execute the function using dblink, that cursor is not returning any values. It is not displaying any error message but returning null values.

Please help me out how can i modify my below function to access remotely using dblink.

create or replace
FUNCTION abc(
name IN VARCHAR2)
RETURN sys_refcursor
AS
nameup VARCHAR2(50) :=upper(name);

p_sqlcode VARCHAR2 (1000);
p_sqlerrm VARCHAR2 (1000);
RESULT sys_refcursor;

BEGIN
OPEN RESULT FOR
SELECT DISTINCT b.stucode
FROM emp a
JOIN student b
ON a.empcode = b.stucode
AND a.empname = b.stuname
WHERE a.empcode IN
(SELECT DISTINCT empcode
FROM manager
WHERE upper(empname) LIKE '%'
|| nameup
|| '%'
)

RETURN RESULT;
exception when others then
p_sqlerrm := SQLERRM;
p_sqlcode := SQLCODE;
dbms_output.put_line('p_sqlerrm' || p_sqlerrm );
dbms_output.put_line('p_sqlcode' || p_sqlcode);

END;

Output in DB1: output in DB2:
stucode stucode
1036
1046
1010
1000

and we said...

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


Is this answer out of date? If it is, please let us know via a Review

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here