• database log table

    Creare una tabella di log in Oracle

    Per tracciare il comportamento di stored procedures, function e package PL/SQL può essere molto utile utilizzare una tabella di log.

    Creazione della tabella di log

    La tabella di log avrà i seguenti campi:

    1. PRG_LOG, un progressivo autoincrementante di tipo NUMBER (generato da una sequence), che sarà anche PRIMARY KEY della tabella;
    2. DATA_EVENTO, campo di tipo DATE con valore di default SYSDATE, che traccia quando si è verificato l’evento;
    3. SEVERITY, campo di tipo VACRHAR2(7) che indica la severity del log; può assumere i valori DEBUG, INFO, WARNING, ERROR.
    4. NOME_OGGETTO, di tipo VARCHAR2(4000), che traccia il sorgente del log, nel formato <SCHEMA>.<PACKAGE>.<PROCEDURA> ad esempio;
    5.  DESCRIZIONE_EVENTO, di tipo VARCHAR2(4000), che descrive l’evento da tracciare;
    6. STATEMENT, di tipo CLOB, che memorizza l’istruzione critica che può innescare eventuali errori e che si desidera monitorare;
    7. COD_UTENTE, di tipo VARCHAR2(50), che memorizza l’utente che ha effettuato l’operazione.

    Di seguito lo script per la creazione della tabella.

    CREATE TABLE HR.HR_LOGS
    (
      PRG_LOG NUMBER,
      DATA_EVENTO DATE DEFAULT sysdate,
      SEVERITY VARCHAR2(7),
      NOME_OGGETTO VARCHAR2(4000 BYTE),
      DESCRIZIONE_EVENTO VARCHAR2(4000 BYTE),
      STATEMENT CLOB,
      COD_UTENTE VARCHAR2(50 BYTE) DEFAULT USER
    );

    Per il campo autoincrementante PRG_LOG verrà usata una sequence così definita:

    CREATE SEQUENCE HR.HR_PRG_LOG_TABLE
      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 1
      NOCYCLE
      NOCACHE
      NOORDER;

    Alla tabella HR_LOGS aggiungiamo un indice UNIQUE sul campo DATA_EVENTO nel modo seguente:

    CREATE INDEX HR.HR_LOGS_IDX01 ON HR.HR_LOGS (DATA_EVENTO);

    In questo modo, se si devono cercare dei record di log che fanno riferimento ad una certa data, l’indicizzazione per DATA_EVENTO consentirà di evitare la scansione dell’intera tabella e renderà il recupero dei record relativi a quella giornata molto più rapido.

    Infine definiamo la PRIMARY KEY della tabella sul campo PRG_LOG nel modo seguente:

    ALTER TABLE HR.HR_LOGS ADD CONSTRAINT HR_LOGS_PK PRIMARY KEY (PRG_LOG);

    Scrittura di record sulla tabella di log

    Per poter popolare la tabella, può essere utile utilizzare una stored procedure che gestisce in modo più strutturato l’inserimento dei log.

    create or replace PROCEDURE HR_PRC_LOG
                                (
                                 p_severity       IN VARCHAR2,
                                 p_nome_oggetto   IN VARCHAR2,
                                 p_descr_oggetto  IN VARCHAR2,
                                 p_statement      IN CLOB,
                                 p_utente_client  IN VARCHAR2 
                                ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    BEGIN
    
       IF INSTR(p_descr_oggetto,'ORA-') > 0 THEN
          INSERT INTO HR_LOGS
                      (
                       PRG_LOG,
                       SEVERITY,
                       NOME_OGGETTO,
                       DESCRIZIONE_EVENTO,
                       DATA_EVENTO,
                       STATEMENT,
                       COD_UTENTE
                      )
               VALUES (
                       HR_PRG_LOG_TABLE.NEXTVAL,
                       p_severity,
                       p_nome_oggetto,
                       DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || 'SQLERRM : ' || p_descr_oggetto,
                       sysdate,
                       p_statement,
                       p_utente_client);
       ELSE
          INSERT INTO HR_LOGS
                      (
                       PRG_LOG,
                       SEVERITY,
                       NOME_OGGETTO,
                       DESCRIZIONE_EVENTO,
                       DATA_EVENTO,
                       STATEMENT,
                       COD_UTENTE
                      )
               VALUES (
                       HR_PRG_LOG_TABLE.NEXTVAL,
                       p_severity,
                       p_nome_oggetto,
                       p_descr_oggetto,
                       sysdate,
                       p_statement,
                       p_utente_client);
       END IF;
    
       COMMIT;
    
    END HR_PRC_LOG;
    /

    La direttiva PRAGMA AUTONOMOUS_TRANSACTION alla riga 9 assicura che la procedura HR_LOGS venga eseguita in una sessione tutta sua, ignorando le modifiche non committate della sessione che la invoca.

    Se il parametro p_descr_oggetto fornito in input contiene la stringa “ORA-” vuol dire che è stato generato un errore. La procedura DBMS_UTILITY.FORMAT_ERROR_BACKTRACE mostra lo stack di esecuzione nel punto in cui l’eccezione è stata generata.

    Utilizzo dei log nelle proprie procedure

    Un esempio di utilizzo della scrittura dei log all’interno delle proprie procedure potrebbe essere il seguente. Si supponga di avere all’interno dello schema HR la tabella employees, che contiene i dati di tutti gli impiegati, e la tabella employees_backup, che ha la stessa struttura della tabella employees e che si vuole riempire con i dati della tabella employees (solo quelli assunti a partire da una certa data) per effettuare un backup. Per effettuare il backup si può creare una stored procedure PL/SQL che fa le seguenti cose:

    • cancella il contenuto della tabella employees_backup;
    • inserisce tutti i record della tabella employees nella tabella employees_backup;
    • esegue un commit;
    • in caso di eccezioni effettua un rollback;

    Di seguito il codice della procedura che esegue il backup:

    CREATE OR REPLACE PROCEDURE hr.prc_backup_employees(
                p_hire_date IN DATE, 
                p_cod_utente IN VARCHAR2
    )
    IS
      c_nome_procedura CONSTANT VARCHAR2(50) := 'prc_backup_employees';
      v_count NUMBER := 0;
      c_debug_severity CONSTANT VARCHAR2(7)  := 'DEBUG';
      c_info_severity CONSTANT VARCHAR2(7)  := 'INFO';
      c_warning_severity CONSTANT VARCHAR2(7)  := 'WARNING';
      c_error_severity CONSTANT VARCHAR2(7)  := 'ERROR';
      
    BEGIN
      HR_PRC_LOG(c_info_severity, c_nome_procedura, 'INIZIO', NULL, p_cod_utente);
      
      -- cancello tutti i record dalla tabella 
      DELETE FROM employees_backup;
      
      -- inserisco i record dalla tabella employees a quella employees_backup
      INSERT INTO employees_backup SELECT * FROM employees WHERE hire_date >= p_hire_date;
      
      v_count := SQL%ROWCOUNT;
      
      COMMIT;
      
      -- inserisco un log con il numero di record inseriti
      IF v_count = 0 THEN
        HR_PRC_LOG(c_warning_severity, c_nome_procedura, 'Record inseriti: ' || TO_CHAR(v_count), NULL, p_cod_utente);
      ELSE
        HR_PRC_LOG(c_info_severity, c_nome_procedura, 'Record inseriti: ' || TO_CHAR(v_count), NULL, p_cod_utente);
      END IF;
      
      HR_PRC_LOG(c_info_severity, c_nome_procedura, 'FINE', NULL, p_cod_utente);
      
    EXCEPTION
      WHEN OTHERS
      THEN
        HR_PRC_LOG(c_error_severity, c_nome_procedura, SQLERRM, NULL, p_cod_utente);
        RAISE;
    END;
    /

    L’invocazione della procedura provoca il caricamento dei dati nella tabella employees_backup (se esiste) e la scrittura delle informazioni nella tabella di log:

    BEGIN
      prc_backup_employees(to_date('01-GEN-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS'), 'HR');
    END;

    Per verificare cosa è successo durante l’esecuzione della procedura, basta eseguire la seguente query:

    SELECT *
      FROM hr_logs
     WHERE data_evento > sysdate-0.1
     ORDER BY data_evento DESC; 

    che restituisce il seguente risultato:

    risultato log oracle

    Se invece la tabella employees_backup fosse stata marcata come READ ONLY, le operazioni di delete e insert sulla tabella non sarebbero permesse. In questo caso la procedura solleva un’eccezione e la tabella di log traccia l’errore nel modo seguente:

    risultato log post errore backup oracle

    La descrizione dell’errore (di severità ERROR) indica cosa è successo:

    ORA-06512: at “HR.PRC_BACKUP_EMPLOYEES”, line 17
    SQLERRM : ORA-12081: update operation not allowed on table “HR”.”EMPLOYEES_BACKUP”

     

    e cioè che alla riga 17 (quella della cancellazione dei dati della tabella) l’operazione ha generato un errore ORA-12081: l’operazione di update (in questo caso la cancellazione) non è consentita sulla tabella employees_backup. Il motivo è ovviamente che la tabella è stata marcata come READ ONLY e quindi non è possibile effettuare operazioni di tipo DML, come INSERT, UPDATE e DELETE.

     

    Giulio Cantali – IT Consultant

    Creatore di Database Master, il primo percorso per diventare esperti di database

Lascia un commento

Se vuoi condividere la tua opinione, lascia un commento

Puoi usare questi tag e attributi: HTML:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">