In the Oracle documentation about Compilation State Management I found this rather stern passage:
The PTF author should not attempt to use the session state (such as PL/SQL package global variables) to store any compilation state. Problems can arise because in a given session all cursors using the PTF will share that state, and other sessions executing the PTF cursor will not see the original compilation state.
In the last two posts in this series I demonstrated that session state refers to current values within an Oracle session. The best example of this is package global variables. I also demonstrated that compilation state is how things looked when the DESCRIBE function was first executed. This remains steadfast across any session that uses the PTF.
Here is a simple example using a global package variable.
-- a very simple package with a single -- public variable stored in SESSION STATE CREATE OR REPLACE PACKAGE other_pkg AS v_other_number NUMBER := 1; END;
And a DESCRIBE function checking the public variable in the package. If its value is greater than 1 then an error is raised. At this point the value is 1 so that becomes part of the Compilation State.
The OPEN procedure displays the value of the public variable.
FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T ) RETURN DBMS_TF.DESCRIBE_T AS BEGIN IF other_pkg.v_other_number > 1 THEN RAISE_APPLICATION_ERROR(-20000,'Other > 1'); ELSE RETURN NULL; END IF; END; PROCEDURE open IS BEGIN DBMS_OUTPUT.PUT_LINE('Other = ' || other_pkg.v_other_number); END;
I’ve left out the rest of the PTF code for the sake of simplicity. Here’s what executions produce.
-- first query returns rows and display the -- public variable as 1 SELECT * FROM qz_poly_func(qz_table); COL1 COL2 ---------- ---------- 10 100 20 200 30 300 Other = 1 -- set the public variable to 99 which is -- greater than 1 so PTF should fail now? BEGIN other_pkg.v_other_number := 99; END; -- NO. The compilation state still sees the -- value as 1 SELECT * FROM qz_poly_func(qz_table); COL1 COL2 ---------- ---------- 10 100 20 200 30 300 Other = 99
The compilation state will not change until a parse forces the DESCRIBE function to execute throwing the error I hard coded into the PTF.
SELECT * FROM qz_poly_func(qz_table) FROM qz_poly_func(qz_table) * ERROR at line 2: ORA-62565: The Describe method failed with error(s). ORA-20000: Other > 1 ORA-06512: at "D.QZ_POLY_PKG", line 14 ORA-06512: at line 22
In a future set of posts I’ll delve further into what causes a PTF parse/describe to happen.
Thanks for reading!