How to return a Strong Type CURSOR from a function or PROC in PLSQL

Question

Playing around with PLSQL at the moment and after finally managing to succeed on returning a weak type, I would love to know how to do this for strongly typed cursors, based on a table structure, let's say the (in)famout employees table.

Below you'll find the function I would like to change and use for this. Already started to edit my code in the line with TYPE cur_empData IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;

Also the Stored PROC, which should be calling both of my tests is attached at the end.

Thanks in advance and Regards

FUNCTION getEmpCursorStrong (
        p_startId NUMBER, p_endId NUMBER
    )
    RETURN EMPLOYEES
    IS
        TYPE cur_empData IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;        
        l_sql_query VARCHAR(100);
    BEGIN
        
        l_sql_query := 'SELECT * FROM EMPLOYEES';

        IF ( p_startId > 0 OR p_endId > 0) THEN
            l_sql_query := l_sql_query || ' WHERE employee_id BETWEEN ' || p_startId || ' AND ' || p_endId;
        END IF;

        OPEN cur_empData FOR
            l_sql_query;

        RETURN cur_empData;
END getEmpCursorStrong;

Here's the calling PROC

    PROCEDURE printEmpData (
        p_startId NUMBER DEFAULT 0, p_endId NUMBER DEFAULT 0
    )
    AS
        cur_empData SYS_REFCURSOR;
        rec_empData EMPLOYEES%ROWTYPE;
    BEGIN

        dbms_output.put_line('WEAK CURSOR OUTPUT:');
        cur_empData := getEmpCursorWeak(p_startId, p_endId);        

        LOOP
            FETCH cur_empData INTO rec_empData;            
            EXIT WHEN cur_empData%NOTFOUND;
                dbms_output.put_line( ' with id: ' || rec_empData.employee_id || ' - ' || rec_empData.LAST_NAME);
                
        END LOOP;

        CLOSE cur_empData;

        dbms_output.put_line('##########################');
        dbms_output.put_line('STRONG CURSOR OUTPUT HERE:');


    END printEmpData;

Answer 1

You cannot use dynamic SQL for a strongly-typed cursor. To enforce the type match, Oracle has to be able to describe the return structure of a SQL query and match it up with the REF CURSOR definition at parse time, not execute time. It can only do this with static SQL. Here's an example:

create or replace package testpkg
as
  type strongcursortype is ref cursor return dual%ROWTYPE;
end;
/
create or replace function getcursor
  return testpkg.strongcursortype
as
  strongcursor testpkg.strongcursortype;
begin
  open strongcursor for select * from dual;
  return strongcursor;
end; 
/
create or replace procedure consumecursor
as
  strongcursor testpkg.strongcursortype;
  resultrow dual%ROWTYPE;
begin
  strongcursor := getcursor();
  
  fetch strongcursor into resultrow;
  while strongcursor%found
  loop
    dbms_output.put_line(resultrow.dummy);
    fetch strongcursor into resultrow;
  end loop;
end; 

Now execute the procedure:

begin consumecursor; end;

And it works, you see 'X' output. But if you try to make the select * from dual dynamic:

create or replace function getcursor
  return testpkg.strongcursortype
as
  strongcursor testpkg.strongcursortype;
begin
  open strongcursor for 'select * from dual';
  return strongcursor;
end; 
/

At compilation you get:

PLS-00455: cursor 'STRONGCURSOR' cannot be used in dynamic SQL OPEN statement

If you need dynamic SQL, use a weak cursor instead (SYS_REFCURSOR). This shouldn't be an issue - the only difference is that a strong cursor validates (and errors if there's a problem) at compilation time, whereas a weak cursor will error at fetch time while executing. Both will error, it's just a matter of when you want the error.