Validate Parallel Pipelined Functions (Dont Assume PQ)

When writing code for parallel processing make sure you allow for running in a non parallel environment as well.

For example here is the code from the previous post being run with PARALLEL QUERY disabled.

SQL> ALTER SESSION DISABLE PARALLEL QUERY;
Session altered.

SQL> DECLARE
  2    v_counter NUMBER := 0;
  3  BEGIN
  4    FOR x IN ( SELECT col1,
  5                      pq,
  6                      TO_CHAR(ts,'HH24:MI:SS') ts,
  7                      seq
  8                 FROM TABLE(demo_function(CURSOR(SELECT *
  9                                                   FROM demo))) ) LOOP
 10      v_counter := v_counter + 1;
 11      DBMS_OUTPUT.PUT_LINE(v_counter || '|' ||
 12                           x.col1 || '|' ||
 13                           x.pq || '|' ||
 14                           x.ts || '|' ||
 15                           x.seq);
 16    END LOOP;
 17  END;
 18  /
PL/SQL procedure successfully completed.

Yes, I did have SERVEROUTPUT ON

Where did the expected output go? The truth is it never even got created because the query shown below raised a NO DATA FOUND exception found exception because no PQ Server was in use.

SELECT server_name
  INTO v_pq
  FROM v$px_process
 WHERE sid = ( SELECT sid
                 FROM v$mystat
                WHERE rownum = 1 );

And the exception was not being passed out of the function so it silently failed. It needs an exception handler like this:

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;

Now it provides these results when called as in the previous post.

 1|1|N/A|19:45:20|1
 2|1|N/A|19:45:21|2
 3|1|N/A|19:45:21|1
 4|1|N/A|19:45:22|2
 5|1|N/A|19:45:22|1
 6|1|N/A|19:45:23|2
 7|2|N/A|19:45:23|1
 8|2|N/A|19:45:24|2
 9|2|N/A|19:45:24|1
10|2|N/A|19:45:25|2
11|2|N/A|19:45:25|1
12|2|N/A|19:45:26|2
13|3|N/A|19:45:26|1
14|3|N/A|19:45:27|2
15|3|N/A|19:45:27|1
16|3|N/A|19:45:28|2
17|3|N/A|19:45:28|1
18|3|N/A|19:45:29|2

Two lessons learned here:

  1. All PLSQL functions (not just Table Functions) silently fail when NO DATA FOUND errors occur
  2. Never assume a function will execute in Parallel just because that what it was designed for

Thanks for reading!