Discussion :

[paidge]

Bonjour, d'habitude on vient demander de l'aide sur les forums, mais là j'ai réussi à trouver la solution que je n'ai pas dénichée sur Internet. Je me permets donc de poster un petit article que j'ai rédigé pour moi-même et qui pourrait éventuellement être intégré à la FAQ ou, du moins, servir à quelqu'un d'autre

La règle : Quand on veut avoir un single quote dans une chaîne de caractère, il faut le doubler pour l'échapper.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dbms_output.put_line('Je m''achète une maison');

Dans la table, on veut stocker 'DEC-2012, 'JAN-2012, 'FEB-2012...etc.
Mais comme il s'agit d'une chaîne de caratères, il faut les entourer avec des single quotes.
En toute logique on aurait donc envie d'écrire : ''DEC-2012',''JAN-2012',''FEB-2012'...etc.
Mais cela ne fonctionnera pas.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO MaTable(Champs1, champs2) VALUES (''DEC-2012', ''JAN-2012');

En effet, Si on exécute la requête telle quelle, le moteur croira qu'il s'agit de la fin de la chaîne de caractères lorsqu'il tombera sur le 2e single quote et plantera. COmme le montre la coloration syntaxique d'ailleurs

Il faut donc appliquer la règle vue plus haut et écrire : '''DEC-2012', '''JAN-2012', '''FEB-2012',....
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO MaTable(Champs1, champs2) VALUES ('''DEC-2012', '''JAN-2012');

Cela fonctionnera si on écrit directement la requête dans Toad ou dans un bloc PL/SQL par exemple.

Ici, la requête est stockée dans une variable VARCHAR (pour être exécutée avec EXECUTE IMMEDIATE), donc elle-même entourée par des single quote.
Il faut donc, là encore, échapper chaque single quote avec un autre single quote.
Exemple (qui fonctionne) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (''''''DEC-2012'',''''''JAN-2012'')';
EXECUTE IMMEDIATE v_maVariable;

Dans L'exemple ci-dessous, les valeurs DEC-2012, JAN-2012, etc. se trouvent dans une variable. Il faut donc les concaténer comme ceci :
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
v_valeur1 := 'DEC-2012';
v_valeur2 := 'JAN-2012';
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (''''''' || v_valeur1 || ''', ''''''' || v_valeur2 || ''')';
EXECUTE IMMEDIATE v_maVariable;

Autre exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
v_valeur1 := 'DEC-2012';
v_valeur2 := 'JAN-2012';
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (';
v_maVariable := v_maVariable || '''''''' || v_valeur1 || ''',';
v_maVariable := v_maVariable || '''''''' || v_valeur2 || ''')';
EXECUTE IMMEDIATE v_maVariable;

[skuatamad]

Quelques précisions pour se faciliter la vie :

La règle : Quand on veut avoir un single quote dans une chaîne de caractère, il faut le doubler pour l'échapper.

C'est vrai mais il peut y avoir plus simple : quoting string literals in 10g

Il faut donc appliquer la règle vue plus haut et écrire : '''DEC-2012', '''JAN-2012', '''FEB-2012',....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO MaTable(Champs1, champs2) VALUES ('DEC-2012', 'JAN-2012');

suffit ou je n'ai pas compris le problème.

Dans L'exemple ci-dessous, les valeurs DEC-2012, JAN-2012, etc. se trouvent dans une variable. Il faut donc les concaténer comme ceci

Ou utiliser des variables de liaison (bind variable), plus scalable, pas de risque d'injection sql et beaucoup plus simple à écrire, que du positif :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
v_valeur1 := 'DEC-2012';
v_valeur2 := 'JAN-2012';
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (:valeur1, :valeur2)';
EXECUTE IMMEDIATE v_maVariable using v_valeur1, v_valeur2;

[paidge]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO MaTable(Champs1, champs2) VALUES ('DEC-2012', 'JAN-2012');

suffit ou je n'ai pas compris le problème.

Non, cela ne suffit pas. Le but (enfin le but que je m'étais fixé) était d'insérer chaque valeur (DEC-2012, JAN-2013,etc.) avec un sinle quote devant. Car, pour les intégrer avec du XML dans excel, si on ne met de single quote en préfixe, c'est reconnu comme un champ DATE (la faute à Excel :p).

Ou utiliser des variables de liaison (bind variable), plus scalable, pas de risque d'injection sql et beaucoup plus simple à écrire, que du positif :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
v_valeur1 := 'DEC-2012';
v_valeur2 := 'JAN-2012';
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (:valeur1, :valeur2)';
EXECUTE IMMEDIATE v_maVariable using v_valeur1, v_valeur2;

Merci pour le tuyau Etant un "débutant" en PL/SQL, je n'avais pas l'habitude d'utiliser ça. Donc si je reprends ton exemple, on aura :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
v_valeur1 := '''DEC-2012';
v_valeur2 := '''JAN-2012';
v_maVariable := 'INSERT INTO MaTable(Champs1, champs2) VALUES (:valeur1, :valeur2)';
EXECUTE IMMEDIATE v_maVariable USING v_valeur1, v_valeur2;

Et merci pour ton lien ça peut être effectivement pratique

[skuatamad]

Non, cela ne suffit pas.

Effectivement je n'avais pas lu :

Dans la table, on veut stocker 'DEC-2012, 'JAN-2012, 'FEB-2012...etc.

[skuatamad]

Du coup pour les variables de liaison il faut concaténer avant le quote supplémentaire à la variable de base.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> declare
  2      v_quote varchar2(1) := '''';
  3      v_valeur1 varchar2(10) := 'DEC-2012';
  4      v_valeur2 varchar2(10) := 'JAN-2012';
  5      v_maVariable varchar(100);
  6  begin
  7      v_valeur1 := v_quote || v_valeur1;
  8      v_valeur2 := v_quote || v_valeur2;
  9      v_maVariable := 'INSERT INTO test_quote(c1, c2) VALUES (:valeur1, :valeur2)';
 10      EXECUTE IMMEDIATE v_maVariable USING v_valeur1, v_valeur2;
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from test_quote;
 
C1         C2
---------- ----------
'DEC-2012  'JAN-2012
 
SQL>

[mnitu]

C’est une idée folle de stocker des dates dans des zones de type varchar au lieu de type date. Et encore plus nuisible d’en ajouter la cote devant pour de raison d’Excel.
La démarche correcte est de stocker des dates dans des (types) date et de les formater en sortie comme bon il vous semble : les formater , ajouter des autres caractères, etc.