log.pkg

OReilly Oracle PL SQL Programming第4版源代码

DROP TABLE log81tab;

CREATE TABLE log81tab (
    code INTEGER,
    text VARCHAR2(4000),
    created_on DATE,
    created_by VARCHAR2(100),
    changed_on DATE,
    changed_by VARCHAR2(100),
    machine VARCHAR2(100),
    program VARCHAR2(100)
    );

CREATE OR REPLACE PACKAGE log81
IS
   PROCEDURE putline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      );
      
   PROCEDURE saveline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      );

END;
/

CREATE OR REPLACE PACKAGE BODY log81
IS
   CURSOR sess IS
       SELECT MACHINE, PROGRAM
         FROM V$SESSION
        WHERE AUDSID = USERENV('SESSIONID');
   rec sess%ROWTYPE;

   PROCEDURE putline (
      code_in IN INTEGER,
      text_in IN VARCHAR2
      )
   IS
   BEGIN
      INSERT INTO log81tab
           VALUES (
              code_in,
              text_in,
              SYSDATE,
              USER,
              SYSDATE,
              USER,
              rec.machine,
              rec.program
           );
   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;
BEGIN
   OPEN sess; FETCH sess INTO rec; CLOSE sess;
END;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/