Re: Logger in Tabelle und/oder auf cli

@Andreas und Hans

Ich möchte NICHT die Änderungen von Daten in Tabellen protokollieren
sondern aus einer der PL-Sprachen Informationen auf die Kommandozeile
und/oder in eine Logging-Tabelle schreiben. Folgend ein Ausschnitt aus
einem Oracle-Package. Die Procedure-Aufrufe TRACE, DEBUG, INFO, WARN,
ERROR, FATAL ist das worum es mir geht.

function GET_CHAR_DIFF_CLASSES(
P_STRING_A varchar2,
P_STRING_B varchar2,
P_LOG_LEVEL PKG_PLNSQL_LOGGER.GT_LOG_LEVEL
:= 'INFO',
P_DO_LOG_2_DBMS_OUTPUT boolean := false,
P_DO_CACHE_OVERRIDE boolean := false,
P_DO_TRUNCATE_LOG_TABLE boolean := false
) return GT_CHARACTER_POSITIONS is
pragma autonomous_transaction;

-- constants
LC_THIS_SUBROUTINE constant
PKG_PLNSQL_LOGGER.GT_COMPONENT_NAME
:= 'GET_CHAR_DIFF_CLASSES';
LC_SQL_INSERT constant
PKG_PLNSQL_LOGGER.GT_MESSAGE :=
'insert into DIFF_CHAR_DIFF_CLASSES(' ||
'CHARACTER_DIFFERENCE_CLASS) values (' || C_LF ||
' :LV_CHARACTER_DIFFERENCE)' || C_LF ||
' returning DIFF_CHAR_DIFF_CLASSES_ID into ' ||
':LV_DIFF_CHAR_DIFF_CLASSES_ID';
LC_SQL_SELECT constant
PKG_PLNSQL_LOGGER.GT_MESSAGE :=
'select DIFF_CHAR_DIFF_CLASSES_ID' || C_LF ||
' from DIFF_CHAR_DIFF_CLASSES' || C_LF ||
' where CHARACTER_DIFFERENCE_CLASS = ' ||
':CHARACTER_DIFFERENCE_CLASS';
LC_LENGTH constant integer :=
length(P_STRING_A);

-- variables
LV_CHAR_A char(1);
LV_CHAR_B char(1);
LV_CHARACTER_DIFFERENCE varchar2(32767);
LV_CHARACTER_POSITIONS GT_CHARACTER_POSITIONS;
LV_DIFFERENCE_FOUND boolean := false;
LV_SQL
PKG_PLNSQL_LOGGER.GT_MESSAGE;
LV_DIFF_CHAR_DIFF_CLASSES_ID
DIFF_CHAR_DIFF_CLASSES.DIFF_CHAR_DIFF_CLASSES_ID%type;
LV_TMP varchar2(32767);
begin
SETUP_LOGGING(
P_LOG_LEVEL => P_LOG_LEVEL,
P_DO_LOG_2_DBMS_OUTPUT => P_DO_LOG_2_DBMS_OUTPUT,
P_DO_CACHE_OVERRIDE => P_DO_CACHE_OVERRIDE,
P_DO_TRUNCATE_LOG_TABLE => P_DO_TRUNCATE_LOG_TABLE);

TRACE(
P_TEXT => 'started',
P_SUBROUTINE => LC_THIS_SUBROUTINE);

