Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 9 sur 9
  1. #1
    Membre chevronné
    Homme Profil pro Pierre-Jean
    Développeur informatique
    Inscrit en
    février 2010
    Messages
    584
    Détails du profil
    Informations personnelles :
    Nom : Homme Pierre-Jean
    Âge : 33
    Localisation : France, Seine Maritime (Haute Normandie)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : février 2010
    Messages : 584
    Points : 742
    Points
    742

    Par défaut Gérer les single quotes en PL/SQL

    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
    Citation Envoyé par paidge
    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 :
    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 :
    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 :
    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 :
    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 :
    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 :
    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;
    L'informatique fait gagner beaucoup de temps. A condition d'en avoir beaucoup devant soi !!!

  2. #2
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 091
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 091
    Points : 3 644
    Points
    3 644

    Par défaut

    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 :
    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 :
    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;

  3. #3
    Membre chevronné
    Homme Profil pro Pierre-Jean
    Développeur informatique
    Inscrit en
    février 2010
    Messages
    584
    Détails du profil
    Informations personnelles :
    Nom : Homme Pierre-Jean
    Âge : 33
    Localisation : France, Seine Maritime (Haute Normandie)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : février 2010
    Messages : 584
    Points : 742
    Points
    742

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Code :
    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).

    Citation Envoyé par skuatamad Voir le message
    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 :
    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 :
    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
    L'informatique fait gagner beaucoup de temps. A condition d'en avoir beaucoup devant soi !!!

  4. #4
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 091
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 091
    Points : 3 644
    Points
    3 644

    Par défaut

    Citation Envoyé par paidge Voir le message
    Non, cela ne suffit pas.
    Effectivement je n'avais pas lu :
    Dans la table, on veut stocker 'DEC-2012, 'JAN-2012, 'FEB-2012...etc.

  5. #5
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 091
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 091
    Points : 3 644
    Points
    3 644

    Par défaut

    Du coup pour les variables de liaison il faut concaténer avant le quote supplémentaire à la variable de base.
    Code :
    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>

  6. #6
    Expert Confirmé Sénior Avatar de mnitu
    Homme Profil pro Marius Nitu
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 478
    Détails du profil
    Informations personnelles :
    Nom : Homme Marius Nitu
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 4 478
    Points : 8 906
    Points
    8 906

    Par défaut

    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.

  7. #7
    Membre chevronné
    Homme Profil pro Pierre-Jean
    Développeur informatique
    Inscrit en
    février 2010
    Messages
    584
    Détails du profil
    Informations personnelles :
    Nom : Homme Pierre-Jean
    Âge : 33
    Localisation : France, Seine Maritime (Haute Normandie)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : février 2010
    Messages : 584
    Points : 742
    Points
    742

    Par défaut

    Je te rassure on utilise Oracle Appli chez nous. Donc les dates sont bien stockées en tant que date. Pour les besoins utilisateur, nous avons développé une procédure qui remplit une table temporaire sur laquelle sera généré un flux XML pour remplir un tableau EXCEL. Il s'agit d'une analyse croisée et les données doivent être affichées mois par mois. C'est pour un besoin spécifique que je me suis penché sur le problème En aucun cas le but est d'inciter les gens à stocker des dates de cette manière. Finalement, il est plus logique (et tu l'as bien souligné ), le quote est rajouté par le programme d'extraction du flux XML (mais le problème est le même ) et, chose que nous n'arrivons pas à comprendre : EXCEL affiche le single quote !!!! Sauf si on se place sur la cellule et qu'on appuie sur ENTREE : là, la quote disparaît comme par magie....Mais c'est un autre sujet
    L'informatique fait gagner beaucoup de temps. A condition d'en avoir beaucoup devant soi !!!

  8. #8
    Expert Confirmé Sénior Avatar de mnitu
    Homme Profil pro Marius Nitu
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 478
    Détails du profil
    Informations personnelles :
    Nom : Homme Marius Nitu
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 4 478
    Points : 8 906
    Points
    8 906

    Par défaut

    Je ne vois pas trop en quoi le problème est le même, mais bon
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
     
    Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
    Connected AS mni
     
    SQL> 
    SQL> SELECT REPLACE(
      2         XMLElement("employeeReport"
      3          ,XMLAgg(
      4            XMLElement("employee", XMLAttributes(c.employee_id AS "id")
      5             ,XMLElement("Name", c.first_name)
      6              ,XMLElement("hireDate", To_Char(c.hire_date,'MON-YYYY'))
      7            )
      8          )
      9        ).extract('/'),'<hireDate>',q'!<hireDate>'!') as xml_output
     10    FROM hr.employees c
     11   where c.department_id = 60
     12  /
     
    XML_OUTPUT
    --------------------------------------------------------------------------------
    <employeeReport>
      <employee id="103">
        <Name>Alexander</Name>
        <hireDate>'JANV.-1990</hireDate>
      </employee>
      <employee id="104">
        <Name>Bruce</Name>
        <hireDate>'MAI  -1991</hireDate>
      </employee>
      <employee id="105">
        <Name>David</Name>
        <hireDate>'JUIN -1997</hireDate>
      </employee>
      <employee id="106">
        <Name>Valli</Name>
        <hireDate>'FÉVR.-1998</hireDate>
      </employee>
      <employee id="107">
        <Name>Diana</Name>
        <hireDate>'FÉVR.-1999</hireDate>
      </employee>
    </employeeReport>
     
    SQL>

  9. #9
    Membre chevronné
    Homme Profil pro Pierre-Jean
    Développeur informatique
    Inscrit en
    février 2010
    Messages
    584
    Détails du profil
    Informations personnelles :
    Nom : Homme Pierre-Jean
    Âge : 33
    Localisation : France, Seine Maritime (Haute Normandie)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : février 2010
    Messages : 584
    Points : 742
    Points
    742

    Par défaut

    Bonjour Mnitu et merci pour ton exemple Il me sera sûrement utile un jour. Mais le sujet ici c'est la gestion des apostrophes (qui d'ailleurs te fait défaut puisque tu as mis un single quote de trop juste avant le 2e point d'exclamation :-P)
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    declare
    v_output VARCHAR2(4000);
    begin
    SELECT REPLACE(
             XMLElement("employeeReport"
              ,XMLAgg(
                XMLElement("employee", XMLAttributes(c.person_id AS "id")
                ,XMLElement("Name", c.first_name)
                 ,XMLElement("BirthDate", To_Char(c.date_of_birth,'MON-YYYY'))
               )
              )
           ).extract('/'),'<BirthDate>',q'!<BirthDate>!') AS xml_output
           INTO v_output
        FROM per_all_people_f c
       WHERE c.person_id = 171;
       dbms_output.put_line(v_output);
    end;
    Donc quand je dis que le problème est le même, je veux dire par là que pour insérer une quote dans une chaîne de caractères, que ce soit avant insertion dans une table ou quand on extrait les données, le problème est le même : Il faut concaténer '''' avec ta chaîne.....Le nombre d'apostrophes dépendra du cas dans lequel on se trouve.

    Attention à bien rester dans le sujet qui n'est ni la gestion des dates, ni la gestion des flux XML
    L'informatique fait gagner beaucoup de temps. A condition d'en avoir beaucoup devant soi !!!

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •