Actually, I'll retract what I've just said - I confused myself again by jumping between Forms/Apex at my current site and my memory of something else...
Since APEX pools connections, using v$session information for this purpose is invalid. You may wish to see if the view apex_workspace_sessions is available in your version - and useful for your purpose.
OK. I have written a trigger for an audit table (log-in, log-off). However, since my DB utilizes an internal authentication scheme, the user data that come back (i.e. "client_info") is null. I want to be able to see the "APP_USER". All of my other audit tables work, since they are triggered after they have logged on, and they are inserting, updating, or deleting a table. It is easy to retrieve APP_USER data at that point. The trick for me, and it is probably a cake-walk for others, is to get the APP_USER credentials when they are logging in the database.
I hope I clarified what I am trying to ultimately achieve.
The following procedure could be utilized by an application in which all sessions connect to a common Oracle username and security is handled within the application. This would allow the DBA to externally distinguish between user sessions in V$SESSION by examining the CLIENT_INFO column.
CREATE OR REPLACE PROCEDURE set_user(app_user_IN IN VARCHAR2)
Suppose that users JOE SMITH and SALLY DALLY log into the application, which connects to Oracle as the user OPBIP. If the application calls the set_user procedure at login, we can distinguish between the database sessions for Joe and Sally in V$SESSION as follows:
SQL> SELECT sid, username, client_info
2 FROM v$session
3 WHERE username='OPBIP';
SID USERNAME CLIENT_INFO
--------- ---------- --------------------
14 OPBIP JOE SMITH
24 OPBIP SALLY DALLY
Ahh, the theory works fine in a Forms environment, but in APEX the v$session entry for APEX_PUBLIC_USER might be shared amongst a number of APEX sessions.
In my understanding - at any instant in time, a session might be used to render a particular page, and that's when you'll see information such as
Module: PARSING_SCHEMA/APEX:APP 110:2
Action: Processes - point: BEFORE_HEADER
but during rest there will be no identifying information - it's just waiting for the next time an APEX page needs interaction.
This was the visual I remembered when first answering your question, but completely forgot about the pooling - sorry.
If you would like a procedure to fire after APEX login, then you would enter the parameterless procedure name in the post-authentication attribute of the authentication scheme. Within that procedure you can refer to v('APP_USER')
Logout would be less reliable as the user could just close the browser window.
i tried placing the name of the procedure "SET_USER" in the post-authentication process attribute of the authentication scheme but I got an error informing me it wasn't able to process "custom post-authentication process".