Home » SQL & PL/SQL » SQL & PL/SQL » Read SYS_REFCURSOR using DBMS_SQL
Read SYS_REFCURSOR using DBMS_SQL [message #224304] Tue, 13 March 2007 16:36 Go to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
I don't know if it's possible, but please help me if you know the answer.
I'm trying to print (read) the output of a weak ref cursor returned by a function, using DBMS_SQL package.

For example, having function:
create or replace
function f
         (pin_input pls_integer)
         return sys_refcursor is
   rc sys_refcursor;
begin
   if pin_input = 1
   then
      open rc for
         select 'a', 'b', 'c'
           from dual;
   else
      open rc for
         select 'x', 'y', 'z'
           from dual;
   end if;
   --
   return rc;
end f;


read the content of the ref cursor, using DBMS_SQL.

I can't get to PARSE the statement. The following code won't run:
declare
   c integer := dbms_sql.open_cursor;
   rc sys_refcursor;
begin
   dbms_sql.parse
      (c             => c
      ,statement     => 'f (2)'
      ,language_flag => dbms_sql.native
      );
   --
   dbms_sql.close_cursor (c => c);
exception
   when others then
      if dbms_sql.is_open (c => c)
      then
         dbms_sql.close_cursor (c => c);
      end if;
      --
      raise;
end;


It fails with ORA-00900: Invalid SQL Statement. I understand why, but I don't see another way of coding this.

Please help, and thank you in advance.
Re: Read SYS_REFCURSOR using DBMS_SQL [message #224392 is a reply to message #224304] Wed, 14 March 2007 02:49 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Actually it's not clear completely what exactly you want to
do.

You can use SQL*Plus connamd PRINT to show refcursor content.
Please be more specific - what do you want to achieve ?

SQL> var rc refcursor
SQL> create function f
  2  return sys_refcursor
  3  is
  4   rc sys_refcursor;
  5  begin
  6   open rc for 'select ename from emp';
  7   return rc;
  8  end;
  9  /

Function created.

SQL> exec :rc := f

PL/SQL procedure successfully completed.

SQL> print rc

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.


Rgds.
Re: Read SYS_REFCURSOR using DBMS_SQL [message #224523 is a reply to message #224392] Wed, 14 March 2007 09:33 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
We tried that, but were getting an "ORA-00604: error occurred at recursive SQL level 1" error. The function that returns the ref cursor is a remote function (residing in another database, and accessed through a database link).
It may very well be that ref cursors can not be communicated via a DB link (I'll have to read the docs on that Smile.

I'm sorry for not stating that earlier (about the remote function), and thank you very much for you reply, Dmitry.

Regards.
Re: Read SYS_REFCURSOR using DBMS_SQL [message #224533 is a reply to message #224523] Wed, 14 March 2007 10:43 Go to previous message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
>>It may very well be that ref cursors can not be communicated via a DB link (I'll have to read the docs on that ).

This is described in the documentation.

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#1802

Quote:

Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another.
If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.



Rgds.
Previous Topic: Data model design
Next Topic: How to process a dynamic sql ?
Goto Forum:
  


Current Time: Sun Dec 04 16:34:52 CST 2016

Total time taken to generate the page: 0.09812 seconds