2-7. Obtaining Environment and Session Information
Problem
You want to obtain environment and session information such as the name and IP address of the current user so that the values can be stored into local variables for logging purposes.
Solution
Make use of the SYS_CONTEXT built-in function to query the database for the user's information. Once you have obtained the information, then store it into a local variable. At that point, you can do whatever you'd like with it, such as save it in a logging table. The following code block demonstrates this technique:
<<obtain_user_info>>
DECLARE
username varchar2(100);
ip_address varchar2(100);
BEGIN
SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
INTO username, ip_address
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('The connected user is: ' || username || ', and the IP address
is ' ||
ip_address);
END;
Once this code block has been run, then the user's information should be stored into the local variables that have been declared within it.
How It Works
You can use the SYS_CONTEXT function to obtain important information regarding the current user's environment, among other things. It is oftentimes used for auditing purposes so that a particular code block can grab important information about the connected user such as you've seen in the solution to this recipe. The SYS_CONTEXT function allows you to define a namespace and then place parameters within it so that they can be retrieved for use at a later time. The general syntax for the use of SYS_CONTEXT is as follows:
SYS_CONTEXT('namespace','parameter'[,length])
A namespace can be any valid SQL identifier, and it must be created using the CREATE_CONTEXT statement. The parameter must be a string or evaluate to a string, and it must be set using the DBMS_SESSION.SET_CONTEXT procedure. The call to SYS_CONTEXT with a valid namespace and parameter will result in the return of a value that has a VARCHAR2 datatype. The default maximum length of the returned value is 256 bytes. However, this default maximum length can be overridden by specifying the length when calling SYS_CONTEXT. The length is an optional parameter. The range of values for the length is 1 to 4000, and if you specify an invalid value, then the default of 256 will be used.
The USERENV namespace is automatically available for use because it is a built-in namespace provided by Oracle. The USERENV namespace contains session information for the current user. Table 2-1 lists the parameters that are available to use with the USERENV namespace.
Table 2-1. USERENV Parameter Listing
When SYS_CONTEXT is used within any query, it is most commonly issued against the DUAL table. The DUAL table is installed along with the data dictionary when the Oracle Database is created. This table is really a dummy table that contains one column that is appropriately named DUMMY. This column contains the value X.
SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
Among other things, DUAL is useful for obtaining values from the database when no actual table is needed. Our solution case is such a situation.