Oracle PL/SQL/SQL Plus/sys context

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>