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:
- All PLSQL functions (not just Table Functions) silently fail when NO DATA FOUND errors occur
- Never assume a function will execute in Parallel just because that what it was designed for
Thanks for reading!