오라클 procedure 수행 로그 남기기 - 예제 코드

오라클(oracle) 환경에서 프로시저(procedure) 작업 수행 시 로그 남기는 코드이다.
로그를 남기기 위한 procedure와 수행 코드를 넣기 위한 procedure 폼 두 가지이다.

사용 방법은 다음과 같다.

작업 수행 procedure의 — start script 와 — end script 사이에 원하는 수행 스크립트를 작성한다.
작업 수행 procedure를 이름만 바꾸고 복제해서 사용하면 로그에서 자동으로 구분할 수 있도록 했다.

작업 수행 procedure 예제 코드,
CREATE OR REPLACE PROCEDURE job_proc01
IS
   err_code   VARCHAR (1024);
   err_msg    VARCHAR (1024);
   job_id     VARCHAR (1024);
   job_nm     VARCHAR (1024);
BEGIN
   job_id := TO_CHAR (SYSTIMESTAMP, 'YYYYMMDDHH24MISS.FF');
   job_nm := $$PLSQL_UNIT;
   etl_job_hist_logger (job_id,
                        job_nm,
                        'N/A',
                        'Started',
                        'N/A');

   -- START SCRIPT


   -- END SCRIPT
   etl_job_hist_logger (job_id,
                        job_nm,
                        'N/A',
                        'Ended',
                        'N/A');
EXCEPTION
   WHEN OTHERS
   THEN
      err_code := SQLCODE;
      err_msg := SUBSTR (SQLERRM, 1, 200);
      etl_job_hist_logger (job_id,
                           job_nm,
                           'N/A',
                           'Fail',
                           err_code || '::' || err_msg);
END;
/

로그 수행 procedure 예제 코드,
CREATE OR REPLACE PROCEDURE etl_job_hist_logger (xJobId        IN STRING,
                                                 xJobNm        IN STRING,
                                                 xJobDesc      IN STRING,
                                                 xJobStat      IN STRING,
                                                 xJobFailMsg   IN STRING)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE ('--------------------------------------');
   DBMS_OUTPUT.PUT_LINE ('> Job ID: ' || xJobId);
   DBMS_OUTPUT.PUT_LINE ('> Job Name: ' || xJobNm);
   DBMS_OUTPUT.PUT_LINE ('> Job Description: ' || xJobDesc);
   DBMS_OUTPUT.PUT_LINE ('> Job Status: ' || xJobStat);

   IF xJobStat IN ('Fail')
   THEN
      DBMS_OUTPUT.PUT_LINE ('> Job Fail Message: ' || xJobFailMsg);
   END IF;

   DBMS_OUTPUT.PUT_LINE ('--------------------------------------');

   INSERT INTO etl_job_hist (job_id,
                             job_nm,
                             job_desc,
                             job_stat,
                             job_fail_msg)
        VALUES (xJobId,
                xJobNm,
                xJobDesc,
                xJobStat,
                xJobFailMsg);
END;
/


**
기타 참조 사항 정리

토드 SQL 코드 정리 단축 코드 (format)
format -> command + shift + F

토드 문장 수행 단축 코드
run one statement -> command + enter
run all statements -> command + shift + enter

맥북 환경에서는 SQL Developer 보다 Toad가 안정적이고 직관적이었다.
앱스토어에서 받은 토드는 MongoDB, MySQL, PostgreSQL도 기본으로 지원한다.

DBMS_OUTPUT 설정 환경 변수 (for sqlplus)
SET SERVEROUTPUT ON;

토드에서 DBMS_OUTPUT은 AutoCommit OFF에서 확인 할 수 있었다.