DEBUG(
P_TEXT => 'LC_LENGTH: ' || LC_LENGTH,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
for I in 1..LC_LENGTH loop
DEBUG(
P_TEXT => 'Index: ' || I,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
LV_CHAR_A := substr(P_STRING_A, I, 1);
LV_CHAR_B := substr(P_STRING_B, I, 1);
DEBUG(
P_TEXT => 'LV_CHAR_A: ' || LV_CHAR_A,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
DEBUG(
P_TEXT => 'LV_CHAR_B: ' || LV_CHAR_B,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
if LV_CHAR_A != LV_CHAR_B then
LV_DIFFERENCE_FOUND := true;
LV_CHARACTER_DIFFERENCE :=
LV_CHAR_A || ' (' || rawtohex(
utl_raw.cast_to_raw(LV_CHAR_A)) || ')' ||
C_US ||
LV_CHAR_B || ' (' || rawtohex(
utl_raw.cast_to_raw(LV_CHAR_B)) || ')';
DEBUG(
P_TEXT =>
'LV_CHARACTER_DIFFERENCE: ' ||
LV_CHARACTER_DIFFERENCE,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
-- get ID of the character difference class
begin
LV_SQL := LC_SQL_SELECT;
execute immediate LV_SQL
into LV_DIFF_CHAR_DIFF_CLASSES_ID
using LV_CHARACTER_DIFFERENCE;
exception
when NO_DATA_FOUND then
LV_SQL := LC_SQL_INSERT;
execute immediate LV_SQL
using LV_CHARACTER_DIFFERENCE
returning into
LV_DIFF_CHAR_DIFF_CLASSES_ID;
end;
DEBUG(
P_TEXT =>
'LV_DIFF_CHAR_DIFF_CLASSES_ID: ' ||
LV_DIFF_CHAR_DIFF_CLASSES_ID,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
-- fill the associative array for ID and positions
if
LV_CHARACTER_POSITIONS.exists(
LV_DIFF_CHAR_DIFF_CLASSES_ID)
then
LV_CHARACTER_POSITIONS(
LV_DIFF_CHAR_DIFF_CLASSES_ID)
:= LV_CHARACTER_POSITIONS(
LV_DIFF_CHAR_DIFF_CLASSES_ID) ||
', ' || I;
else
LV_CHARACTER_POSITIONS(
LV_DIFF_CHAR_DIFF_CLASSES_ID)
:= I;
end if;
DEBUG(
P_TEXT => 'Positions: ' ||
LV_CHARACTER_POSITIONS(
LV_DIFF_CHAR_DIFF_CLASSES_ID),
P_SUBROUTINE => LC_THIS_SUBROUTINE);
end if;
end loop;
if LV_DIFFERENCE_FOUND then
commit;
dbms_stats.gather_table_stats(
OWNNAME => user,
TABNAME =>
'DIFF_CHAR_DIFF_CLASSES',
ESTIMATE_PERCENT =>
C_ESTIMATE_PERCENT,
METHOD_OPT => C_METHOD_OPT,
DEGREE => C_PARALLEL_DEGREE,
GRANULARITY => C_GRANULARITY,
CASCADE => C_CASCADE);
end if;

TRACE(
P_TEXT => 'ended',
P_SUBROUTINE => LC_THIS_SUBROUTINE);
return LV_CHARACTER_POSITIONS;
exception
when others then
FATAL(
P_TEXT => 'Last statement in "excution ' ||
'buffer" LV_SQL:' || C_LF || LV_SQL,
P_SUBROUTINE => LC_THIS_SUBROUTINE);
V_LOCAL_VALUES := V_LOCAL_VALUES || C_LF ||
' LC_THIS_SUBROUTINE: ' || LC_THIS_SUBROUTINE ||
C_LF ||
' LC_SQL_INSERT: ' || LC_SQL_INSERT || C_LF ||
' LC_SQL_SELECT: ' || LC_SQL_SELECT || C_LF ||
' LC_LENGTH: ' || LC_LENGTH || C_LF ||
' P_STRING_A: ' || P_STRING_A || C_LF ||
' P_STRING_B: ' || P_STRING_B || C_LF ||
' P_LOG_LEVEL: ' || P_LOG_LEVEL || C_LF ||
' P_DO_LOG_2_DBMS_OUTPUT: ';
if P_DO_LOG_2_DBMS_OUTPUT then
V_LOCAL_VALUES := V_LOCAL_VALUES || '{true}';
else
V_LOCAL_VALUES := V_LOCAL_VALUES || '{false}';
end if;
V_LOCAL_VALUES := V_LOCAL_VALUES || C_LF ||
' P_DO_CACHE_OVERRIDE: ';
if P_DO_CACHE_OVERRIDE then
V_LOCAL_VALUES := V_LOCAL_VALUES || '{true}';
else
V_LOCAL_VALUES := V_LOCAL_VALUES || '{false}';
end if;
V_LOCAL_VALUES := V_LOCAL_VALUES || C_LF ||
' P_DO_TRUNCATE_LOG_TABLE: ';
if P_DO_TRUNCATE_LOG_TABLE then
V_LOCAL_VALUES := V_LOCAL_VALUES || '{true}';
else
V_LOCAL_VALUES := V_LOCAL_VALUES || '{false}';
end if;
V_LOCAL_VALUES := V_LOCAL_VALUES || C_LF ||
' LV_CHAR_A: ' || LV_CHAR_A || C_LF ||
' LV_CHAR_B: ' || LV_CHAR_B || C_LF ||
' LV_CHARACTER_DIFFERENCE: ' ||
LV_CHARACTER_DIFFERENCE || C_LF ||
' LV_SQL: ' || LV_SQL || C_LF ||
' LV_DIFF_CHAR_DIFF_CLASSES_ID: ' ||
LV_DIFF_CHAR_DIFF_CLASSES_ID || C_LF ||
' LV_DIFFERENCE_FOUND: ';
if LV_DIFFERENCE_FOUND then
V_LOCAL_VALUES := V_LOCAL_VALUES || '{true}';
else
V_LOCAL_VALUES := V_LOCAL_VALUES || '{false}';
end if;
if LV_CHARACTER_POSITIONS.count > 0 then
LV_TMP := LV_CHARACTER_POSITIONS.first;
while LV_TMP is not null loop
V_LOCAL_VALUES := V_LOCAL_VALUES || C_LF
|| ' LV_CHARACTER_POSITIONS("' || LV_TMP ||
'"): ' || LV_CHARACTER_POSITIONS(LV_TMP);
LV_TMP :=
LV_CHARACTER_POSITIONS.next(LV_TMP);
end loop;
end if;
LOG_VALUES;

TRACE(
P_TEXT => 'ended',
P_SUBROUTINE => LC_THIS_SUBROUTINE);

raise;
end GET_CHAR_DIFF_CLASSES;

Zitat von "Thiemo Kellner, NHC Barhufpflege"
<thiemo(dot)kellner(at)gelassene-pferde(dot)biz>:

> Hallo Charles
>
> Vielen Dank für die Antwort. Ich sehe, ich habe mich nicht klar
> ausgedrückt. Ich möchte Ereignisse in selbst geschriebenem plpgsql
> (und den anderen PLs von PostgreSQL) in einer allgemeinen
> Logging-Tabelle und/oder auf der Kommando-Zeile (wahlweise)
> protokollieren.
>
> DBMS-Probleme möchte ich dabei explizit ausklammern. Ich denke,
> dafür sind die Logs des DBMS-Server selbst zuständig. Auch möchte
> ich nicht diese Logs mit den Applikationseinträgen zumüllen. Das
> eine hat für mich mit dem anderen nichts zu tun.
>
> Liebe Grüße
>
> Thiemo
>
> Zitat von Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>:
>
>> Guten Morgen
>>
>> Wenn es darum geht, Aenderungen in Tabellen zu verfolgen, dann
>> könnte dies helfen:
>>
>> https://wiki.postgresql.org/wiki/Audit_trigger
>>
>> Ansonsten, kann man allgemein mit Trigger Funktionen Logs steuern,
>> d.h. Zeilen in anderen Tabellen schreiben.
>> Nicht zu vergessen ist aber, dass i.d.R. in Files geloggt wird,
>> damit auch Probleme mit dem DBMS selber untersucht werden können,
>> d.h. auch dann wenn der DB Server nicht mehr läuft.
>>
>> Schönen Tag und Gruss
>> Charles
>>
>>> -----Original Message-----
>>> From: pgsql-de-allgemein-owner(at)postgresql(dot)org
>>> [mailto:pgsql-de-allgemein-owner(at)postgresql(dot)org] On Behalf Of Thiemo
>>> Kellner, NHC Barhufpflege
>>> Sent: Dienstag, 8. März 2016 00:49
>>> To: pgsql-de-allgemein <pgsql-de-allgemein(at)postgresql(dot)org>
>>> Subject: [pgsql-de-allgemein] Logger in Tabelle und/oder auf cli
>>>
>>> Hoi zäme
>>>
>>> I möchte von innerhalb pgsql in eine Tabelle und/oder auf die
>>> Kommandozeile loggen, so ähnlich wie
>>> https://sourceforge.net/projects/plnsqllogger/ . Kennt jemand eine
>>> Open-Source-Lösung?
>>>
>>> Liebe Grüße
>>>
>>> Thiemo
>>>
>>> --
>>> Auf Gelassene Pferde kann man bauen!
>>> +49 (0)1578-772 37 37
>>> +41 (0)78 947 36 21
>>> sip: thiemo(dot)kellner(at)iptel(dot)org
>>> http://www.gelassene-pferde.biz
>>> Mitglied bei http://www.keep-it-natural.org
>>> Öffentlicher PGP-Schlüssel:
>>> http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF
>>>
>>> ----------------------------------------------------------------
>>> This message was sent using IMP, the Internet Messaging Program.
>>
>>
>
>
>
> --
> Auf Gelassene Pferde kann man bauen!
> +49 (0)1578-772 37 37 (Mo, Di)
> +41 (0)78 947 36 21 (Mi - Fr)
> Skype: thiemo.kellner
> http://www.gelassene-pferde.biz
> Mitglied bei http://www.keep-it-natural.org
> Öffentlicher PGP-Schlüssel:
> http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>

--
Auf Gelassene Pferde kann man bauen!
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
sip: thiemo(dot)kellner(at)iptel(dot)org
http://www.gelassene-pferde.biz
Mitglied bei http://www.keep-it-natural.org
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.