PL SQL - Procedure - table that holds a record of users who executed a procedure

Question

Hi and apologies in advance if the question has already been asked. I haven't been able to come across the answer.

I'm wondering if there is a table that holds a record of oracle usernames that have executed a particular procedure or function.

I'm trying to create a procedure that can be called as a subprogram by another procedure. The procedure which i'm looking to create will create a log entry every time the other procedure is executed. Example below;

User_Name = The Oracle user name of the person who executes the function.

Name = The name of the procedure or function.

LastCompileDT = The date/time the function or procedure was last compiled.

I'm a bit stuck on where to source the data from. I've come across the all_source table but it only gives me the owner of the procedure and not the executing user.

Any feedback would be greatly appreciated.

Thanks

Answer 1

There might be a couple of ways to do that. Maybe someone else can suggest a method of extracting all this data from one data dictionary view. However, my method would be like this:

1. User_Name: use the keyword USER. It returns the Oracle user that executed the procedure:

   SELECT USER FROM DUAL;
   

   However, if you are interested in the OS user who executed that procedure, then you can use the following

   SELECT sys_context( 'userenv', 'os_user' ) FROM DUAL;
   

   More on this here. To my knowledge, this can be fetched on the fly only, and it is not logged anywhere by default. So you need to run it when you call the procedure.

2. Procedure Name: &

3. LastCompileDT : can be fetched from the view USER_OBJECTS

   SELECT OBJECT_NAME, LAST_DDL_TIME
   FROM USER_OBJECTS
   WHERE OBJECT_TYPE = 'PROCEDURE'
   AND OBJECT_NAME = '<YOUR PROCEDURE NAME>';