java - Oracle RESET_PACKAGE does not reset value of a variable in the session

Question

I have an app where JDBC connections are pooled. This is related to my question. For simplicity let's assume I have 1 connection and I need to set a variable then reset session / context state. However the idea is not reverse / reset the 'app1_ctx' variable particularly as in the actual case users can enter many procedures that set many variables so what I need is one procedure that clears all session related variables or even restart session. (please check this question too to understand the problem )

Below is my procedure:

CREATE OR REPLACE CONTEXT app1_ctx USING app1_ctx_package;
CREATE OR REPLACE PACKAGE app1_ctx_package IS
  PROCEDURE set_empno (empno NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY app1_ctx_package IS
  PROCEDURE set_empno (empno NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app1_ctx', 'empno', empno);
  END;
END;

Then when checking value of 'empno':

select SYS_CONTEXT ('app1_ctx', 'empno') employee_num from dual;

I get employee_num = null

To set the empno variable we run the following:

begin 
APP1_CTX_PACKAGE.SET_EMPNO(11);
end;

Then when re-checking value of 'empno', I get employee_num = 11

What we need is to clear all session / package variables after this.

I try to clear session variables using RESET_PACKAGE or the below similar procedures.

begin 
DBMS_SESSION.RESET_PACKAGE;
end;

begin
DBMS_SESSION.modify_package_state(DBMS_SESSION.reinitialize);
end;

begin 
DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES); 
end;

But then when rechecking the variable it still has the same value.

How can I achieve this?

I am not sure how use CLEAR_ALL_CONTEXT procedure.

question from:https://stackoverflow.com/questions/65851641/oracle-reset-package-does-not-reset-value-of-a-variable-in-the-session

Answer 1

dbms_session.clear_all_context( 'app1_ctx' );

You'd need to pass the same namespace to clear_all_context that you passed as the first parameter to set_context.

If you don't know all the contexts your application uses but you do know all the schemas it uses

for ctx in (select * 
              from dba_context
             where schema in (<<schemas your application uses>>))
loop
  dbms_session.clear_all_context( ctx.namespace );
end loop;

In this example, there are no package variables so there would be no need to call reset_package or modify_package_state.