SQL Pkg Example

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.