Re: logging mechanism

From: Kay Kanekowski <kkanekowski_at_a-m-c.de>
Date: Tue, 8 May 2001 07:56:53 +0200
Message-ID: <9d81en$3sn$1_at_fermi.tro.net>

Hi,

[Quoted] if you have an Oracele 8.x.x (?) is simple with an procedure like that:

   PROCEDURE log_sql (

      msg_level_in IN msg_level_typ,
      message_in   IN VARCHAR2,
      userid_in    IN VARCHAR2    := USER,
      log_time_in  IN DATE        := SYSDATE,
      job_id_in    IN NUMBER      := NULL,
      job_typ_in   IN job_typ_typ := NULL)
   IS
     PRAGMA AUTONOMOUS_TRANSACTION;

   --
   BEGIN
--

      INSERT INTO logging_table (
         msg_level, message, userid, log_time, job_id, job_typ)
      VALUES (
         msg_level_in, message_in, userid_in, log_time_in, job_id_in,
job_typ_in);


      --

      COMMIT;


      --

   EXCEPTION
      WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR (-20903, 'log_sql: ' || SQLERRM);

   END log_sql;

In Oracle 7.x.x. you need the DBMS_PIPE mechanism. And you have to start a second process, who will
listened to PIPE and then writes to the logging_table (with COMMIT).

   PROCEDURE log_pipe (

      msg_level_in IN msg_level_typ,
      message_in   IN VARCHAR2,
      userid_in    IN VARCHAR2    := USER,
      log_time_in  IN DATE        := SYSDATE,
      job_id_in    IN NUMBER      := NULL,
      job_typ_in   IN job_typ_typ := NULL)
   IS


      --

      status INTEGER;
      antwort VARCHAR2 (100) := '';
   BEGIN
      DBMS_PIPE.PACK_MESSAGE (MESSAGE_TYP_NORMAL);
      DBMS_PIPE.PACK_MESSAGE (msg_level_in);
      DBMS_PIPE.PACK_MESSAGE (message_in);
      DBMS_PIPE.PACK_MESSAGE (userid_in);
      DBMS_PIPE.PACK_MESSAGE (log_time_in);
      DBMS_PIPE.PACK_MESSAGE (job_id_in);
      DBMS_PIPE.PACK_MESSAGE (job_typ_in);


      --

      status := DBMS_PIPE.SEND_MESSAGE (PIPE_SEND);
      status := DBMS_PIPE.RECEIVE_MESSAGE (PIPE_ACKNOWLEDGE,
MAX_ACK_TIME); -- acknowledge-Meldung holen


      --

      IF status = TIMEOUT THEN
         RAISE_APPLICATION_ERROR (-20900, 'Listener-Prozeß nicht aktiv oder
[Quoted] Time-Out');
      ELSE
         DBMS_PIPE.UNPACK_MESSAGE (antwort);
         IF antwort <> MESSAGE_TYP_ACKNOWLEDGE THEN
             RAISE_APPLICATION_ERROR (-20901, 'Falsche Synchronisation mit
Listener');
         END IF;
      END IF;


      --

   END log_pipe;

HTH
Kay
another Oracle Developer Received on Tue May 08 2001 - 07:56:53 CEST