Oracle: Add a new row to a refcursor-CodePudding

I have a procedure which returns refcursor as OUT parameter.

I need to wrap it inside a new procedure and add new row which depends on one of the refcursors field. As all cursors are immutable I am stuck with this problem. Maybe create temporary table?

DECLARE
initial_cursor SYS_REFCURSOR;  
result_cursor SYS_REFCURSOR;  
BEGIN
initial_procedure(initial_cursor);
-- add a new row which depends on initial cursors row and wrap it into result cursor.

END;

Lets consider that initial cursor will consist only of boolean values and new row will be varchar 'TRUE' or 'FALSE' (if cursor value is 0 then 'FALSE', else 'TRUE')

CodePudding user response：

You can create a PIPELINED function to read the cursor and output the rows with an extra row:

CREATE FUNCTION add_row(
  i_cur   IN SYS_REFCURSOR,
  i_value IN NUMBER
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
  v_value NUMBER;
  v_flag  BOOLEAN := TRUE;
BEGIN
  LOOP
    FETCH i_cur INTO v_value;
    EXIT WHEN i_cur%NOTFOUND;
    PIPE ROW (v_value);
    IF v_value = 0 THEN
      v_flag := FALSE;
    END IF;
  END LOOP;
  IF v_flag THEN
    PIPE ROW ('TRUE');
  ELSE
    PIPE ROW ('FALSE');
  END IF;
END;
/

and then you can use that to make the second cursor:

DECLARE
  initial_cursor SYS_REFCURSOR;  
  result_cursor  SYS_REFCURSOR;
  v_value VARCHAR2(20);
  
  PROCEDURE initial_procedure(
    cur OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN cur FOR
      SELECT 1 AS value FROM DUAL;
  END;
BEGIN
  initial_procedure(initial_cursor);
  -- add a new row which depends on initial cursors row and wrap it into result cursor.

  OPEN result_cursor FOR
    SELECT COLUMN_VALUE AS value
    FROM   TABLE(add_row(initial_cursor, 2));
  
  LOOP
    FETCH result_cursor INTO v_value;
    EXIT WHEN result_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_value);
  END LOOP;
END;
/

Which outputs:

1
TRUE

db<>fiddle here