This post shows how to verify a pipelined-parallel function is working as expected by adding timestamps and PQ names to the output.
First let’s create a simple two column table and add some test data.
CREATE TABLE demo ( col1 NUMBER, col2 NUMBER ); BEGIN FOR counter1 IN 1..3 LOOP FOR counter2 IN 1..3 LOOP INSERT INTO demo VALUES(counter1,counter2); END LOOP; END LOOP; END; /
Define object types for return values from the function.
CREATE TYPE return_o AS OBJECT ( col1 NUMBER, pq VARCHAR2(5), -- name of PQ server ts DATE, -- date of (HH24:MI:SS) of pipe row seq NUMBER ); -- sequence of pipe row (1 or 2) / CREATE TYPE return_t AS TABLE OF return_o; /
Define a package for for the REF CURSOR definition.
CREATE OR REPLACE PACKAGE demo_curs AS TYPE v_demo_curs IS REF CURSOR RETURN demo%ROWTYPE; END; /
And now for the function itself.
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 := -1; v_col2 NUMBER; v_pq VARCHAR2(5); BEGIN -- determine which PQ Server is being used for the current -- Oracle session executing the function SELECT server_name INTO v_pq FROM v$px_process WHERE sid = ( SELECT sid FROM v$mystat WHERE rownum = 1 ); -- fetch values from the cursor and pipe them twice with a -- one second sleep in the middle. -- apply a sequence number (1 or 2) to identify upstream LOOP FETCH p_curs INTO v_col1, v_col2; EXIT WHEN p_curs%NOTFOUND; PIPE ROW(return_o(v_col1,v_pq,SYSDATE,1)); DBMS_LOCK.SLEEP(1); PIPE ROW(return_o(v_col1,v_pq,SYSDATE,2)); END LOOP; RETURN; END; /
And now to call the function using an anonymous PLSQL block.
ALTER SESSION FORCE PARALLEL QUERY; DECLARE v_counter NUMBER := 0; BEGIN FOR x IN ( SELECT col1, pq, TO_CHAR(ts,'HH24:MI:SS') ts, seq FROM TABLE(demo_function(CURSOR(SELECT * FROM demo))) ) LOOP v_counter := v_counter + 1; DBMS_OUTPUT.PUT_LINE(v_counter || '|' || x.col1 || '|' || x.pq || '|' || x.ts || '|' || x.seq); END LOOP; END; / ALTER SESSION DISABLE PARALLEL QUERY;
Here is the output.
1|1|P000|18:50:43|1 2|1|P000|18:50:44|2 3|1|P000|18:50:44|1 4|1|P000|18:50:45|2 5|1|P000|18:50:45|1 6|1|P000|18:50:46|2 7|2|P001|18:50:43|1 8|2|P001|18:50:44|2 9|2|P001|18:50:44|1 10|2|P001|18:50:45|2 11|2|P001|18:50:45|1 12|2|P001|18:50:46|2 13|3|P001|18:50:46|1 14|3|P001|18:50:47|2 15|3|P001|18:50:47|1 16|3|P001|18:50:48|2 17|3|P001|18:50:48|1 18|3|P001|18:50:49|2
The column in the output are as follows:
- The incremental row number assigned by the anonymous block
- The the COL1 value in play (1,2 or 3)
- The PQ Server in use
- The time the row was returned to the anonymous block
- The sequence (1 or 2) of the row
Key points about the result set are:
- The first 6 rows identify that COL=1 went to PQ Server P000 and sequence 1 and 2 were always 1 second apart.
- Rows 7 through 12 identify that COL1=2 went to PQ Server P001 and sequence 1 and 2 were always 1 second apart.
- Rows 13 through 18 identify that COL=3 went to PQ Server P001 (same as COL1=2) and sequence 1 and 2 were always 1 second apart.
- Rows 12 and 13 went through PQ Server P001 at the same second even though they are different COL1 values
When using writing Table Functions (especially parallel and pipelined ones) I highly recommend having a simple set of test data and expected results such as the ones shown in this post.
It’s very helpful for verifying any code modifications have changed the overall processing.
Its also valuable for verifying Oracle is behaving correctly.
For more complex functions I recommend adding PQ Server and timestamp columns to the return types. They don’t have to be used by the application but can be very useful for debugging.
Check out the next post here on validating table functions.
Thanks for reading!