SQL: SYS_CONTEXT

In my experience so far, I've seen very few places where SYS_CONTEXT is used. It is typically used in conjunction with CONTEXTs and also Virtual Private Database (VPD).

Oracle has a built in namespace called USERENV and their is a wealth of information you can retrieve from there. I wrote up a quick view to demonstrate the use of SYS_CONTEXT:

SELECT
  SYS_CONTEXT( 'USERENV', 'ACTION' ) action,
  SYS_CONTEXT( 'USERENV', 'AUTHENTICATION_METHOD' ) authentication_method,
  SYS_CONTEXT( 'USERENV', 'BG_JOB_ID' ) bg_job_id,
  SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) client_identifier,
  SYS_CONTEXT( 'USERENV', 'CLIENT_INFO' ) client_info,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMAID' ) current_schemaid,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) current_schema,
  SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) current_user,
  SYS_CONTEXT( 'USERENV', 'DB_DOMAIN' ) db_domain,
  SYS_CONTEXT( 'USERENV', 'DB_NAME' ) db_name,
  SYS_CONTEXT( 'USERENV', 'DB_UNIQUE_NAME' ) db_unique_name,
  SYS_CONTEXT( 'USERENV', 'HOST' ) host,
  SYS_CONTEXT( 'USERENV', 'INSTANCE' ) instance,
  SYS_CONTEXT( 'USERENV', 'INSTANCE_NAME' ) instance_name,
  SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) ip_address,
  SYS_CONTEXT( 'USERENV', 'ISDBA' ) isdba,
  SYS_CONTEXT( 'USERENV', 'LANG' ) lang,
  SYS_CONTEXT( 'USERENV', 'LANGUAGE' ) language,
  SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) network_protocol,
  SYS_CONTEXT( 'USERENV', 'NLS_CALENDAR' ) nls_calendar,
  SYS_CONTEXT( 'USERENV', 'NLS_CURRENCY' ) nls_currency,
  SYS_CONTEXT( 'USERENV', 'OS_USER' ) os_user,
  SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) server_host,
  SYS_CONTEXT( 'USERENV', 'TERMINAL' ) terminal
FROM dual

Which yields the following:

ACTION:  NULL
AUTHENTICATION_METHOD:  PASSWORD
BG_JOB_ID:  NULL
CLIENT_IDENTIFIER:  NULL
CLIENT_INFO:  NULL
CURRENT_SCHEMAID:  88
CURRENT_SCHEMA:  CJUSTICE
CURRENT_USER:  CJUSTICE
DB_DOMAIN:  NULL
DB_NAME:  testing
DB_UNIQUE_NAME:  testing
HOST:  cdj-laptop
INSTANCE:  1
INSTANCE_NAME:  TESTING
IP_ADDRESS:  192.168.1.4
ISDBA:  FALSE
LANG:  US
LANGUAGE:  AMERICAN_AMERICA.WE8MSWIN1252
NETWORK_PROTOCOL:  tcp
NLS_CALENDAR:  GREGORIAN
NLS_CURRENCY:  $
OS_USER:  chet
SERVER_HOST:  oracledb
TERMINAL:  UNKNOWN

Basically, these are global variables that Oracle stores on the current session/user.

You can create a context for just about anything you want. I've used them in the past along with table based security in a stateless environment. In essence, I create a record in a table with a SESSION_ID, I then store that in the context (global variable) and I use that to reconnect to the database each time.

I promise to have an example of using it in the near future.