javajdbcweblogic12cucp

Stored Procedure call from Java returning SQLException after PLSQL code changes

I am calling a Stored Procedure using JDBC and the connection is configured as UCP in Oracle Web Logic Server 12c. Below is my sample code snippet.

String query = "{? = call PACK_GLOBAL_VARIABLES.getBatchDate()};
CallableStatement cs = connection.prepareCall(query);
cs.registerOutParameter(1, Types.DATE);
cs.executeUpdate();

The problem is, whenever there is any code change at PL/SQL end then I am always getting this exception:

java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SJN_UAT.PACK_GLOBAL_VARIABLES" has been invalidated
ORA-04065: not executed, altered or dropped package body "SJN_UAT.PACK_GLOBAL_VARIABLES"
ORA-06508: PL/SQL: could not find program unit being called: "SJN_UAT.PACK_GLOBAL_VARIABLES"
ORA-06512: at line 1

This issue gets resolved by means of either of these 2 ways:

  1. Restart the Web Logic Server
  2. Compile the PL/SQL package multiple times (without restarting Web Logic)

None of the above two approaches are feasible in Production environment. Can anyone please help in identifying this issue and the possible resolution?

Solution

  • it is not a good practice to rebuild package when the applcation is running.

    it seems the expected behavior of oracle and java application.

    two possible soluation:

    1. alter system flush shared pool after rebuild package, have protential risk about database
    2. catch error code in java code, if the error code is 4068, reexecute the package