I have previously noted that numeric constants declared at the package level don’t create package state, and that strings and (weirdly) negative numbers do create package state. Happily, under Oracle 19c and above, no package level constants create package state.
We can test this using a very basic package (this test done using XE 21c):
CREATE OR REPLACE PACKAGE pkg_constants
AS
FUNCTION get_string
RETURN VARCHAR2;
FUNCTION get_number
RETURN NUMBER;
FUNCTION get_negative_number
RETURN NUMBER;
END pkg_constants;
/
CREATE OR REPLACE PACKAGE BODY pkg_constants
AS
c_string CONSTANT VARCHAR2(20) := 'abc';
c_number CONSTANT NUMBER := 100;
c_negative_number CONSTANT NUMBER := -100;
FUNCTION get_string
RETURN VARCHAR2
AS
BEGIN
RETURN c_string;
END get_string;
FUNCTION get_number
RETURN NUMBER
AS
BEGIN
RETURN c_number;
END get_number;
FUNCTION get_negative_number
RETURN NUMBER
AS
BEGIN
RETURN c_negative_number;
END get_negative_number;
END pkg_constants;
/
Using a simple query:
select pkg_constants.get_string as string_value , pkg_constants.get_number as number_value , pkg_constants.get_negative_number as negative_number from dual;
we can list the package level values:
STRING_VALUE NUMBER_VALUE NEGATIVE_NUMBER --------------- ------------ --------------- abc 100 -100
In another session if we now recompile the package body with new values for the constants:
CREATE OR REPLACE PACKAGE BODY pkg_constants
AS
c_string CONSTANT VARCHAR2(20) := 'xyz';
c_number CONSTANT NUMBER := 99;
c_neg_number CONSTANT NUMBER := -99;
FUNCTION get_string
RETURN VARCHAR2
AS
BEGIN
RETURN c_string;
END get_string;
FUNCTION get_number
RETURN NUMBER
AS
BEGIN
RETURN c_number;
END get_number;
FUNCTION get_negative_number
RETURN NUMBER
AS
BEGIN
RETURN c_negative_number;
END get_negative_number;
END pkg_constants;
/
When we rerun the query in our previous session, we get the new values and not ORA-04068:
select pkg_constants.get_string as string_value , pkg_constants.get_number as number_value , pkg_constants.get-negative_number as negative_number from dual; STRING_VALUE NUMBER_VALUE NEGATIVE_NUMBER< --------------- ------------ --------------- xyz 99 -99
If we remove the CONSTANT keywords in the package body, i.e. turn the constants into package level variables, then rerunning the query after the second compilation of the package body results in the familiar ORA-04068.
Mark Hoxey 