设置context变量了，却没有结果

显示全部楼层 · 2012-1-4 11:50:44

db: oracle 11.2 on windows
test user: scott
----
scott user have the following privilege:
execute on dbms_session .
create any context.
----
code profile:
create or replace context cont_scott using scott.cont_pkg1;
create or replace package cont_pkg1 is
procedure set_cont;
end;
create or replace package body cont_pkg1 is
procedure set_cont is
v_deptno number(4) ;
begin
DBMS_SESSION.set_context('CONT_SCOTT', 'DEPT_NO', '123');
end;
/
exec cont_pkg1.set_cont
SELECT SYS_CONTEXT('CONT_SCOTT ','DEPT_NO') FROM DUAL ;--- result is null . Why ?

千问 · 2012-1-4 11:50:44

无法重现
SUNDOG315>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SUNDOG315>create context cont_test using test.cont_pkg1;
上下文已创建。
SUNDOG315>create package cont_pkg1 is
2procedure set_cont;
3end;
4/
程序包已创建。
SUNDOG315>create or replace package body cont_pkg1 is
2procedure set_cont is
3v_depno number(4);
4begin
5dbms_session.set_context('CONT_TEST','DEPT_NO','123');
6end;
7end;
8/
程序包体已创建。
SUNDOG315>exec cont_pkg1.set_cont;
PL/SQL 过程已成功完成。
SUNDOG315>select sys_context('CONT_TEST','DEPT_NO') from dual;
SYS_CONTEXT('CONT_TEST','DEPT_NO')
--------------------------------------------------------------------------------
123

千问 · 2012-1-4 11:50:44

在我的机器上确实是空值！
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create or replace package cont_pkg1 is
2procedure set_cont;
3end;
4/
程序包已创建。
SQL> create or replace package body cont_pkg1 is
2procedure set_cont is
3v_deptno number(4) ;
4begin
5DBMS_SESSION.set_context('CONT_SCOTT', 'DEPT_NO', '123');
6end;
7end;
8/
程序包体已创建。
SQL> create or replace context cont_scott using scott.cont_pkg1;
上下文已创建。
SQL> exec cont_pkg1.set_cont
PL/SQL 过程已成功完成。
SQL> SELECT SYS_CONTEXT('CONT_SCOTT ','DEPT_NO') FROM DUAL ;
SYS_CONTEXT('CONT_SCOTT','DEPT_NO')
--------------------------------------------------------------------------------

SQL>

设置context变量 了，却没有结果

设置context变量了，却没有结果