1. creating a package
2. inside package create 2 procedure and 2 function
3. now add audit to capture start timing and end timing for each procedure and function in audit table
4. TBL_AUDIT_LOG(process_name,start_time,end_time,created_by,created_dttm)

CodePudding user response：

Although you can create a table within a package (actually, its procedure) using dynamic SQL (see execute immediate), you shouldn't do that. That's bad practice. Just create the table at SQL level as

create table TBL_AUDIT_LOG
  (process_name   varchar2(60),
   start_time     date,
   end_time       date,
   created_by     varchar2(30),
   created_dttm   date);

and use it from within the package. I'd suggest you NOT to insert directly into the table from every program unit in the package - create yet another, logging procedure which will accept certain parameters and perform logging itself, possibly as an autonomous transaction. Something like this:

procedure p_log (par_proc_name in varchar2, par_start in date, par_end in date) as
begin
  insert into tbl_audit_log (process_name, start_time, end_time, created_by, created_dttm)
  values (par_proc_name, par_start, par_end, user, sysdate);

  commit;
end;

CodePudding user response：

I would create a procedure for logging :

PROCEDURE INSERT_LOG(P_FROM IN VARCHAR2,
                       P_START_DATE   IN DATE,
                       P_END_DATE   IN DATE) IS  
BEGIN
INSERT INTO TBL_AUDIT_LOG VALUES (P_FROM , P_START_DATE, P_START_DATE,USER, NULL);
COMMIT;
END;

Not: i am not sure what is created_dttm - so i passed null.

And inside your procedures, you can use it like this :

  PROCEDURE TEST_P(P_VAR IN NUMBER) IS
    C_LOG_START DATE;
    C_LOG_END DATE;
  BEGIN
  
C_LOG_START := SYSDATE ;  
/*
YOUR PROC LOGİC
*/
C_LOG_END := SYSDATE ;

-- CALL LOG ROC HERE 
INSERT_LOG('TEST_P',C_LOG_START,C_LOG_END,NULL);
  END;

its smiliar with functions, but you have to declare your functions like this :

  FUNCTION GET_CUSTOMER(REFNO VARCHAR2) RETURN TBL_CUSTOMER_PROFILE IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    IS_NODATA BOOLEAN;
    LORESULT  TBL_CUSTOMER_PROFILE;
  BEGIN
/*function logic*/
  END;

Check here , it is very important :