General Logging module for Stored Procedure operation

The target implementation records the start time, end time, running status, and error information of the stored procedure, and encapsulates log records using functions, stored call function source code stored procedure template CREATEORREPLACEPROCEDUREproc_xxIS -- modify flag return value V_AFFECT_LINENUMBER; PROIDNUMBER; BEGIN -- call change flag

The target implementation records the start time, end time, running status, and error information of the stored procedure, and encapsulates log records using functions, stored call function source code stored PROCEDURE template create or replace procedure proc_xx IS -- modify flag return value V_AFFECT_LINE NUMBER; proid number; BEGIN -- call change flag

The target implementation records the start time, end time, running status, and error information of the stored procedure, and stores the stored procedure template for calling the function source code in the form of function encapsulation logs.

Create or replace procedure proc_xx IS -- modify the flag returned value V_AFFECT_LINE NUMBER; proid number; BEGIN -- call the change Flag Function and change the process to 'S' V_AFFECT_LINE: = INSERT_LOG (PROID, 'proc _ xx', WIFI. GLOBAL_PARAM.STATUS_START); -- calls V_AFFECT_LINE: = WIFI. func_xx (); -- modify the flag and set C V_AFFECT_LINE: = WIFI. MODIFY_STATUS (PROID, WIFI. GLOBAL_PARAM.STATUS_CLOSE, ''); exception when others then -- call the change Flag Function to change the process to 'F' V_AFFECT_LINE: = MODIFY_STATUS (PROID, WIFI. GLOBAL_PARAM.STATUS_FAILED, WIFI. GLOBAL_PARAM.LOG_EXCEPTION); END;

Function Template

create or replace function func_xx return int is  begin....            return 1;exception  when others then      set_error_log ();      RETURN 0; end;

Related logging functions

Create or replace function INSERT_LOG (proid out number, program_name IN VARCHAR2, status IN VARCHAR2) return numberis encrypted INTO memory-FUNCTION Description: Initialize log INTO memory begin insert into program_log VALUES (program_log_seq.NEXTVAL, TO_CHAR (SYSDATE, 'yyyymmdd'), program_name, SYSDATE, NULL, status, ''); SELECT program_log_seq.CURRVAL INTO proid FROM dual; COMMIT; RETURN 1; exception when others then return 0; END; create or replace function MODIFY_STATUS (proId IN number, status IN varchar2, proDesc in varchar2) return number is thisDate date; BEGIN ------------------------------------------------------------------------------------------------------------------------------- -------------------------FUNCTION description: modify the running status of the stored procedure in the log table and record the error information about the end time of the start time ---------------------------- thisDate: = sysdate; -- update the status error information update program_log set program_status = status, memo = proDesc where id = proId; -- Update Date: if it is running, update the start date. If it is running successfully or failed, update end run date IF status ='s 'then update program_log set start_date = thisDate where id = proId; ELSIF status = 'C' THEN update program_log set end_date = thisDate where id = proId; ELSIF status = 'F' THEN update program_log set end_date = thisDate where id = proId; COMMIT; RAISE_APPLICATION_ERROR (-20040, 'status IS F'); end if; /* ELSIF status = 'C' OR status = 'F' THEN update program_log set end_date = thisDate where id = proId; end if; */commit; RETURN 1; exception when others then RAISE_APPLICATION_ERROR (-20041, 'status IS F'); RETURN 0; END; create or replace procedure SET_ERROR_LOG ISBEGIN GLOBAL_PARAM.LOG_EXCEPTION: = 'error desc --- '| sqlerrm; commit; RAISE no_data_found; END; -- Procedure; create or replace package GLOBAL_PARAM ISLOG_EXCEPTION VARCHAR2 (2000): = ''; STATUS_START VARCHAR2 (10): ='s '; STATUS_CLOSE VARCHAR2 (10): = 'C'; STATUS_FAILED VARCHAR2 (10): = 'F'; END;

Create a log table

create table PROGRAM_LOG(  ID             NUMBER not null,  BATCH_NUMBER   VARCHAR2(50),  PROGRAM_NAME   VARCHAR2(100),  START_DATE     DATE,  END_DATE       DATE,  PROGRAM_STATUS VARCHAR2(20),  MEMO           VARCHAR2(2000))