Skip to main content

Constantly changing business requirements put pressure on IT teams to change code frequently. In today’s globally integrated environments, however, the idea of a quiet maintenance downtime period is becoming obsolete. Thus, it’s necessary to be able to make changes on a hot running system.

 

Everything I've read about avoiding ORA-04068 says to not use package globals. However, IFS requires module_ and lu_name_, so how can we get around this?

 

The source code for DICTIONARY_SYS reads the values of these globals right from the source code. The globals have to be on lines 2 through 6, but nothing says they can't be commented out!

 

Here is a demonstration of this trick at work. Package "one" uses globals, and package "two" uses fake globals inside a comment. Both get picked up in the sweep for the dictionary cache, but only package "one" gets invalidated on a second run.
 

With package globals:

CREATE OR REPLACE PACKAGE c_test_one_api AUTHID DEFINER IS
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CTestOne';

 

SQL> SELECT c_test_one_api.hello_world FROM DUAL;

HELLO_WORLD
--------------------------------------------------------------------------------
Hello World!

SQL> /* Issued in another session: ALTER PACKAGE c_test_one_api COMPILE; */
SQL> SELECT c_test_one_api.hello_world FROM DUAL;
SELECT c_test_one_api.hello_world FROM DUAL
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "INST1APP.C_TEST_ONE_API" has been
invalidated
ORA-04065: not executed, altered or dropped package "INST1APP.C_TEST_ONE_API"

Without package globals:

CREATE OR REPLACE PACKAGE c_test_two_api AUTHID DEFINER IS
/*
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CTestTwo';
*/

 

SQL> SELECT c_test_two_api.hello_world FROM DUAL;

HELLO_WORLD
--------------------------------------------------------------------------------
Hello World!

SQL> /* Issued in another session: ALTER PACKAGE c_test_two_api COMPILE; */
SQL> SELECT c_test_two_api.hello_world FROM DUAL;

HELLO_WORLD
--------------------------------------------------------------------------------
Hello World!

SQL>

There's one minor caveat: Calls to error_sys accept an LU parameter. You'll need to change those to use hard-code LU names or define them with SQL*Plus DEFINEs, etc.

Hi @durette,
Thank you for this knowledge share !
Appreciate it.
Best Regards,
Yasas


Reply