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.