Autonomous Logging PL/SQL Code

Autonomous Logging PL/SQL Code

 
DROP TABLE xx_sri_logtab
/

CREATE TABLE xx_sri_logtab (
    code INTEGER,
    text VARCHAR2(4000),
    created_on DATE,
    created_by VARCHAR2(100),
    changed_on DATE,
    changed_by VARCHAR2(100)
    )
/    
CREATE OR REPLACE PACKAGE xx_sri_log_pkg
IS
   PROCEDURE putline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      );
      
   PROCEDURE saveline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      );

END xx_sri_log_pkg;

/

CREATE OR REPLACE PACKAGE BODY xx_sri_log_pkg

IS
   PROCEDURE putline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      )
   IS
   v_code Number;
   v_msg  varchar2(4000);
   BEGIN
      INSERT INTO xx_sri_logtab
           VALUES (
              code_in,
              text_in,
              SYSDATE,
              fnd_profile.value('USER_ID')),
              SYSDATE,
              fnd_profile.value('USER_ID'))
           );
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_msg := SQLERRM;
INSERT INTO xx_sri_logtab
           VALUES (v_code,v_msg,SYSDATE,
              fnd_profile.value('USER_ID')),
              SYSDATE,
              fnd_profile.value('USER_ID'))
   END;
   
   PROCEDURE saveline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      )
   IS
PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      putline (code_in, text_in);
      COMMIT;
   EXCEPTION WHEN OTHERS THEN 
   ROLLBACK;
   END;

END xx_sri_log_pkg;

/