Can I access a cursor from within a procedure by name?

Question

The cursor name would be passed in as a varchar2, and the cursor itself exists in the same package as the procedure.

Given only the name (and not a cursor reference) is it possible to access the cursor and loop through it?

If this requires the use of an "execute immediate" or similar, that's not out of the question... (though it's not quite clear to me how that would work, I was under the impression anything it declares is out of scope once it completes).

Apologies in advance, this seems like it should be obvious to me but I'm coming up blank.

Answer 1

Thinking about this a bit, I think you're going about it the wrong way. I'd UNION ALL the results from each of the "cursors" together and then use the "cursor name" to eliminate all the unwanted rows (which the optimizer should optimize away) so that you only get the rows you want. So something like

CREATE OR REPLACE PROCEDURE DO_SOMETHING(pin_Cursor_name IN VARCHAR2)
IS
  CURSOR csrFruits IS
    SELECT UPPER(pin_Cursor_name) AS FRUIT_TYPE,
           VARIETY_NAME,
           COLOR,
           SIZE,
           TARTNESS_RATING,
           NULL AS FUZZ_LENGTH,
           ROOTSTOCK,
           NULL AS PEEL_THICKNESS
      FROM APPLES
      WHERE pin_Cursor_name = 'apples'
    UNION ALL
    SELECT UPPER(pin_Cursor_name) AS FRUIT_TYPE,
           VARIETY_NAME,
           COLOR,
           SIZE,
           NULL AS TARTNESS_RATING,
           FUZZ_LENGTH,
           NULL AS ROOTSTOCK,
           NULL AS PEEL_THICKNESS
      FROM PEACHES
      WHERE pin_Cursor_name = 'peaches'
    UNION ALL
    SELECT UPPER(pin_Cursor_name) AS FRUIT_TYPE,
           VARIETY_NAME,
           COLOR,
           SIZE,
           NULL AS TARTNESS_RATING,
           NULL AS FUZZ_LENGTH,
           NULL AS ROOTSTOCK,
           PEEL_THICKNESS
      FROM KUMQUATS
      WHERE pin_Cursor_name = 'kumquats'
    UNION ALL
    SELECT UPPER(pin_Cursor_name) AS FRUIT_TYPE,
           VARIETY_NAME,
           'GREEN' AS COLOR,
           SIZE,
           NULL AS TARTNESS_RATING,
           FUZZ_LENGTH,
           ROOTSTOCK,
           NULL AS PEEL_THICKNESS
      FROM KIWIS
      WHERE pin_Cursor_name = 'kiwis';
BEGIN
  FOR aRow IN csrFruits LOOP
    DBMS_OUTPUT.PUT_LINE(pin_Cursor_name || ' - ' ||
                         aRow.VARIETY_NAME || ', ' ||
                         aRow.COLOR || ', ' ||
                         aRow.SIZE);
  END LOOP;
END DO_SOMETHING;

So here we have a cursor which will read from one of four different tables (APPLES, PEACHES, KUMQUATS, and KIWIS) depending on the input parameter. The idea is to have each of the subqueries return a rowset of the same "shape", adding NULL AS XXX for each column which an individual subquery doesn't supply.

Best of luck.

Answer 2

Your original problem statement is quite a bit vague and it's unclear for me what constraints you have and what that "other system" expects as a return value. You also might have an XY-problem, so the answer by @bobjarvis might have a valid point too.

The key problem here is that in PL/SQL there is no way to convert an explicit cursor to a cursor variable. Thus the following "simple" solution is not possible:

-- pseudo PL/SQL code
cursor cur_a ...
cursor cur_b ...

function get_cursor(p_cur_name varchar2) return sys_refcursor is 
  v_cur sys_refcursor;
begin
  execute immediate 'open v_cur for p_cur_name';
  return v_cur;
end;

v_cur := get_cursor('cur_b');

In the example package below I assume that all the cursors will have the same result set structure. I was lazy and used weak cursor variables even I should have used strong ones. The package code should be easy to follow.

There is at least one other variation that might be useful for you - bulk collect the data to a collection and process the collection with other subroutines. Below print(varchar2) just demonstrates how to open-iterate-close a cursor "dynamically" with a dbms_output.put_line.

create or replace package so48 is
  cursor cur_a is
    select 'A1' as val, 1 as id from dual union all
    select 'A2' as val, 2 as id from dual union all
    select 'A3' as val, 3 as id from dual
  ;
  cursor cur_b is
    select 'B1' as val, 4 as id from dual union all
    select 'B2' as val, 5 as id from dual union all
    select 'B3' as val, 6 as id from dual
  ;

  function get_cursor(p_cur_name in varchar2) return sys_refcursor;
  procedure print(p_cur in sys_refcursor);

  procedure print(p_cur_name in varchar2);
end;
/
show errors

create or replace package body so48 is
  function get_cursor(p_cur_name in varchar2) return sys_refcursor is
    v_cur sys_refcursor;
  begin
    case 
      when p_cur_name = 'A' then
        open v_cur for
          select 'A1' as val, 1 as id from dual union all
          select 'A2' as val, 2 as id from dual union all
          select 'A3' as val, 3 as id from dual
        ;
      when p_cur_name = 'B' then
        open v_cur for
          select 'B1' as val, 4 as id from dual union all
          select 'B2' as val, 5 as id from dual union all
          select 'B3' as val, 6 as id from dual
        ;
      else
        null;
    end case;

    return v_cur;
  end;

  procedure print(p_cur in sys_refcursor) is
    v_val varchar2(32767);
    v_id number;
  begin
    loop
      fetch p_cur into v_val, v_id;
      exit when p_cur%notfound;
      dbms_output.put_line('(val = ' || v_val || ')(id = ' || v_id || ')');
    end loop;
  end;

  procedure print(p_cur_name in varchar2) is
    plsql_compilation_error exception;
    pragma exception_init(plsql_compilation_error, -6550);
    v_cur_name constant varchar2(32767) := 'so48.' || p_cur_name;
    v_plsql constant varchar2(32767) :=
      q'[declare
        v_val varchar2(32767);
        v_id number;
      begin
        open ]' || v_cur_name || q'[;
        loop
          fetch ]' || v_cur_name || q'[ into v_val, v_id;
          exit when ]' || v_cur_name || q'[%notfound;
          dbms_output.put_line('(val = ' || v_val || ')(id = ' || v_id || ')');
        end loop;
        close ]' || v_cur_name || q'[;
      end;]';
  begin
    execute immediate v_plsql;
  exception
    when plsql_compilation_error then
      dbms_output.put_line('PL/SQL compilation error');
  end;
end;
/
show errors

Example run

SQL> exec so48.print(so48.get_cursor('A'))
(val = A1)(id = 1)
(val = A2)(id = 2)
(val = A3)(id = 3)

PL/SQL procedure successfully completed.

SQL> exec so48.print('cur_b')
(val = B1)(id = 4)
(val = B2)(id = 5)
(val = B3)(id = 6)

PL/SQL procedure successfully completed.

SQL>