2-7. Obtaining Environment and Session Information
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.
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 ...