Les erreurs sous Oracle 1 : erreurs syntaxiques/sémantiques, utilitaire OERR, table SPERRORLOG, table ERRLOG
Introduction
Cet article est le premier d'une série consacrée à la gestion des erreurs sous Oracle. Ce sujet est vaste, ces erreurs pouvant être gérées sous SQL*Plus, en PL/SQL, dans des fichiers, via des triggers, j'en passe et des meilleurs :-) Attention, le but ici est de présenter cette gestion des erreurs, pas d'en faire une analyse poussée et exhaustive, ça, c'est à vous de le faire selon vos besoins...
Les autres articles sont ici :
- Les erreurs sous oracle 2 : contraintes d'intégrité, whenever oserror/sqlerror, trigger servererror, fichiers alert.log/trace
- Les erreurs sous Oracle 3 : PL/SQL
Aujourd'hui nous allons regarder quatre choses :
- les erreurs syntaxiques et sémantiques
- l'utilitaire Linux OERR
- tracer les erreurs SQL : la table SPERRORLOG
- la tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT
Points d'attention
Aucun.
Base de tests
Une base Oracle 12.
Exemples
============================================================================================
Les erreurs syntaxiques et sémantiques
============================================================================================
Il existe deux grand types d'erreur sous Oracle, les erreurs syntaxiques et les erreurs sémantiques. Malheureusement il n'est pas possible de les distinguer via un code! Si le besoin existe, alors il faut lister les erreurs les plus fréquentes des deux types et faire ensuite une gestion numéro d'erreur par numéro d'erreur; dans un programme PL/SQL par exemple.
Erreur syntaxique : c'est quand un ordre SQL est mal écrit. Ici il n'y a aucun nom de colonne ni le caractère *.
SQL> SELECT FROM test444;
SELECT FROM test444
*
ERROR at line 1:
ORA-00936: missing expression
Exemple d'erreur sémantique : la syntaxe est bonne mais il y a un pb avec les objets de la requête ou du user qui la lance; ici la table ou vue n'existe pas.
SQL> select * from test444;
select * from test444
*
ERROR at line 1:
ORA-00942: table or view does not exist
============================================================================================
L'utilitaire Linux OERR
============================================================================================
Quand une erreur survient, un message est affiché par SQL*Plus.
SQL> select * frommm;
select * frommm
*
ERREUR a la ligne 1 :
ORA-00923: mot-cle FROM absent a l'emplacement prevu
On peut parfois avoir plus d'information sur celle-ci en utilisant l'utilitaire Linux OERR; il a en effet deux champs appelés "Cause" et "Action" en plus. Cet outil extrait les messages d'erreur depuis un fichier Oracle; ce fichier peut-être mis à jour manuellement pour avoir des messages plus précis mais c'est hors scope de cet article.
SQL> ! oerr
Usage: oerr facility error
Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error. So you should type "oerr ora 7300".
If you get LCD-111, type "oerr lcd 111", and so on.
Parfois on n'a aucune info en plus de celle affichée par SQL*Plus; ah si, dans mon cas le texte est en anglais, ce qui en général est bien plus utile (avis personnel) :-)
SQL> ! oerr ora 923
00923, 00000, "FROM keyword not found where expected"
// *Cause:
// *Action:
Mais, parfois, selon le code erreur, les champs Cause et Action peuvent être renseignés et on a alors plus d'info que le message par défaut de SQL*Plus.
SQL> ! oerr ora 42
00042, 0000, "Unknown Service name %s"
// *Cause: An attempt was made to use an invalid application service.
// *Action: Use a valid service name from SERVICE$ or add a new
// service using the DBMS_SERVICE package.
============================================================================================
Tracer les erreurs SQL : la table SPERRORLOG
============================================================================================
Il est possible de tracer les erreurs survenues sous SQL*Plus dans une table dédiée, appelée SPERRORLOG. Les erreurs tracées sont les erreurs SQL mais aussi SQL*Plus. Au préalable, il faut activer cette fonctionnalité avec le paramètre errorlogging ("enregistrements des erreurs" en français). Cela a pour effet de créer une table SPERRORLOG dans le schéma courant.
Voici les options de la commande sous SQL*Plus.
SQL> help set
...
ERRORL[OGGING] {ON|OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
...
SQL> set errorlogging on
SQL> show user
USER is "HR"
SQL> select owner, object_type from dba_objects where object_name= 'SPERRORLOG';
OWNER OBJECT_TYPE
-----------------------
HR TABLE
SQL> desc SPERRORLOG
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT CLOB
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
On va tester avec deux types d'erreur.
Erreur SQL : SELECT qui provoque une erreur.
SQL> select * from test99;
select * from test99
*
ERROR at line 1:
ORA-00942: table or view does not exist
Erreur SQL*Plus.
SQL> col STATEMENT col A30
SP2-0158: unknown COLUMN option "col"
OK, les messages d'erreurs sont bien sauvegardés dans la nouvelle table.
SQL> SELECT * from SPERRORLOG order by TIMESTAMP;
USERNAME TIMESTAMP SCRIPT IDENTIFIER MESSAGE STATEMENT
---------- ------------------------------ ------------------- ---------- ----------------------
HR 08-AUG-19 01.26.14.000000 PM ORA-00942: table or view does not exist select * from test99
HR 08-AUG-19 01.29.29.000000 PM SP2-0158: unknown COLUMN option "col" col STATEMENT col A30
A noter que même si je ferme ma session, le contenu de la table SPERRORLOG n'est pas supprimé; ouf, cela permet d'avoir un historique des erreurs :-)
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@vbgeneric ~]$ sqlplus HR
Enter password:
SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
TIMESTAMP MESSAGE STATEMENT
------------------------------ ---------------------------------------- ----------------------------------------
08-AUG-19 01.26.14.000000 PM ORA-00942: table or view does not exist select * from test99
08-AUG-19 01.29.29.000000 PM SP2-0158: unknown COLUMN option "col" col STATEMENT col A30
On peut aussi créer sa propre table d'erreurs, pour éviter que la table SPERRORLOG ne soit purgée, par un batch de DBA par exemple.
SQL> create table my_sperror_log
(
username varchar2(256),
timestamp timestamp,
script clob,
identifier varchar(256),
message clob,
statement clob
);
Table created.
Avec l'option "errorlogging on table", je donne le nom d'une table pour tracer les erreurs et on voit bien que SPERRORLOG n'est plus mise à jour.
SQL> set errorlogging on table my_sperror_log;
SQL> select * from test 55555;
select * from test 55555
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
TIMESTAMP MESSAGE STATEMENT
------------------------------ ---------------------------------------- ----------------------------------------
08-AUG-19 01.26.14.000000 PM ORA-00942: table or view does not exist select * from test99
08-AUG-19 01.29.29.000000 PM SP2-0158: unknown COLUMN option "col" col STATEMENT col A30
SQL> select TIMESTAMP, MESSAGE, STATEMENT from my_sperror_log order by TIMESTAMP;
TIMESTAMP MESSAGE STATEMENT
------------------------------ ---------------------------------------- -------------------------
08-AUG-19 01.40.06.000000 PM ORA-00933: SQL command not properly ended select * from test 55555
Si on vide la table des erreurs sans préciser un nom, c'est la tablecourante qui est vidée et pas forcément SPERRORLOG.
SQL> set errorlogging on truncate
SQL> select count(*) from SPERRORLOG;
COUNT(*)
----------
2
SQL> select count(*) from my_sperror_log;
COUNT(*)
----------
0
L'option IDENTIFIER quant à elle permet d'attribuer un id à chaque session dans la table des erreurs.Ainsi on pourra filtrer les erreurs sur un user. Attention, pas de quotes ou guillemets pour le libellé de l'identifiant.
SQL> set errorlogging on identifier DAVID;
SQL>
SQL> SELECT * FROM cvfg;
SELECT * FROM cvfg
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from my_sperror_log where identifier = 'DAVID' order by TIMESTAMP;
TIMESTAMP IDENTIFIER MESSAGE STATEMENT
------------------------------ -------------------- ------------------------------------------------------
08-AUG-19 01.50.03.000000 PM DAVID ORA-00942: table or view does not exist SELECT * FROM cvfg
J'ouvre maintenant une session avec le user SYS et je renouvelle l'expérience, en utilisant bien comme table des erreurs celle du user HR sinon Oracle va me créer dans le schéma SYS une table SPERRORLOG.
SQL> connect SYS as sysdba
SQL> set errorlogging on table HR.my_sperror_log;
SQL> set errorlogging on identifier SYS;
Je provoque une erreur comme SYS. On voit bien ensuite qu'on peut filtrer les erreurs selon l'utilisateur qui a provoqué les erreurs. Il est alors facile de créer un "TRIGGER AFTER LOGON" et de renseigner pour chaque user connecté le paramètre IDENTIFIER avec le USERNAME de connexion.
SQL> set columnzzzz NAME for A30
SP2-0158: unknown SET option "columnzzzz"
SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from HR.my_sperror_log order by TIMESTAMP;
TIMESTAMP IDENTIFIER MESSAGE STATEMENT
------------------------------ -------------------- ---------------------------------------- -------------------
08-AUG-19 01.50.03.000000 PM DAVID ORA-00942: table or view does not exist SELECT * FROM cvfg
08-AUG-19 01.55.54.000000 PM SYS SP2-0158: unknown SET option "columnzzzz" set columnzzzz NAME for A30
============================================================================================
La tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT
============================================================================================
Dans la syntaxe du INSERT, UPDATE, DELETE, MERGE depuis Oracle 10g, il est possible de logger les erreurs des opérations DML de façon détaillées (avec le code et le message Oracle) et même de bypasser les erreurs pour éviter qu'un programme ne s'arrête et n'annule tout ce qui s'est bien déroulé avant.
Création de la table de tests avec 100 enregistrements.
SQL> CREATE TABLE ZZZ_DDU(ID NUMBER(7) NOT NULL, LIB VARCHAR2(20 CHAR));
SQL> BEGIN
FOR i in 1..100
LOOP
INSERT INTO ZZZ_DDU VALUES(i, TO_CHAR(i));
END LOOP;
COMMIT;
END;
/
Création de la table stockant les erreurs avec le package DBMS_ERRLOG pour la table ZZZ_DDU.
Voici la définition de ce package dans la doc Oracle; à noter qu'il ne contient qu'une seule procédure.
"The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources."
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ZZZ_DDU', 'errlog');
Test avec N Insert qui plantent mais sans la clause REJECT LIMIT : seule la première erreur est tracée car le programme s'arrête de suite.
SQL> BEGIN
FOR i in 1000..1010
LOOP
INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU');
END LOOP;
COMMIT;
END;
/
BEGIN
*
ERREUR a la ligne 1 :
ORA-12899: valeur trop grande pour la colonne "ZZTEST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20)
ORA-06512: a ligne 4
SQL> SELECT * FROM ERRLOG;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_RO OR ORA_ERR_TAG$ ID LIB
--------------- -------------------------------------------------- ---------- -- --------------- -----
12899 ORA-12899: valeur trop grande pour la colonne "ZZT I TEST ZZZ_DDU 1001 AXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
EST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20) XXXXXXXXXXXXXXXXXXXX
Test avec 11 INSERTs qui plantent avec la clause REJECT LIMIT à 50 : toutes les erreurs sont bien logguées et Oracle continue à traiter les INSERTs tant qu'il n'arrive pas à 50 erreurs. On ne constate d'ailleurs aucun message d’erreur dans SQL*Plus sur cette opération.
SQL> truncate table errlog;
SQL> truncate table ZZZ_DDU;
SQL> BEGIN
FOR i in 1000..1010
LOOP
INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU') REJECT LIMIT 50;
END LOOP;
COMMIT;
END;
/
Toutes les erreurs sont logguées dans la table des erreurs dans la limite du REJECT LIMIT.
SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ID FROM ERRLOG order by ID;
ORA_ERR_NUMBER$ ORA_ERR_TAG$ ID
--------------- --------------- -----
12899 TEST ZZZ_DDU 1001
12899 TEST ZZZ_DDU 1003
12899 TEST ZZZ_DDU 1005
12899 TEST ZZZ_DDU 1007
12899 TEST ZZZ_DDU 1009
Tous les INSERTsOK sont bien dans la table ZZZ_DDU, ils n’ont pas été annulés par le premier INSERT en erreur puisqu'on tolère 50 erreurs avant qu'un ROLLBACK ne soit déclenché.
SQL> SELECT * FROM ZZZ_DDU order by ID;
ID LIB
---------- ------------------------------
########## 1000
########## 1002
########## 1004
########## 1006
########## 1008
########## 1010
6 lignes selectionnees.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)