Oracle PL/SQL Tutorial/SQL PLUS Session Environment/SYS CONTEXT
Версия от 16:45, 26 мая 2010; (обсуждение)
Get current program with sys_context("userenv","sessionid") and v$session
<source lang="sql">
SQL> SQL> SQL> set serveroutput on SQL> declare
2 myModule varchar2(48); 3 begin 4 select b.module into myModule from v$session b where b.audsid = sys_context("userenv","sessionid"); 5 dbms_output.put_line("Current Program is " || myModule); 6 end; 7 /
Current Program is SQL*Plus PL/SQL procedure successfully completed. SQL> SQL></source>
sys_context(""userenv"",""sessionid"")
<source lang="sql">
SQL> SQL> SQL> create or replace function myFunction
2 return varchar2 3 authid current_user 4 as 5 myModule varchar2(48); 6 cmd varchar2(500); 7 begin 8 cmd := "select b.module " ||"from v$session b " ||"where b.audsid = sys_context(""userenv"",""sessionid"")"; 9 execute immediate cmd into myModule; 10 return myModule; 11 end; 12 /
Function created. SQL> select myFunction from dual; 1 row selected.
SQL> drop function myFunction; Function dropped. SQL></source>
Use SYS_CONTEXT to retrieve the database name
<source lang="sql">
SQL> SET TERMOUT OFF SQL> SQL> --Specify that new values for the database_name column SQL> --go into a substitution variable called databasae_name SQL> COLUMN database_name NEW_VALUE database_name SQL> SQL> --. SQL> SELECT SYS_CONTEXT("USERENV","DB_NAME") database_name
2 FROM dual;
User System Privileges Page 1 DATABASE_NAME
XE 1 row selected. SQL></source>