Category: Table Functions

All of the previous examples in this set of posts focus on returning rows from a REF Cursor Loop.

This post explains what happens when rows are returned from outside the REF Cursor Loop. As always – lets dive right into the code!

-- a simple demo table
CREATE TABLE demo
( col1 NUMBER,
  col2 NUMBER );

-- object types for return values from the function
CREATE TYPE return_o AS OBJECT ( col1    NUMBER,         -- col1 value from REF Cursor
                                 pq      VARCHAR2(5),    -- PQ Server row process by
                                 ts      DATE,           -- timestamp row returned
                                 rowtype VARCHAR2(10) ); -- (In Loop OR After Loop)
/
CREATE TYPE return_t AS TABLE OF return_o;
/

-- a package to define the REF Cursor
CREATE OR REPLACE PACKAGE demo_curs AS
  TYPE v_demo_curs IS REF CURSOR RETURN demo%ROWTYPE;
END;
/

-- Some demo data
BEGIN
  FOR counter1 IN 1..2 LOOP
    FOR counter2 IN 1..2 LOOP
      INSERT INTO demo
      VALUES(counter1,counter2);
    END LOOP;
  END LOOP;
END;
/

-- And last but not least - the function
CREATE OR REPLACE FUNCTION demo_function ( p_curs demo_curs.v_demo_curs )
                           RETURN return_t
                           PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(col1) )
                           ORDER p_curs BY ( col1 )
                           PIPELINED AS
  v_col1 NUMBER := 0;
  v_col2 NUMBER;
  v_pq VARCHAR2(5);
BEGIN
  BEGIN
    SELECT server_name
      INTO v_pq
      FROM v$px_process
     WHERE sid = ( SELECT sid
                     FROM v$mystat
                    WHERE rownum = 1 );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_pq := 'N/A';
  END;

  LOOP
    FETCH p_curs INTO v_col1, v_col2;
    EXIT WHEN p_curs%NOTFOUND;
    -- return row from inside cursor loop
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'In Loop'));
  END LOOP;

  -- return row from outside cursor loop
  PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'After Loop'));

  RETURN;

END;

Now to see what is returned in non-parallel mode.

SQL> BEGIN
  2    FOR x IN ( SELECT col1,
  3                      pq,
  4                      rowtype
  5                 FROM TABLE(demo_function(CURSOR(SELECT *
  6                                                   FROM demo))) ) LOOP
  7      DBMS_OUTPUT.PUT_LINE(x.col1 || '|' ||
  8                           x.pq   || '|' ||
  9                           x.rowtype);
 10    END LOOP;
 11  END;
 12  /
1|N/A|In Loop
1|N/A|In Loop
2|N/A|In Loop
2|N/A|In Loop
2|N/A|After Loop

That output is straightforward. Two In Loop rows for COL1=1, Two In Loop rows for (COL1=2) and one After Loop row with COL1=2. Note that the COL1 value of 2 was left over from the preceding cursor loop row.

Now for the parallel mode results:

0|P002|After Loop
2|P001|In Loop
2|P001|In Loop
2|P001|After Loop
1|P000|In Loop
1|P000|In Loop
1|P000|After Loop
0|P003|After Loop

Allow me to explain with 3 points:

1. Rows 2 through 4 (shown in Green) came from PQ Server P001 which processed COL1=2 – it included two In Loop rows and one After Loop row
2. Rows 5 through 7 (shown in Blue) show that PQ Server P000 did the same for COL=1
3. Rows 1 and 8 (shown in Red) came from PQ Servers (P002 and P003 respectively) that were used but did not return any rows from the Ref Cursor

Points 1 and 2 are logical because the code in the function was essentially run twice so two After Loop rows were created.

NB: There is no way to predict how many PQ servers will be used. For example added rows for COL1=3 would not guarantee a 3rd PQ Server would be used.

Point 3 requires some code changes to deal with. I’ll use a boolean to check if rows were returned by the Ref Cursor.

CREATE OR REPLACE FUNCTION demo_function ( p_curs demo_curs.v_demo_curs )
                  RETURN return_t
                  PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(col1) )
                  ORDER p_curs BY ( col1 )
                  PIPELINED AS
  v_col1 NUMBER := 0;
  v_col2 NUMBER;
  v_pq VARCHAR2(5);
  v_rows_found BOOLEAN := FALSE; -- were rows returned from cursor
BEGIN
  BEGIN
    SELECT server_name
      INTO v_pq
      FROM v$px_process
     WHERE sid = ( SELECT sid
                     FROM v$mystat
                     WHERE rownum = 1 );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_pq := 'N/A';
  END;

  LOOP
    FETCH p_curs INTO v_col1, v_col2;
    EXIT WHEN p_curs%NOTFOUND;
    v_rows_found := TRUE;
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'In Loop'));
  END LOOP;

  -- if rows returned by cursor then return After Loop row
  IF v_rows_found THEN
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'After Loop'));
  END IF;

  RETURN;
END;

And that produces much better results.

1|P000|In Loop
1|P000|In Loop
1|P000|After Loop
2|P001|In Loop
2|P001|In Loop
2|P001|After Loop

Remember these behaviors when returning rows from outside the cursor loop in parallel table functions.

Thanks for reading!