Blog

Let’s suppose that you want to return on Sys_RefCursor, some values, which does not exist in the database,

thus should be created on the fly.

One way to do this, is by returning the contents of a nested table to Sys_RefCursor.

Important: In the example below, we have to create first the TYPE on schema level, using the statement;

CREATE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(10);

If we declare it on procedure level (TYPE t_varchar2_tab IS TABLE OF VARCHAR2(10)) it will

not work raising the error: PL/SQL: ORA-00902: invalid datatype

This happens, because we use the TYPE in SQL statement. In this case the type must be

declared on schema level.

Here is the code:

declare

    l_Recordset_O            Sys_Refcursor;
    L_DESC                    VARCHAR2(100);

    Procedure Get_Hardcoded_List (
        P_Recordset_O            Out  Sys_Refcursor) IS

    l_num number;       
    v_array t_varchar2_tab; 

    BEGIN

        v_array :=  t_varchar2_tab();

        for l_num in 1..10 loop
            v_array.extend;
            v_array(l_num) := 'A' || l_num;
        end loop;

    OPEN P_Recordset_O for 
    SELECT * FROM table(cast(v_array as t_varchar2_tab));

    END Get_Hardcoded_List;    

begin

    Get_Hardcoded_List (
        P_Recordset_O            => L_Recordset_O);
       
          loop
              fetch L_Recordset_O into L_DESC;
              exit when L_Recordset_O%notfound;
              dbms_output.put_line(L_DESC);
          end loop;        

end;

Script output: