Day: March 20, 2019

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!