Oracle DML Error Logging

Détails
Affichages : 7259

Vote utilisateur: 3 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles inactivesEtoiles inactives
 

source: Oracle® Database - SQL Language Reference - 11g Release 1 (11.1) - B28286-05
source: Oracle® Database - Administrator's Guide - 11g Release 1 (11.1) - B28310-04

Lorsque que l’on utilise une instruction DML (insert/update/delete/merge) via une sous requête, plutôt que d’avoir une erreur puis un rollback il est possible de gérer certaines erreurs via une table de journalisation d’erreur :

  • En fixant la limite du nombre de lignes en erreur ou en positionnant cette limite à UNLIMITED
  • En utilisant la fonctionnalité de logging des erreurs DML.

 

Voyons comment utiliser cette fonctionnalité :

Les erreurs gérées :

  • Valeurs trop large pour la colonne
  • Violations de contrainte (NOT NULL, unique, referential, et check constraints)
  • Erreurs levées pendant l’exécution d’un trigger
  • Erreurs résultant d’une conversion de type entre la colonne cible et la sous-requête
  • Erreur de mapping de Partition
  • Certaines erreurs sur des opérations de MERGE (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

 

Les erreurs non gérées :

Certaines erreurs ne sont pas enregistrées par la gestion intégrée des erreurs et entrainent l’arrêt de l’opération de DML et son rollback

  • Violation de contraintes différées.
  • Une opération d’INSERT, d’UPDATE ou de MERGE en direct-path qui lève une violation de contrainte unique ou d’index unique.
  • Il n’est pas possible de tracer les erreurs dans un table de trace pour les colonnes de type LONG, LOB, ou object . Toutefois, la table en cible de l’opération DML peut contenir ce type de colonne :

     

    • Si vous créez ou modifiez la table de journalisation d'erreur correspondant afin qu'elle contienne une colonne d'un type non pris en charge, et si le nom de la colonne correspond à une colonne non pris en charge dans la table cible de l'opération DML, l'instruction DML échoue au moment du parse.
    • Si la table de journalisation d'erreur ne contient pas tous les types de colonnes non pris en charge, alors toutes les erreurs DML sont enregistrés jusqu'à ce que la limite d'erreurs soit atteinte.
    • Pour les lignes sur lesquelles des erreurs se produisent, les valeurs correspondantes au colonnes dans la table de journalisation des erreurs sont enregistrées avec les informations de commande.

 

Exemple :

  

CREATE
  TABLE "D01_STG"."STIF_D01_GROUP2_INSU"
  (
    "INSURANCE_CODE"          VARCHAR2(2000 CHAR),
    "INSURANCE_NAME"          VARCHAR2(2000 CHAR),
    "INSURANCE_TYPE"          VARCHAR2(2000 CHAR),
    "INSURANCE_CREATION_DATE" VARCHAR2(2000 CHAR),
    "INSURANCE_UPDATE_DATE"   VARCHAR2(2000 CHAR),
    "THE_FILE_CREATION_TIME"  VARCHAR2(2000 CHAR),
    "MT_QUALITY_AUDIT_FK" RAW(16),
    "MT_QUALITY_ISSUE_CODE" VARCHAR2(2000 CHAR),
    "MT_QUALITY_ISSUE_FLAG" NUMBER,
    "MT_REPORTING_DT" DATE,
    "MT_LOAD_DT" DATE,
    "MT_BATCH_ID"     NUMBER,
    "MT_SOURCEFILE"   VARCHAR2(2000 CHAR),
    "MT_SOURCESYSTEM" VARCHAR2(2000 CHAR),
    "MT_FILE"         VARCHAR2(2000 CHAR),
    "LINENUMBER"      NUMBER
  )
  TABLESPACE "D01_STG_DTA" 
;

  

CREATE
  TABLE "STCL_D01_GROUP2_INSU"
  (
    "INSURANCE_CODE" VARCHAR2(10 CHAR),
    "INSURANCE_NAME" VARCHAR2(50 CHAR),
    "INSURANCE_TYPE" VARCHAR2(50 CHAR),
    "INSURANCE_CREATION_DATE" DATE,
    "INSURANCE_UPDATE_DATE" DATE,
    "THE_FILE_CREATION_TIME" DATE,
    "MT_QUALITY_AUDIT_FK" RAW(16),
    "MT_QUALITY_ISSUE_CODE" VARCHAR2(2000 CHAR),
    "MT_QUALITY_ISSUE_FLAG" NUMBER(18,0),
    "MT_REPORTING_DT" DATE,
    "MT_LOAD_DT" DATE,
    "MT_BATCH_ID"     NUMBER(18,0),
    "MT_SOURCEFILE"   VARCHAR2(2000 CHAR),
    "MT_SOURCESYSTEM" VARCHAR2(20 CHAR),
    "MT_FILE"         VARCHAR2(2000 CHAR),
    "LINENUMBER"      NUMBER
  )
  TABLESPACE "D01_STG_DTA" 
;

 

La table d’erreur

 

Descriptions des colonnes de gestion d’erreurs (obligatoire dans la table de gestion des erreurs DML)

ORA_ERR_NUMBER$ NUMBER Code erreur Oracle
ORA_ERR_MESG$ VARCHAR2(2000) Message d’erreur Oracle
ORA_ERR_ROWID$ ROWID Rowid  de la ligne en erreur (pour les opérations d’update ou de delete uniquement biensur)
ORA_ERR_OPTYP$ VARCHAR2(2) Type de l’opération DML : insert (I), update (U), delete (D) Note: Les erreurs issues d’une clause update ou insert d’une opération de MERGE sont distinguées par les valeurs U et I.
ORA_ERR_TAG$ VARCHAR2(2000) Valeur du tag fourni par l’utilisateur dans la clause d’error logging

Colonne de gestion des erreurs  : Data Types

NUMBER VARCHAR2(4000) Capable de tracer les erreurs de conversion
CHAR/VARCHAR2(n) VARCHAR2(4000) Trace n’importe qu'elle valeur dans le charset par défaut sans perte d’information
NCHAR/NVARCHAR2(n) NVARCHAR2(4000) Trace n’importe qu'elle valeur dans le charset national sans perte d’information
DATE/TIMESTAMP VARCHAR2(4000) Trace n’importe quelle valeur sans perte. La conversion en caractères alphanumérique est faite suivant le masque date/time par défaut
RAW RAW(2000) Trace n’importe qu'elle valeur hexa sans perte
ROWID UROWID Trace quelque soit le type de ROWID
LONG/LOB Non supporté
User-defined types Non supporté

Il est possible de créer cette table avec des packages PL/SQL intégrés : DBMS_ERRLOG.CREATE_ERROR_LOG 

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(
DML_TABLE_NAME => 'STCL_D01_GROUP2_INSU',
ERR_LOG_TABLE_OWNER => 'D01_STG',
ERR_LOG_TABLE_NAME => 'STER_D01_GROUP2_INSU',
ERR_LOG_TABLE_SPACE => 'D01_STG_DTA' );

 

La table ainsi générée.

on note l’ajout des colonnes ORA_ERR_*** ainsi que le changement de type des colonnes de la table initiale vers du varchar2(4000) pour les problématique de conversion. 

CREATE TABLE STER_D01_GROUP2_INSU ( 
ORA_ERR_NUMBER$ NUMBER, 
ORA_ERR_MESG$ VARCHAR2(2000), 
ORA_ERR_ROWID$ ROWID, 
ORA_ERR_OPTYP$ VARCHAR2(2), 
ORA_ERR_TAG$ VARCHAR2(2000), 
INSURANCE_CODE VARCHAR2(4000), 
INSURANCE_CREATION_DATE VARCHAR2(4000), 
INSURANCE_NAME VARCHAR2(4000), 
INSURANCE_TYPE VARCHAR2(4000), 
INSURANCE_UPDATE_DATE VARCHAR2(4000), 
LINENUMBER VARCHAR2(4000), 
MT_BATCH_ID VARCHAR2(4000), 
MT_FILE VARCHAR2(4000), 
MT_LOAD_DT VARCHAR2(4000), 
MT_QUALITY_AUDIT_FK VARCHAR2(4000), 
MT_QUALITY_ISSUE_CODE VARCHAR2(4000), 
MT_QUALITY_ISSUE_FLAG VARCHAR2(4000), 
MT_REPORTING_DT VARCHAR2(4000), 
MT_SOURCEFILE VARCHAR2(4000), 
MT_SOURCESYSTEM VARCHAR2(4000), 
THE_FILE_CREATION_TIME VARCHAR2(4000)) 
TABLESPACE D01_STG_DTA ;

 

 

Insertion (ici avec des valeurs trop longues en dur dans les données sources)

NOTEZ le LOG ERROR INTO  qui suit le SELECT 

INSERT INTO "STCL_D01_GROUP2_INSU" "STCL_D01_GROUP2_INSU" 
(
"INSURANCE_CODE", "INSURANCE_NAME", "INSURANCE_TYPE", 
"INSURANCE_CREATION_DATE", "INSURANCE_UPDATE_DATE", 
"THE_FILE_CREATION_TIME", "MT_QUALITY_AUDIT_FK", "MT_QUALITY_ISSUE_CODE", 
"MT_QUALITY_ISSUE_FLAG", "MT_REPORTING_DT", "MT_LOAD_DT", "MT_BATCH_ID", 
"MT_SOURCEFILE", "MT_SOURCESYSTEM", "MT_FILE", "LINENUMBER") 
(
SELECT 
'12345678901' /*SUBSTR(NVL("STIF_D01_GROUP2_INSU"."INSURANCE_CODE", 
'Unknown'), 1, 10) */ /* EXP_TRANSFORM.OUTGRP1.INSURANCE_CODE */ "INSURANCE_CODE", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."INSURANCE_NAME", 'Unknown'), 1, 50)/* EXP_TRANSFORM.OUTGRP1.INSURANCE_NAME */ "INSURANCE_NAME", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."INSURANCE_TYPE", 'Unknown'), 1, 50)/* EXP_TRANSFORM.OUTGRP1.INSURANCE_TYPE */ "INSURANCE_TYPE", 
NVL( TO_DATE("STIF_D01_GROUP2_INSU"."INSURANCE_CREATION_DATE",'DD.MM.YYYY'), 
TO_DATE( '01.01.0001', 'DD.MM.YYYY'))/* EXP_TRANSFORM.OUTGRP1.INSURANCE_CREATION_DATE */ "INSURANCE_CREATION_DATE", 
NVL( TO_DATE("STIF_D01_GROUP2_INSU"."INSURANCE_UPDATE_DATE",'DD.MM.YYYY'), 
TO_DATE( '01.01.0001', 'DD.MM.YYYY'))/* EXP_TRANSFORM.OUTGRP1.INSURANCE_UPDATE_DATE */ "INSURANCE_UPDATE_DATE", 
NVL( TO_DATE("STIF_D01_GROUP2_INSU"."THE_FILE_CREATION_TIME",'DD.MM.YYYY HH24:MI:SS'), 
TO_DATE( '01.01.0001', 'DD.MM.YYYY'))/* EXP_TRANSFORM.OUTGRP1.THE_FILE_CREATION_TIME */ "THE_FILE_CREATION_TIME", 
"STIF_D01_GROUP2_INSU"."MT_QUALITY_AUDIT_FK"/* EXP_TRANSFORM.OUTGRP1.MT_QUALITY_AUDIT_FK */ "MT_QUALITY_AUDIT_FK", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."MT_QUALITY_ISSUE_CODE", 'Unknown'), 1, 2000)/* EXP_TRANSFORM.OUTGRP1.MT_QUALITY_ISSUE_CODE */ "MT_QUALITY_ISSUE_CODE", 
TO_NUMBER("STIF_D01_GROUP2_INSU"."MT_QUALITY_ISSUE_FLAG",
TRANSLATE("STIF_D01_GROUP2_INSU"."MT_QUALITY_ISSUE_FLAG",'-+012345678','SS999999999'))/* EXP_TRANSFORM.OUTGRP1.MT_QUALITY_ISSUE_FLAG */ "MT_QUALITY_ISSUE_FLAG", 
NVL("STIF_D01_GROUP2_INSU"."MT_REPORTING_DT", 
TO_DATE( '01.01.0001', 'DD.MM.YYYY'))/* EXP_TRANSFORM.OUTGRP1.MT_REPORTING_DT */ "MT_REPORTING_DT", 
NVL("STIF_D01_GROUP2_INSU"."MT_LOAD_DT", 
TO_DATE( '01.01.0001', 'DD.MM.YYYY'))/* EXP_TRANSFORM.OUTGRP1.MT_LOAD_DT */ "MT_LOAD_DT", 
TO_NUMBER("STIF_D01_GROUP2_INSU"."MT_BATCH_ID",
TRANSLATE("STIF_D01_GROUP2_INSU"."MT_BATCH_ID",'-+012345678','SS999999999'))/* EXP_TRANSFORM.OUTGRP1.MT_BATCH_ID */ "MT_BATCH_ID", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."MT_SOURCEFILE", 'Unknown'), 1, 2000)/* EXP_TRANSFORM.OUTGRP1.MT_SOURCEFILE */ "MT_SOURCEFILE", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."MT_SOURCESYSTEM", 'Unknown'), 1, 20)/* EXP_TRANSFORM.OUTGRP1.MT_SOURCESYSTEM */ "MT_SOURCESYSTEM", 
SUBSTR(NVL("STIF_D01_GROUP2_INSU"."MT_FILE", 'Unknown'), 1, 2000)/* EXP_TRANSFORM.OUTGRP1.MT_FILE */ "MT_FILE", 
TO_NUMBER("STIF_D01_GROUP2_INSU"."LINENUMBER",
TRANSLATE("STIF_D01_GROUP2_INSU"."LINENUMBER",'-+012345678','SS999999999'))/* EXP_TRANSFORM.OUTGRP1.LINENUMBER */ "LINENUMBER" 
FROM "STIF_D01_GROUP2_INSU" "STIF_D01_GROUP2_INSU" 
WHERE ( "STIF_D01_GROUP2_INSU"."MT_BATCH_ID" = 2052 ) AND 
( "STIF_D01_GROUP2_INSU"."MT_SOURCESYSTEM" = 'NURV' /* FILTER_RUN */ ) AND 
( "STIF_D01_GROUP2_INSU"."MT_QUALITY_ISSUE_FLAG" IS NULL OR "STIF_D01_GROUP2_INSU"."MT_QUALITY_ISSUE_FLAG" < 2 /* FILTER_NON_REJECT */ ) ) 
LOG ERRORS INTO D01_STG.STER_D01_GROUP2_INSU reject limit unlimited ;

  

