oracle 上下文 context测试

最新推荐文章于 2024-07-19 08:30:00 发布

bamuta

最新推荐文章于 2024-07-19 08:30:00 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/bamuta/article/details/19984463

Oracle 同时被 3 个专栏收录

173 篇文章

订阅专栏

Oracle 管理

55 篇文章

订阅专栏

PL/SQL开发

9 篇文章

订阅专栏

本文档介绍了Oracle数据库中的上下文(Context)测试，包括用户自定义的会话内和全局Context，以及系统Context的创建和使用。示例中展示了创建用户、授予权限以及使用SQL命令创建Context的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle 上下文 context测试

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

创建用户：

SQL> create user conuser identified by conuser;

SQL> grant create session,create procedure,create any context to conuser;

注意权限必要是：create any context

一。用户自定义context

会话内的context

conn conuser/conuser

创建context

create context tancon using tanpack;

SQL> create package tanpack as
procedure tancon_value(tancon_v in varchar2);
end tanpack;
/

SQL> create or replace package body tanpack as
procedure tancon_value(tancon_v in varchar2) is
begin
dbms_session.set_context('tancon','tancon_a1','tancon_v value is:'||tancon_v);
end tancon_value;
end tanpack;
/

SQL> exec tanpack.tancon_value('bamuta');
SQL> select sys_context('tancon','tancon_a1') from dual;

SYS_CONTEXT('TANCON','TANCON_A1')
--------------------------------------------------------------------------------
tancon_v value is:bamuta

创建一个全局context

SQL> create context g_tancon using g_tanpack accessed globally;

SQL> create package g_tanpack as
2 procedure tancon_value(t_value in varchar2);
3 end g_tanpack;
4 /

SQL> create or replace package body g_tanpack as
2 procedure tancon_value(t_value in varchar2) is
3 begin
4 dbms_session.set_context('g_tancon','g_tancon_a1',t_value);
5 end tancon_value;
6 end g_tanpack;
7 /

SQL> exec g_tanpack.tancon_value('bamuta');

PL/SQL procedure successfully completed.

SQL> select sys_context('g_tancon','g_tancon_a1') from dual;

SYS_CONTEXT('G_TANCON','G_TANCON_A1')
--------------------------------------------------------------------------------
bamuta

在其他用户，其他会话里均能查出该值来。

二。系统context

namespace 为USERENV的为系统定义好的，直接使用即可，比如：

SQL> SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SH

详细内容参见：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176