로깅 프로시저 (트랜잭션과 무관하게 기록)

CREATE TABLE TSQL.LOG_T
(
  LOG_ID   NUMBER,
  LOG_SYS  VARCHAR2(32 BYTE),
  LOG_TYP  VARCHAR2(32 BYTE),
  LOG_MSG  VARCHAR2(4000 BYTE),
  LOG_DTM  DATE
);

CREATE UNIQUE INDEX TSQL.LOG_T_PK ON TSQL.LOG_T(LOG_ID);

CREATE OR REPLACE PUBLIC SYNONYM LOG_T FOR TSQL.LOG_T;

ALTER TABLE TSQL.LOG_T ADD (
  CONSTRAINT LOG_T_PK
  PRIMARY KEY
  (LOG_ID)
  USING INDEX TSQL.LOG_T_PK
  ENABLE VALIDATE);

GRANT SELECT ON TSQL.LOG_T TO PUBLIC;

CREATE SEQUENCE TSQL.LOG_S
  START WITH 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  ORDER;


CREATE OR REPLACE PROCEDURE TSQL.log_p(p_log_sys varchar2, p_log_typ varchar2, p_log_msg varchar2)
as
   pragma autonomous_transaction;
begin
   insert into log_t (log_id, log_sys, log_typ, log_msg, log_dtm)
   values (log_s.nextval, p_log_sys, p_log_typ, p_log_msg, sysdate);
   commit;
end;
/

GRANT EXECUTE ON TSQL.LOG_P TO PUBLIC;