SYS_CONTEXT
![Description of sys_context.gif follows](https://i-blog.csdnimg.cn/blog_migrate/a53e4ba6ad5a8e9d5f7a3ea6d6bf69c5.gif)
Description of the illustration sys_context.gif
SYS_CONTEXT
returns the value ofparameter
associated with the contextnamespace
at the current instant(当前实例). You can use this function in both SQL and PL/SQL statements.SYS_CONTEXT
must be executed locally.
the context(context,即上下文,在数据库中是类似于表等概念,是一个数据库对象)namespace
must already have been created, and the associated parameter
and its value must also have beenset using the DBMS_SESSION
.set_context
procedure. Thenamespace
must be a valid SQL identifier. Theparameter
name can be any string. It is not case sensitive, but it cannot exceed 30 bytes in length.
The data type of the return value isVARCHAR2
. The default maximum size of the return value is 256 bytes(默认返回值的数据类型最大所占字节为256字节). You canoverride this default (maximum size) by specifying the optional length
parameter, which must be aNUMBER
or a value that can be implicitly converted to NUMBER(参数
. The valid range of values is 1 to 4000 bytes(参数length值的范围为1到4000). If you specify an invalid value, then Oracle Database ignores it and uses the default.length
的数据类型必须是一个NUMBER
类型或是能隐式转换为
)NUMBER
的类型
Oracle provides the following built-in(即内建,也就是说已经预先在数据库中创建了的) namespaces:
-
USERENV
- Describesthe current session(返回当前会话的信息). The predefined(预定义) parameters of namespaceUSERENV
are listed in Table 5-11. -
SYS_SESSION_ROLES
- Indicates whethera specified role is currently enabled for the session. This namespace is available starting with Oracle Database 11g Release 2 (11.2.0.4).
关于context这个数据库对象,参见:
-
Oracle Database Security Guide for information on using the application context feature in your application development
-
CREATE CONTEXT for information on creating user-defined context namespaces(如何创建用户自定义的context)
-
Oracle Database PL/SQL Packages and Types Reference for information onthe
DBMS_SESSION
.set_context
procedure(如何使用theDBMS_SESSION
.set_context
来创建并设置context
里的参数)
注释:
context类似于schema这个概念一样,就是一个容器,里面存放多个参数。
Examples
The following statement returns the name of the user who logged onto the database:
CONNECT OE/password
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
FROM DUAL;
SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
OE
The following example queries the SESSION_ROLES
data dictionary view to show thatRESOURCE
is the only role currently enabled for the session. It then uses theSYS_CONTEXT
function to show that the RESOURCE
role is currently enabled for the session and theDBA
role is not.
CONNECT OE/password
SELECT role FROM session_roles;
ROLE
--------
RESOURCE
SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'RESOURCE')
FROM DUAL
SYS_CONTEXT('SYS_SESSION_ROLES','RESOURCE')
--------------------------------------
TRUE
SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA')
FROM DUAL;
SYS_CONTEXT('SYS_SESSION_ROLES','DBA')
--------------------------------------
FALSE
The following hypothetical example returns the group number that was set as the value for the attributegroup_no
in the PL/SQL package that was associated with the context hr_apps
when hr_apps
was created(假设名为hr_apps
的context已经创建好了,group_no
是它的一个参数):
SELECT SYS_CONTEXT ('hr_apps', 'group_no') "User Group" FROM DUAL; 附加: Oracle recommends that you use theSYS_CONTEXT
function with the built-inUSERENV
namespace for current functionality(即USERENV(OPTION)函数). 译文:SYS_CONTEXT函数可以以
SYS_CONTEXT ('USERENV', 'OPTION')取代
USERENV(OPTION)函数的功能。
USERENV(OPTION)函数介绍:
USERENV(OPTION),返回当前的会话信息.
OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
OPTION='LANGUAGE'返回数据库的字符集.
OPTION='SESSIONID'为当前会话标识符.
OPTION='ENTRYID'返回可审计的会话标识符.
OPTION='LANG'返回会话语言名称的ISO简记.
OPTION='INSTANCE'返回当前的实例.
OPTION='terminal'返回当前计算机名
例子: SELECT USERENV('LANGUAGE') FROM DUAL;
疑问:
参数 ENTRYID 是什么意思?