Possible to capture the user that runs your pl/sql script? 1

[wellster34]

I have an interesting situation that I do not know if it is possible to be done or not. I have a pl/sql script (code) that when it is called by any user, I need to capture their information (i.e. User Id) so that I can store it in a log table. I know in my pl/sql I can insert a record into the log table but capturing the id that is calling the pl/sql seems to the hard part...

Is this possible? Is there a session id/user id associated with every single call to pl/sql or oracle?

Anyway, I'm new to this concept and if anyone has seen/done this before, please let me know.

Thanks for you time.

 

[taupirho]

Check out the following link from asktom. It may be of assistance to you:-

http://osi.oracle.com/~tkyte/who_called_me/index.html

 

[saustin]

Hi Wellster,
We use the following in our triggers to capture the user id.

Steve.

v_username varchar2(10);
SELECT TRIM ('#' FROM user) INTO v_username FROM dual;

.... so if #STEVE is the user, v_username will contain STEVE

 

[wellster34]

Thanks so much for everyone's help and I really appreciate it. It works like a charm now. Once again, thanks!!!

 

[mrniice]

Here is what we use in our auditing triggers if you want to get a little fancier and find the osuser and terminal.

SELECT UPPER(username), UPPER(osuser), UPPER(terminal),
UPPER(machine)
INTO v_username,v_osuser,v_terminal,v_machine
FROM v$session
WHERE audsid in (SELECT userenv('SESSIONID') FROM dual);