SQL Pkg Example

CREATE OR REPLACE Package Pkg_sys_log is
--Author:li Cong
--created:2009-10-12
--Purpose: To store system-level log information, such as the process error.

---system error log record
PROCEDURE Error_log (P_package_name VARCHAR2,
P_proc_name VARCHAR2,
P_excep_dt DATE,
P_excep_code VARCHAR2,
P_excep_desc VARCHAR2,
P_excep_remk VARCHAR2,
P_line_no number);

---system execution log
PROCEDURE Execute_log (P_package_name VARCHAR2,
P_proc_name VARCHAR2,
P_exec_start_tm DATE,
P_exec_end_tm DATE,
P_EXEC_PEND_TM number,
P_exec_ref_data_rows number,
P_exec_remk VARCHAR2);
END Pkg_sys_log;

CREATE OR REPLACE Package BODY Pkg_sys_log is

--*************************************************************
--Author:keliven LIU
--created:2008-05-20
--PURPOSE: Record the stored procedure run log in the system

--PARAMETER:
--P_package_name VARCHAR2 package Name
--P_proc_name VARCHAR2, process name
--P_excep_dt date, execution date
--P_excep_code VARCHAR2, exception code
--P_excep_desc VARCHAR2, exception description information
--P_excep_remk VARCHAR2, notes, possible values are ' BEGIN ', ' END ', ' ERROR '
--P_line_no number line

--MODIFY history
-Person DATE COMMENTS
-- -------------------------------------------------------------
--*************************************************************

--*************************************************************
--Author:keliven LIU
--created:2008-05-20
--PURPOSE: Record the stored procedure run log in the system

--PARAMETER:
--NAME TYPE DESC
--P_seq_no Number The current value of the primary sequence in the exception stored procedure
--p_proc_name VARCHAR2 stored procedure name
--P_excep_dt date time, including start time, end time, exception occurrence time
--P_excep_code VARCHAR2 exception code
--P_excep_desc VARCHAR2 Exception Description information
--P_excep_remk VARCHAR2 notes, the possible values are ' BEGIN ', ' END ', ' ERROR '
--P_line_no number line

--P_package_name VARCHAR2, package name
--P_proc_name VARCHAR2, process name
--P_exec_start_tm date, execution start time
--P_exec_end_tm date, execution end time
--P_exec_pend_tm number, execution takes time
--p_exec_ref_data_rows number, which involves data volume
--P_exec_remk VARCHAR2 notes, the possible values are ' BEGIN ', ' END ', ' ERROR '

--MODIFY history
-Person DATE COMMENTS
-- -------------------------------------------------------------
--*************************************************************
PROCEDURE Error_log (P_package_name VARCHAR2,
P_proc_name VARCHAR2,
P_excep_dt DATE,
P_excep_code VARCHAR2,
P_excep_desc VARCHAR2,
P_excep_remk VARCHAR2,
P_line_no number) as
PRAGMA autonomous_transaction;
BEGIN
INSERT into Tc_exception_log
(LOG_ID,
Package_name,
Proc_name,
EXCEPTION_TM,
Exception_code,
Exception_desc,
EXCEPTION_REMK,
LINE_NO)
VALUES
(Seq_log. Nextval,
SUBSTR (P_package_name, 1, 120),
SUBSTR (P_proc_name, 1, 120),
P_excep_dt,
SUBSTR (P_excep_code, 1, 200),
SUBSTR (P_excep_desc, 1, 1000),
SUBSTR (P_EXCEP_REMK, 1, 600),
P_LINE_NO);

COMMIT;

EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Stp_running_log ' | | SQLCODE | | ': ' | | SQLERRM);
ROLLBACK;
END Error_log;

---system execution log
PROCEDURE Execute_log (P_package_name VARCHAR2,
P_proc_name VARCHAR2,
P_exec_start_tm DATE,
P_exec_end_tm DATE,
P_EXEC_PEND_TM number,
P_exec_ref_data_rows number,
P_exec_remk VARCHAR2) as
PRAGMA autonomous_transaction;
BEGIN

INSERT into Tc_execute_log
(LOG_ID,
Package_name,
Proc_name,
STRAT_TM,
END_TM,
SPEND_TM,
Ref_data_rows,
REMK)
VALUES
(Seq_log. Nextval,
SUBSTR (P_package_name, 1, 120),
SUBSTR (P_proc_name, 1, 120),
P_EXEC_START_TM,
P_EXEC_END_TM,
P_EXEC_PEND_TM,
P_exec_ref_data_rows,
SUBSTR (P_exec_remk, 1, 1000));
COMMIT;

EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Tc_execute_log ' | | SQLCODE | | ': ' | | SQLERRM);
ROLLBACK;
END Execute_log;

END Pkg_sys_log;

SQL Pkg Example