SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using DBMS_SQL in a function returning SYS_REFCURSOR

JackKJan 20 2023

Hi All.
I am new in using DBMS_SQL package.
DBMS_SQL uses OPEN_CURSOR function which returns a number. How may I use this number (which is a cursor in DBMS_SQL meaning) as a return of a function defined to return SYS_REFCURSOR?

create or replace function my_test_func return sys_refcursor is
  refcur  sys_refcursor;
  v_stmt  varchar2(5000);
  c       number;
  dummy   number;
begin
  v_stmt := 'SELECT * FROM ALL_OBJECTS';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, v_stmt, DBMS_SQL.NATIVE);
  dummy := DBMS_SQL.EXECUTE(c);
  -- (...)  how to convert 'c' to 'refcur'?
  return refcur;
end;

I am using Oracle 19c.
Thanks in advance,
Jacek

This post has been answered by cormaco on Jan 20 2023

Jump to Answer

Added on Jan 20 2023

#cursor

3 comments

357 views