Oracle: How to populate/insert row to a Ref Cursor

oracleplsqlsys-refcursor

Really need help regarding Ref Cursor. I have a Stored Procedure GET_PERSONROLES that have parameter type ref cursor. I just wanted to pupulate this ref cursor manually like inserting a row to the refcursor.
Can I insert a row into a refcursor though a loop?
Thank you in advance.

The procedure depends on this publicly declared type:

create or replace package types 
as 
    type cursorTypePersonRole is ref cursor; 
end;

Here is my pseudo-codeL

create or replace PROCEDURE GET_PERSONROLES
( 
  P_CURSOR IN OUT types.cursorTypePersonRole
) AS
  REFCUR SYS_REFCURSOR; 
  TYPE REFTABLETYPE IS RECORD (
    IS_MANAGER_LEVEL1 VARCHAR2(1),
    IS_MANAGER_LEVEL2 VARCHAR2(1)
  );
  TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
  PERSONROLES_TABLETYPE TABLETYPE; 
BEGIN
  --calls another stored proc to populate REFCUR with data without problem
  MY_STOREDPROC('12345', REFCUR);
  LOOP
    FETCH REFCUR BULK COLLECT INTO PERSONROLES_TABLETYPE;
    EXIT WHEN PERSONROLES_TABLETYPE.COUNT = 0;
    FOR indx IN 1 .. PERSONROLES_TABLETYPE.COUNT 
    LOOP
       -- I'm able to query perfectly the values of IS_MANAGER_LEVEL1 and IS_MANAGER_LEVEL 2
       -- I'm aware that the below codes are wrong
       -- However this means I wanted to insert these values to a row of the cursor if possible
       -- Do some logic to know what data will be assigned in the row.
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL1 = 'Y' then
           P_CURSOR := <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>
       end if;
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL2 = 'Y' then
           P_CURSOR := <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>
       end if;
    END LOOP;
  END LOOP;     
  CLOSE REFCUR;
END GET_PERSONROLES;

Best Answer

A ref cursor is not a variable: it is a pointer to a result set which is consumed by the act of reading it. The result set itself is immutable.

Immutability makes sense, because it reflects Oracle's emphasis on read consistency.

The simplest way to produce the output you appear to want is to create a SQL Type 

   open P_CURSOR for 
       select IS_MANAGER_LEVEL1,
              IS_MANAGER_LEVEL2 
       from table ( PERSONROLES_TABLETYPE );

This will work in 12c; in earlier versions to use the table() call like this you may need to declare REFTABLETYPE and TABLETYPE as SQL types( rather than in PL/SQL).

"Ok edited it now"

Alas your requirements are still not clear. You haven't given us the structure of the output ref cursor or shown what other processing you want to undertake.

However, given the title of your question, let's have a guess. So:

create or replace PROCEDURE GET_PERSONROLES ( P_CURSOR IN OUT types.cursorTypePersonRole) AS
  REFCUR SYS_REFCURSOR; 
  TYPE REFTABLETYPE IS RECORD (IS_MANAGER_LEVEL1 VARCHAR2(1),
    IS_MANAGER_LEVEL2 VARCHAR2(1));
  TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
  PERSONROLES_TABLETYPE TABLETYPE; 
  personrole_rec PersonRole%rowtype;
  type personrole_nt is table of PersonRole%rowtype;
  personroles_recs personrole_nt := new personrole_nt() ; 
BEGIN
    MY_STOREDPROC('12345', REFCUR);
    FETCH REFCUR BULK COLLECT INTO PERSONROLES_TABLETYPE;
    FOR indx IN 1 .. PERSONROLES_TABLETYPE.COUNT LOOP
       /* in the absence of requirements I'm just making some stuff up */
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL1 = 'Y' then
           personrole_rec.whatever1 := 'something';
       else
           personrole_recc.whatever1 := null;
       end if;
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL2 = 'Y' then
           personrole_rec.whatever2 := 'something else';
       else
           personrole_recc.whatever2 := null;
       end if;
       if personrole_rec.whatever1 is not null 
       or personrole_rec.whatever2 is mot null then
              personroles_recs.exend();
              personroles_recs(personroles_recs.count()) := personroles_rec;
       end if;
  END LOOP;     
  CLOSE REFCUR;
  open p_cursor for 
      select * from table ( personroles_recs );
END GET_PERSONROLES;

This code uses a second collection to store the desired output. Like your code it reads the populated collection and evaluates the attributes of each row. If a value which means the criteria it sets an attribute in a rowtype variable. If one or both attributes are set it populates a new row in a second collection. At the end of the procedure it opens the ref cursor using a table() function call on the second collection.

Note that you do not need the nested loop: you're not using the LIMIT clause so your coder reads the entire cursor into the collection in one swoop.

The implemented rules may not be exactly what you want (because you haven't explained exactly what you want) but this should give you the general idea.

Note that, depending on exactly what processing is masked by <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>, the simpler approach could still be feasible:

open P_CURSOR for 
   select case when IS_MANAGER_LEVEL1 = 'Y' then 'YES' else 'NO' end,
          case when IS_MANAGER_LEVEL2 = 'Y' then 'YES' else 'NO' end
   from table ( PERSONROLES_TABLETYPE );
Related Topic