Oracle RESET_PACKAGE不会在会话中重置变量的值 [英] Oracle RESET_PACKAGE does not reset value of a variable in the session

问题描述

我有一个将JDBC连接池化的应用程序.这与我的问题有关.为了简单起见，假设我有1个连接，并且需要设置一个变量，然后重置会话/上下文状态.但是，这种想法不是反向/重置"app1_ctx"变量，尤其是在实际情况下，用户可以输入许多设置许多变量的过程，因此我需要的是一个清除所有与会话相关的变量甚至重启会话的过程. (请检查问题也无法理解问题)

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 )

以下是我的程序:

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;

然后在检查'empno'的值时:

Then when checking value of 'empno':

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

我得到employee_num = null

I get employee_num = null

要设置empno变量，我们运行以下命令:

To set the empno variable we run the following:

begin 
APP1_CTX_PACKAGE.SET_EMPNO(11);
end;

然后，当重新检查'empno'的值时，我得到employee_num = 11

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

我们需要的是在此之后清除所有会话/程序包变量.

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

我尝试使用 RESET_PACKAGE 或以下类似过程.

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.

我该如何实现?

我不确定如何使用 CLEAR_ALL_CONTEXT 过程.

I am not sure how use CLEAR_ALL_CONTEXT procedure.

推荐答案

dbms_session.clear_all_context( 'app1_ctx' );

您需要将与第一个参数传递给set_context的名称空间传递给clear_all_context.

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;

在此示例中，没有包变量，因此无需调用reset_package或modify_package_state.

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

这篇关于Oracle RESET_PACKAGE不会在会话中重置变量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！