Re: logging mechanism

Thanks Kay,

This was exactly what I was looking for. To my regret, it seems that the new pragma AUTONOMOUS_TRANSACTION works in 8.1.6 but not in 8.0.5.
The simpilicity and robustness of the first solution is very appealling, so I will wait until we upgrade!

Matthijs Rademakers

Kay Kanekowski <kkanekowski_at_a-m-c.de> schreef in berichtnieuws 9d81en$3sn$1_at_fermi.tro.net...
> Hi,
>
> 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
> 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 - 17:07:56 CEST