select * from STER_D01_GROUP2_INSU;

Contenu de la table de trace 

12899    "ORA-12899: value too large for column "D01_STG"."STCL_D01_GROUP2_INSU"."INSURANCE_CODE" (actual: 11, maximum: 10)"        I        12345678901    11-FEB-1994 00.00.00    ASSURANCE ANNULATION        1    11-FEB-1994 00.00.00    1    2052    NURVIS_INSURANCE_20110527120000.CSV    27-SEP-2011 15.01.07        Unknown        27-MAY-2011 00.00.00    INSURANCE    NURV    28-AUG-2011 06.00.09
12899    "ORA-12899: value too large for column "D01_STG"."STCL_D01_GROUP2_INSU"."INSURANCE_CODE" (actual: 11, maximum: 10)"        I        12345678901    11-FEB-1994 00.00.00    ASSURANCE ANNULATION SUPPLEMENTAIRE    2    11-FEB-1994 00.00.00    2    2052    NURVIS_INSURANCE_20110527120000.CSV    27-SEP-2011 15.01.07        Unknown        27-MAY-2011 00.00.00    INSURANCE    NURV    28-AUG-2011 06.00.09
12899    "ORA-12899: value too large for column "D01_STG"."STCL_D01_GROUP2_INSU"."INSURANCE_CODE" (actual: 11, maximum: 10)"        I        12345678901    11-FEB-1994 00.00.00    ASSURANCE ANNULATION SUPPLEMENTAIRE    3    11-FEB-1994 00.00.00    3    2052    NURVIS_INSURANCE_20110527120000.CSV    27-SEP-2011 15.01.07        Unknown        27-MAY-2011 00.00.00    INSURANCE    NURV    28-AUG-2011 06.00.09
12899    "ORA-12899: value too large for column "D01_STG"."STCL_D01_GROUP2_INSU"."INSURANCE_CODE" (actual: 11, maximum: 10)"        I        12345678901    11-FEB-1994 00.00.00    ASSURANCE ANNULATION SUPPLEMENTAIRE    4    11-FEB-1994 00.00.00    4    2052    NURVIS_INSURANCE_20110527120000.CSV    27-SEP-2011 15.01.07        Unknown        27-MAY-2011 00.00.00    INSURANCE    NURV    28-AUG-2011 06.00.09

 

Ajouter vos commentaires

Poster un commentaire en tant qu'invité

0 / 30000 Restriction des caractères
Votre texte doit contenir entre 10 et 30000 caractères
Vos commentaires sont soumis à la modération de l'administrateur.
conditions d'utilisation.

Commentaires

  • Aucun commentaire trouvé