0

Hi

I am working on a procedure to log the users and times of our reports. Our reports are all executed from stored procedures. The generic logging proc which will be placed in the 30+ report procedures.

I'm having a problem with getting the start and end times for the report. I know I can get both times by putting the current time in a variable before I execute the report and doing the same afterwards. This is however a last option.

Currently, I call the proc before I execute a report, that way it gives me the start time of the report which is the minimum requirement. Here is an example of the proc:

CREATE OR REPLACE PROCEDURE proc_rpt_log_stats(P_Proc_Name IN varchar2, 
                                               P_Proc_Param IN varchar2,
                                               O_Error_Message IN OUT VARCHAR2)
IS

    L_Proc_Name  VARCHAR2(30) := 'PROC_RPT_LOG_STATS';
    --L_Proc_Param VARCHAR2(100) := 'Proc Params';
    L_OSuser     VARCHAR2(20);

begin

    SELECT sys_context('USERENV', 'OS_USER') 
    INTO L_OSuser
    FROM dual;

    INSERT INTO rpt_logging (PROC_NAME, USER_NO, PROC_PARAM, PROC_START)
    VALUES                  (P_Proc_Name,
                             L_OSuser,
                             P_Proc_Param,
                             current_timestamp
                            );
                            
    COMMIT;

EXCEPTION
     WHEN OTHERS THEN
      O_error_message := TO_CHAR(SQLCODE) || SQLERRM || L_Proc_Name;
                        
end proc_rpt_log_stats;

You can see that I use the current_timestamp function to get the start time. Now is there a way that I can move this proc to after I execute the report without needing to pass a parameter of the starting time or that the only option?

Thanks in advance.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by sa_coder
0

If I remember correctly, at a previous company I worked for we had to log stuff like this too.

We had a procedure/function that was called by all our procedures. It had the procedure that was being run, the current time, and a message "X Proc starting" or "X Proc ending" sent. We placed the procedure/function at the beginning and end of all of the procedures we had running. It would then write the info to a log.

Does this help?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.