CODEWITHSUNDEEP
oracle-databasefunctionplsqlcursor
atamata
atamata

Reputation: 1077

Function to return a cursor in PL SQL,

I have a function which returns a SYS_REFCURSOR, this function is to be called from different packages and we don't want to have to duplicate the cursor definition in multiple places.

FUNCTION f_get_cur(p_date DATE, p_code VARCHAR(10)) RETURN SYS_REFCURSOR IS
  cur_s SYS_REFCURSOR;
BEGIN
  OPEN cur_s FOR
    SELECT .blah blah etc etc

  return cur_s;
END f_get_cur;

Which compiles ok, however when I want to use the function in a FOR LOOP where I'd normally put the cursor I get the following error

Error: PLS-00456: item 'f_get_cur' is not a cursor

I'm attempting to open the cursor like so...

FOR cc_rec IN f_get_cur(c_date, p_c_code) LOOP

Am I using the wrong data type? Is there some other way of achieving what I'm trying?

Upvotes: 3

Views: 3918

Answers (2)

atamata
atamata

Reputation: 1077

I managed to get this working by creating another(!) cursor which implements the function I already created (which wraps the original cursor)

cursor cur_real(cp_date DATE, cd_code VARCHAR2(10)) IS
  select ... etc
FROM TABLE(f_get_cur(cp_date, cp_code));

I can now use the cursor like so

FOR cc_rec IN f_get_cur(c_date, p_c_code) LOOP
   do stuff ... etc
END LOOP

Upvotes: 0

Aleksej
Aleksej

Reputation: 22969

You need to handle the returned cursor in a different way; for example:

SQL> create or replace FUNCTION f_get_cur(p_date DATE, p_code VARCHAR) RETURN SYS_REFCURSOR IS
  2    cur_s SYS_REFCURSOR;
  3  BEGIN
  4    OPEN cur_s FOR
  5      SELECT to_char(p_date, 'dd-mm-yyyy') || p_code val from dual;
  6
  7    return cur_s;
  8  END f_get_cur;
  9  /

Function created.

SQL> declare
  2        cur_s SYS_REFCURSOR;
  3        v     varchar2(100);
  4  begin
  5      cur_s := f_get_cur(sysdate, 'xx');
  6      loop
  7          fetch cur_s into v;
  8          exit when cur_s%NOTFOUND;
  9          dbms_output.put_line(v);
 10      end loop;
 11  end;
 12  /
30-04-2019xx

PL/SQL procedure successfully completed.

SQL>

Upvotes: 5

Related Questions