Premier piège : les SELECT sans FROM en erreur

Vous commencez votre premier jour d’utilisation sur Oracle. vous souhaitez juste lancer un test basique, comme par exemple faire un calcul ou tester une fonction de chaîne.

Vous faites alors :

SELECT 2 + 3;

Ou encore :

SELECT UPPER('coucou');

Et à chaque fois, pour ces requêtes très simples, vous obtenez une erreur :

ORA-00923: mot-clé FROM absent à l'emplacement prévu

Notez bien le début du message d’erreur : ils commencent tous ainsi, avec ORA suivi d’un numéro à 5 chiffres pour le code d’erreur.

Le message nous dit que le mot-clé FROM est manquant, pourtant, les requêtes que vous faites semblent correctes.

Cela va vous surprendre, mais sous Oracle, il faut toujours utiliser le mot-clé SELECT avec le mot-clé FROM, sinon, vous obtenez l’erreur mentionnée ci-dessus.

Mais alors, comment faire pour ce genre de requête ? La réponse est l’utilisation d’une table fictive appelée dual. Dès que vous avez des requêtes ne faisant pas intervenir de table, de vue ou de CTE, alors ajoutez systématiquement FROM dual à votre requête.

Nous pouvons donc réécrire les requêtes précédentes ainsi :

SELECT 2 + 3 FROM dual;

Elle donne bien le bon résultat et sans erreur :

2+3|
---+
  5|

Et maintenant avec :

SELECT UPPER('coucou') FROM dual;

Elle fonctionne également :

UPPER('COUCOU')|
---------------+
COUCOU         |

Voyons maintenant un autre cas : une CTE récursive.

WITH cte_exemple(n) AS (
	SELECT 0
	UNION ALL
	SELECT n + 1 FROM cte_exemple WHERE  n < 9
)
SELECT * FROM cte_exemple;

Vous obtiendrez encore l’erreur du mot-clé FROM manquant.

Donc pour lancer une CTE récursive, il faut penser à spécifier le FROM dual !

En voici la version corrigée :

WITH cte_exemple(n) AS (
	SELECT 0 FROM dual
	UNION ALL
	SELECT n + 1 FROM cte_exemple WHERE  n < 9
)
SELECT * FROM cte_exemple;

Et cela donne le résultat escompté :

N|
-+
0|
1|
2|
3|
4|
5|
6|
7|
8|
9|

Donc retenez bien : pas de SELECT sans FROM !

Deuxième piège : CREATE TABLE DEFAULT en erreur

Vous voulez créer une table assez simple, avec certaines colonnes devant avoir une valeur par défaut, et ne devant pas valoir NULL.

Cela pourrait être l’exemple suivant :

CREATE TABLE THING (
	NAME VARCHAR2 ( 100 ) NOT NULL,
	CREATED_AT DATE NOT NULL DEFAULT SYSDATE  
);

Vous exécutez la requête de création de table, sûr⋅e de vous, et là vous obtenez l’étrange erreur suivante :

ORA-00907: parenthèse de droite absente

Pourtant, il ne manque pas de parenthèse. Alors, pourquoi cette erreur est-elle levée ?

L’explication est qu’Oracle est peu tolérant au changement d’ordre dans les déclarations. Pour une syntaxe qui serait valable avec un autre SGBD, on obtient une erreur avec Oracle, mais une erreur pas claire car comme il s’attend à un ordre différent des éléments, il soulève une erreur par rapport à quelque chose qu’il attend et qui n’a pas lieu d’être ici. Pour faire simple, il soulève une erreur innapropriée.

La solution est de faire attention à l’emplacement de la clause DEFAULT. Il faut ici la placer après le nom de la colonne, comme ceci par exemple :

CREATE TABLE THING (
	NAME VARCHAR2 ( 100 ) NOT NULL,
	CREATED_AT DATE DEFAULT SYSDATE NOT NULL  
);

La requête s’exécute sans erreur, la table est bien créée.

Insérons-y une ligne :

INSERT INTO THING (NAME) VALUES ('Foo');

Voyons si la valeur par défaut a bien été prise :

SELECT * FROM THING;

La sélection donne :

NAME|CREATED_AT             |
----+-----------------------+
Foo |2022-04-23 11:41:19.000|

Nous avons bien la valeur par défaut pour la date de création.

Donc retenez bien : attention à l’ordre de déclaration qui est assez rigide chez Oracle, contrairement à d’autres SGBD.

Troisième piège : les clés primaires auto-incrémentées

Oracle ne propose pas une, pas deux, mais trois façons différentes de déclarer une clé primaire auto-incrémentée. Selon ce que vous aurez choisi, vous obtiendrez ou non des erreurs lors des insertions de données.

Voyons cela avec 3 exemples.

Auto-incrémentation basique

Testons directement avec cette déclaration :

CREATE TABLE BAR (
	ID NUMBER GENERATED AS IDENTITY PRIMARY KEY,
	NAME VARCHAR2 ( 100 ) NOT NULL  
);

La table est bien créée. insérons maintenant des données :

INSERT INTO BAR (NAME) VALUES ('Foo');

Fonctionne et donne ceci :

ID|NAME|
--+----+
 1|Foo |

Insérons en spécifiant une valeur pour la clé primaire :

INSERT INTO BAR (ID, NAME) VALUES (2, 'Thing');

Oracle lève l’erreur suivante :

ORA-32795:  impossible d'insérer la valeur dans une colonne d'identité avec
            les mots-clés GENERATED ALWAYS

Voyons avec la valeur NULL pour la clé :

INSERT INTO BAR (ID, NAME) VALUES (NULL, 'Bar');

Oracle lève la même erreur :

ORA-32795:  impossible d'insérer la valeur dans une colonne d'identité avec
            les mots-clés GENERATED ALWAYS

Donc, pour un GENERATED AS IDENTITY il ne faut pas spécifier de valeur pour le champ de la clé primaire lors des insertions, Oracle se charge de tout.

Auto-incrémentation par défaut

Passons à la deuxième façon de délarer une clé primaire avec auto-incrémentation.

CREATE TABLE BAR2 (
	ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	NAME VARCHAR2 ( 100 ) NOT NULL  
);

La table est créée, réalisons maintenant différentes insertions :

INSERT INTO BAR2 (NAME) VALUES ('Foo');

Cette première insertion sans spécifier de valeur pour la colonne auto-incrémenté passe sans problème.

INSERT INTO BAR2 (ID, NAME) VALUES (2, 'Thing');

La deuxième insertion, en donnant une valeur pour la colonne auto-incrémentée, passe également sans difficulté.

INSERT INTO BAR2 (ID, NAME) VALUES (NULL, 'Bar');

Cette troisième insertion, en passant NULL pour la colonne auto-incrémentée, lève l’erreur suivante :

ERREUR : ORA-01400: impossible d'insérer NULL dans ("TEST"."BAR2"."ID")

Nous avons donc au final pour cette table :

ID|NAME |
--+-----+
 1|Foo  |
 2|Thing|

Donc, pour un GENERATED BY DEFAULT AS IDENTITY il ne faut pas passer la valeur NULL pour le champ de la clé primaire lors des insertions.

Auto-incrémentation par défaut pour NULL

Voyons maintenant ce qu’il en est avec la troisième façon de définir un champ auto-incrémenté comme clé primaire.

CREATE TABLE BAR3 (
	ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
	NAME VARCHAR2 ( 100 ) NOT NULL  
);

La table étant créée, testons les différentes façons d’insérer les données :

INSERT INTO BAR3 (NAME) VALUES ('Foo');

Cette première insertion sans spécifier de valeur pour la colonne auto-incrémentée passe sans problème.

INSERT INTO BAR3 (ID, NAME) VALUES (2, 'Thing');

La deuxième insertion, en donnant une valeur pour la colonne auto-incrémentée, passe également sans difficulté.

INSERT INTO BAR3 (ID, NAME) VALUES (NULL, 'Bar');

La troisième insertion, en forçant à NULL pour la colonne auto-incrémentée, passe cette fois-ci sans difficulté.

Voyons maintenant les valeurs insérées :

SELECT * FROM BAR3;

Nous avons bien un ID à 3 pour l’insertion avec NULL :

ID|NAME |
--+-----+
 1|Foo  |
 2|Thing|
 3|Bar  |

Donc, pour un GENERATED BY DEFAULT ON NULL AS IDENTITY, il est possible d’utiliser tous les types d’insertion possible !

PK AI récapitulatif

Voici un tableau récapitulant les insertions possibles en fonction du type d’auto-incrémentation utilisé.

Conclusion

J’espère vous avoir fait épargner du temps en recherche de résolution de problème pour ces quelques particularités d’Oracle qui peuvent surprendre de prime abord.