Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut SQLERRM:ORA-00932: types de données incohérents ; attendu : - ; obtenu : -

    Bonjour

    Je rencontre l'erreur
    SQLERRM:ORA-00932: types de données incohérents ; attendu : - ; obtenu : -


    La requete SQL fonctionne
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    SELECT * FROM (SELECT '18993','[T.S4A.S4A.RJDLK.001.QAG QTPYTL, T.S4A.S4A.RJDLK.001.QAP QTPYTL]' from dual);
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    MAIS PAS LE EXECUTE IMMEDIATE
      v_tmpS:='(SELECT ''18993'',''[T.S4A.S4A.RJDLK.001.QAG QTPYTL, T.S4A.S4A.RJDLK.001.QAP QTPYTL]'' from dual)';
     EXECUTE IMMEDIATE 'SELECT * FROM' || v_tmpS INTO v_tmpS2;

  2. #2
    Membre expert
    Avatar de Sunchaser
    Homme Profil pro
    OPNI (Objet Programmant Non Identifié)
    Inscrit en
    décembre 2004
    Messages
    2 011
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Manche (Basse Normandie)

    Informations professionnelles :
    Activité : OPNI (Objet Programmant Non Identifié)
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : décembre 2004
    Messages : 2 011
    Points : 3 072
    Points
    3 072

    Par défaut

    Bonjour,

    Tu devrais surement préciser la nature de "v_tmpS2".
    Qu'est ce donc? Un Type?

    @+
    Aux persévérants aucune route n'est interdite.
    Celui qui ne sait pas se contenter de peu ne sera jamais content de rien.
    Current Status
    Avec 40% de pollinisateurs invertébrés menacés d'extinction selon les Nations Unies, l'homme risque fort de passer de la monoculture à la mono diète...
    Faîtes quelque chose de bien avec vos petits sous: Enfants du Mekong

  3. #3
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    bonjour

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
     v_tmpS2      varchar2(30000);
    Mon soucis vient du fait que le nombre de colonnes est dynamique

  4. #4
    Membre expert
    Avatar de Sunchaser
    Homme Profil pro
    OPNI (Objet Programmant Non Identifié)
    Inscrit en
    décembre 2004
    Messages
    2 011
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Manche (Basse Normandie)

    Informations professionnelles :
    Activité : OPNI (Objet Programmant Non Identifié)
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : décembre 2004
    Messages : 2 011
    Points : 3 072
    Points
    3 072

    Par défaut

    Bonjour,

    Je ne sais pas à quoi cela sert, mais ça à l'air sympa

    Je ne crois pas que pouvoir stocker tes résultats dans des variables ou un Type si le nombre de colonnes retournées est dynamique soit "direct".
    La première chose que j'imaginerais est de stocker plutôt dans une collection.
    Avec qqchose comme:

    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
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
     
    CREATE OR REPLACE TYPE store_some_char AS TABLE OF VARCHAR2 (500 CHAR);
    /
     
     
    /* ---- */
    DECLARE
       temp_store       store_some_char;
       temp_model_sql   VARCHAR2 (500 CHAR)
                           DEFAULT '(SELECT ''18993'' value1,''[T.S4A.S4A.RJDLK.001.QAG QTPYTL, T.S4A.S4A.RJDLK.001.QAP QTPYTL]'' value2 from dual)';
       dyn_sql          VARCHAR2 (500 CHAR);
    BEGIN
       dyn_sql := 'SELECT store_some_char(value1, value2) FROM' || temp_model_sql;
       DBMS_OUTPUT.put_line (dyn_sql);
     
       EXECUTE IMMEDIATE dyn_sql INTO temp_store;
     
       FOR i IN 1 .. temp_store.COUNT
       LOOP
          DBMS_OUTPUT.put_line (temp_store(i));
       END LOOP;
    END;
     
    /* -- ou bien même: -- */
    DECLARE
       temp_store   store_some_char;
       dyn_sql      VARCHAR2 (500 CHAR)
                       DEFAULT 'SELECT store_some_char(''18993'',''[T.S4A.S4A.RJDLK.001.QAG QTPYTL, T.S4A.S4A.RJDLK.001.QAP QTPYTL]'') from dual';
    BEGIN
       DBMS_OUTPUT.put_line (dyn_sql);
     
       EXECUTE IMMEDIATE dyn_sql INTO temp_store;
     
       FOR i IN 1 .. temp_store.COUNT
       LOOP
          DBMS_OUTPUT.put_line (temp_store (i));
       END LOOP;
    END;
    Cela implique que tous les enregistrements sont du même type (varchar2 ici), ou peuvent être casté vers le même type.

    Sinon, il y a l'option de faire un DBMS_SQL.parse sur ton SQL dynamique, récupérer toutes les infos nécessaires sur les colonnes retournées par ce SQL, puis créer un type dynamique via ANYTYPE / ANYDATASET.
    C'est un peu plus "sport", je pense.

    Peut être qqu'un d'autre aura une meilleure idée ...

    @+
    Aux persévérants aucune route n'est interdite.
    Celui qui ne sait pas se contenter de peu ne sera jamais content de rien.
    Current Status
    Avec 40% de pollinisateurs invertébrés menacés d'extinction selon les Nations Unies, l'homme risque fort de passer de la monoculture à la mono diète...
    Faîtes quelque chose de bien avec vos petits sous: Enfants du Mekong

  5. #5
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    Merci, je te dis si cela est ok Jeudi, car je ne suis pas au bureau en debut de semaine !
    Pour info la requete SQL est plus longue, ce sont des noms de file IBM MQ entre plusieurs applications

  6. #6
    Membre expert
    Homme Profil pro
    Inscrit en
    septembre 2006
    Messages
    2 620
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : septembre 2006
    Messages : 2 620
    Points : 3 676
    Points
    3 676

    Par défaut

    Pour récupérer des résultats dynamiques, vous pouvez passer par un sys_refcursor, obtenir son cursor number via dbms_sql.to_cursor_number et ensuite vous avez accès à la description des colonnes via dbms_sql.describe_columns, en bouclant sur le nombre de colonnes vous pouvez faire un dbms_sql.define_column et ensuite en bouclant sur dbms_sql.fetch_rows vous récupérez les valeurs via dbms_sql.column_value...

  7. #7
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    Bonjour

    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
    23
    24
     
    procedure test2
    is 
    TYPE store_some_char IS TABLE OF VARCHAR2 (500 CHAR);
       temp_store   store_some_char;
       dyn_sql      VARCHAR2 (500 CHAR)
                       DEFAULT 'SELECT store_some_char(''18993'',''[T.S4A.S4A.RJDLK.001.QAG QTPYTL, T.S4A.S4A.RJDLK.001.QAP QTPYTL]'') from dual';
    BEGIN
     
       DBMS_OUTPUT.put_line (dyn_sql);
       EXECUTE IMMEDIATE dyn_sql INTO temp_store;
     
       FOR i IN 1 .. temp_store.COUNT
     
       LOOP
     
          DBMS_OUTPUT.put_line (temp_store (i));
     
       END LOOP;
     
     
    end test2;
     
    j'ai un TEMP_STORE in the INTO list of wrong type !
    Merci

  8. #8
    Membre expert
    Avatar de Sunchaser
    Homme Profil pro
    OPNI (Objet Programmant Non Identifié)
    Inscrit en
    décembre 2004
    Messages
    2 011
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Manche (Basse Normandie)

    Informations professionnelles :
    Activité : OPNI (Objet Programmant Non Identifié)
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : décembre 2004
    Messages : 2 011
    Points : 3 072
    Points
    3 072

    Par défaut

    Bonjour,

    Perso, dans le bout de code que j'avais testé (et qui fonctionne chez moi), j'ai crée un type au niveau DB et non pas localement dans ma procédure:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    CREATE OR REPLACE TYPE store_some_char AS TABLE OF VARCHAR2 (500 CHAR);
    /
    Lorsque je remplace ton "TYPE store_some_char IS TABLE OF VARCHAR2 (500 CHAR); temp_store store_some_char;" par mon type, le même code que le tien (celui de ton dernier message - j'ai fait un copié-collé) fonctionne pour moi.

    @+
    Aux persévérants aucune route n'est interdite.
    Celui qui ne sait pas se contenter de peu ne sera jamais content de rien.
    Current Status
    Avec 40% de pollinisateurs invertébrés menacés d'extinction selon les Nations Unies, l'homme risque fort de passer de la monoculture à la mono diète...
    Faîtes quelque chose de bien avec vos petits sous: Enfants du Mekong

  9. #9
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    Re
    Désolé je n'ai pas tout compris je le met donc en haut du PACKAGE BODY en dehors des fonctions ?

  10. #10
    Membre expert
    Avatar de Sunchaser
    Homme Profil pro
    OPNI (Objet Programmant Non Identifié)
    Inscrit en
    décembre 2004
    Messages
    2 011
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Manche (Basse Normandie)

    Informations professionnelles :
    Activité : OPNI (Objet Programmant Non Identifié)
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : décembre 2004
    Messages : 2 011
    Points : 3 072
    Points
    3 072

    Par défaut

    Non, dans ce cas, cet object / ce type est un objet séparé de ton package et accessible pour toute procédure ou package dans ton schéma.
    Je le crée donc séparemment de la procédure ou package.
    Aux persévérants aucune route n'est interdite.
    Celui qui ne sait pas se contenter de peu ne sera jamais content de rien.
    Current Status
    Avec 40% de pollinisateurs invertébrés menacés d'extinction selon les Nations Unies, l'homme risque fort de passer de la monoculture à la mono diète...
    Faîtes quelque chose de bien avec vos petits sous: Enfants du Mekong

  11. #11
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    BEGIN
    TOOLS.test2;
    END;
    Rapport d'erreur -
    ORA-00932: types de données incohérents ; attendu : - ; obtenu : -
    ORA-06512: à "MQEMMO.TOOLS", ligne 1280
    ORA-06512: à ligne 3
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
    *Cause:

    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
     
    procedure test2
     is 
    -- TYPE store_some_char IS TABLE OF VARCHAR2 (15000 CHAR);
       temp_store   store_some_char;
           dyn_sql      VARCHAR2 (15000 CHAR)             DEFAULT 'SELECT * FROM(SELECT ''29037'' as IDFLUX,'''' as EMPREINTE from dual),(select substr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,1)+1,instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,2)-1-instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,1)) as APPLIPUT1 from DUAL), (SELECT null as QMPUT1 from dual), (SELECT null as QAPUT1 from dual), (SELECT null as APPLIPUT2 from dual), (SELECT null as QMPUT2 from dual), (SELECT null as QAPUT2 from dual), (SELECT null as APPLIPUT3 from dual), (SELECT null as QMPUT3 from dual), (SELECT null as QAPUT3 from dual), (SELECT null as APPLIPUT4 from dual), (SELECT null as QMPUT4 from dual), (SELECT null as QAPUT4 from dual), (SELECT null as APPLIPUT5 from dual), (SELECT null as QMPUT5 from dual), (SELECT null as QAPUT5 from dual), (SELECT null as APPLIPUT6 from dual), (SELECT null as QMPUT6 from dual), (SELECT null as QAPUT6 from dual), (SELECT null as APPLIPUT7 from dual), (SELECT null as QMPUT7 from dual), (SELECT null as QAPUT7 from dual), (SELECT null as APPLIPUT8 from dual), (SELECT null as QMPUT8 from dual), (SELECT null as QAPUT8 from dual), (SELECT null as APPLIPUT9 from dual), (SELECT null as QMPUT9 from dual), (SELECT null as QAPUT9 from dual), (SELECT null as APPLIPUT10 from dual), (SELECT null as QMPUT10 from dual), (SELECT null as QAPUT10 from dual), (SELECT null as APPLIPUT11 from dual), (SELECT null as QMPUT11 from dual), (SELECT null as QAPUT11 from dual), (SELECT null as APPLIPUT12 from dual), (SELECT null as QMPUT12 from dual), (SELECT null as QAPUT12 from dual), (SELECT null as APPLIPUT13 from dual), (SELECT null as QMPUT13 from dual), (SELECT null as QAPUT13 from dual), (SELECT null as APPLIPUT14 from dual), (SELECT null as QMPUT14 from dual), (SELECT null as QAPUT14 from dual), (SELECT null as APPLIPUT15 from dual), (SELECT null as QMPUT15 from dual), (SELECT null as QAPUT15 from dual), (SELECT null as APPLIPUT16 from dual), (SELECT null as QMPUT16 from dual), (SELECT null as QAPUT16 from dual), (SELECT null as APPLIPUT17 from dual), (SELECT null as QMPUT17 from dual), (SELECT null as QAPUT17 from dual), (SELECT null as APPLIPUT18 from dual), (SELECT null as QMPUT18 from dual), (SELECT null as QAPUT18 from dual), (SELECT null as APPLIPUT19 from dual), (SELECT null as QMPUT19 from dual), (SELECT null as QAPUT19 from dual), (SELECT null as APPLIPUT20 from dual), (SELECT null as QMPUT20 from dual), (SELECT null as QAPUT20 from dual), (SELECT null as APPLIPUT21 from dual), (SELECT null as QMPUT21 from dual), (SELECT null as QAPUT21 from dual), (SELECT null as APPLIPUT22 from dual), (SELECT null as QMPUT22 from dual), (SELECT null as QAPUT22 from dual), (SELECT null as APPLIPUT23 from dual), (SELECT null as QMPUT23 from dual), (SELECT null as QAPUT23 from dual), (SELECT null as APPLIPUT24 from dual), (SELECT null as QMPUT24 from dual), (SELECT null as QAPUT24 from dual), (SELECT null as APPLIPUT25 from dual), (SELECT null as QMPUT25 from dual), (SELECT null as QAPUT25 from dual), (SELECT null as APPLIPUT26 from dual), (SELECT null as QMPUT26 from dual), (SELECT null as QAPUT26 from dual), (SELECT null as APPLIPUT27 from dual), (SELECT null as QMPUT27 from dual), (SELECT null as QAPUT27 from dual), (SELECT null as APPLIPUT28 from dual), (SELECT null as QMPUT28 from dual), (SELECT null as QAPUT28 from dual), (SELECT null as APPLIPUT29 from dual), (SELECT null as QMPUT29 from dual), (SELECT null as QAPUT29 from dual), (SELECT null as APPLIPUT30 from dual), (SELECT null as QMPUT30 from dual), (SELECT null as QAPUT30 from dual), (SELECT null as APPLIPUT31 from dual), (SELECT null as QMPUT31 from dual), (SELECT null as QAPUT31 from dual), (SELECT null as APPLIPUT32 from dual), (SELECT null as QMPUT32 from dual), (SELECT null as QAPUT32 from dual), (SELECT null as APPLIPUT33 from dual), (SELECT null as QMPUT33 from dual), (SELECT null as QAPUT33 from dual),(select app.code as APPLIGET1 from link_appli l1,application app where l1.id_application_link=50222 and app.id_application=l1.id_application) c1GET,(select qm1.mq_name  as QMGET1 from link_appli l1,MQ_QUEUEALIAS qa1,  MQ_QMANAGER qm1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS AND qa1.ID_QMANAGER=qm1.id_qmanager) QM1GET,(select qa1.mq_name  as QAPGET1 from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS) QA1GET, (SELECT null as APPLIGET2 from dual), (SELECT null as QMGET2 from dual), (SELECT null as QAGET2 from dual), (SELECT null as APPLIGET3 from dual), (SELECT null as QMGET3 from dual), (SELECT null as QAGET3 from dual), (SELECT null as APPLIGET4 from dual), (SELECT null as QMGET4 from dual), (SELECT null as QAGET4 from dual), (SELECT null as APPLIGET5 from dual), (SELECT null as QMGET5 from dual), (SELECT null as QAGET5 from dual), (SELECT null as APPLIGET6 from dual), (SELECT null as QMGET6 from dual), (SELECT null as QAGET6 from dual), (SELECT null as APPLIGET7 from dual), (SELECT null as QMGET7 from dual), (SELECT null as QAGET7 from dual), (SELECT null as APPLIGET8 from dual), (SELECT null as QMGET8 from dual), (SELECT null as QAGET8 from dual), (SELECT null as APPLIGET9 from dual), (SELECT null as QMGET9 from dual), (SELECT null as QAGET9 from dual), (SELECT null as APPLIGET10 from dual), (SELECT null as QMGET10 from dual), (SELECT null as QAGET10 from dual), (SELECT null as APPLIGET11 from dual), (SELECT null as QMGET11 from dual), (SELECT null as QAGET11 from dual), (SELECT null as APPLIGET12 from dual), (SELECT null as QMGET12 from dual), (SELECT null as QAGET12 from dual), (SELECT null as APPLIGET13 from dual), (SELECT null as QMGET13 from dual), (SELECT null as QAGET13 from dual), (SELECT null as APPLIGET14 from dual), (SELECT null as QMGET14 from dual), (SELECT null as QAGET14 from dual), (SELECT null as APPLIGET15 from dual), (SELECT null as QMGET15 from dual), (SELECT null as QAGET15 from dual), (SELECT null as APPLIGET16 from dual), (SELECT null as QMGET16 from dual), (SELECT null as QAGET16 from dual), (SELECT null as APPLIGET17 from dual), (SELECT null as QMGET17 from dual), (SELECT null as QAGET17 from dual), (SELECT null as APPLIGET18 from dual), (SELECT null as QMGET18 from dual), (SELECT null as QAGET18 from dual), (SELECT null as APPLIGET19 from dual), (SELECT null as QMGET19 from dual), (SELECT null as QAGET19 from dual), (SELECT null as APPLIGET20 from dual), (SELECT null as QMGET20 from dual), (SELECT null as QAGET20 from dual), (SELECT null as APPLIGET21 from dual), (SELECT null as QMGET21 from dual), (SELECT null as QAGET21 from dual), (SELECT null as APPLIGET22 from dual), (SELECT null as QMGET22 from dual), (SELECT null as QAGET22 from dual), (SELECT null as APPLIGET23 from dual), (SELECT null as QMGET23 from dual), (SELECT null as QAGET23 from dual), (SELECT null as APPLIGET24 from dual), (SELECT null as QMGET24 from dual), (SELECT null as QAGET24 from dual), (SELECT null as APPLIGET25 from dual), (SELECT null as QMGET25 from dual), (SELECT null as QAGET25 from dual), (SELECT null as APPLIGET26 from dual), (SELECT null as QMGET26 from dual), (SELECT null as QAGET26 from dual), (SELECT null as APPLIGET27 from dual), (SELECT null as QMGET27 from dual), (SELECT null as QAGET27 from dual), (SELECT null as APPLIGET28 from dual), (SELECT null as QMGET28 from dual), (SELECT null as QAGET28 from dual), (SELECT null as APPLIGET29 from dual), (SELECT null as QMGET29 from dual), (SELECT null as QAGET29 from dual), (SELECT null as APPLIGET30 from dual), (SELECT null as QMGET30 from dual), (SELECT null as QAGET30 from dual), (SELECT null as APPLIGET31 from dual), (SELECT null as QMGET31 from dual), (SELECT null as QAGET31 from dual), (SELECT null as APPLIGET32 from dual), (SELECT null as QMGET32 from dual), (SELECT null as QAGET32 from dual), (SELECT null as APPLIGET33 from dual), (SELECT null as QMGET33 from dual), (SELECT null as QAGET33 from dual)';     
     
     BEGIN
       DBMS_OUTPUT.put_line (dyn_sql);
        EXECUTE IMMEDIATE dyn_sql INTO temp_store;
        FOR i IN 1 .. temp_store.COUNT
        LOOP
           DBMS_OUTPUT.put_line (temp_store (i));
        END LOOP;
    end test2;

  12. #12
    Membre expert
    Avatar de Sunchaser
    Homme Profil pro
    OPNI (Objet Programmant Non Identifié)
    Inscrit en
    décembre 2004
    Messages
    2 011
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : France, Manche (Basse Normandie)

    Informations professionnelles :
    Activité : OPNI (Objet Programmant Non Identifié)
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : décembre 2004
    Messages : 2 011
    Points : 3 072
    Points
    3 072

    Par défaut

    Salut,

    Effectivement...
    Je ne sais pas quel mécanisme te donne ce SQL dynamique (comment il est créé), mais il faudrait pouvoir faire en sorte de lister tous les champs qui sortent afin ne pas avoir le "SELECT * FROM(...", mais un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT store_some_char(IDFLUX, EMPREINTE, APPLIPUT1, QMPUT1, QAPUT1, APPLIPUT2, QMPUT2 //.... etc...
    )
    FROM (
    SELECT * FROM(SELECT ''29037'' as IDFLUX,'''' as EMPREINTE from dual),(select substr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,1)+1,instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,2)-1-instr((select qa1.mq_name    from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS),''.'',1,1)) as APPLIPUT1 from DUAL), (SELECT null as QMPUT1 from dual), (SELECT null as QAPUT1 from dual), (SELECT null as APPLIPUT2 from dual), (SELECT null as QMPUT2 from dual), (SELECT null as QAPUT2 from dual), (SELECT null as APPLIPUT3 from dual), (SELECT null as QMPUT3 from dual), (SELECT null as QAPUT3 from dual), (SELECT null as APPLIPUT4 from dual), (SELECT null as QMPUT4 from dual), (SELECT null as QAPUT4 from dual), (SELECT null as APPLIPUT5 from dual), (SELECT null as QMPUT5 from dual), (SELECT null as QAPUT5 from dual), (SELECT null as APPLIPUT6 from dual), (SELECT null as QMPUT6 from dual), (SELECT null as QAPUT6 from dual), (SELECT null as APPLIPUT7 from dual), (SELECT null as QMPUT7 from dual), (SELECT null as QAPUT7 from dual), (SELECT null as APPLIPUT8 from dual), (SELECT null as QMPUT8 from dual), (SELECT null as QAPUT8 from dual), (SELECT null as APPLIPUT9 from dual), (SELECT null as QMPUT9 from dual), (SELECT null as QAPUT9 from dual), (SELECT null as APPLIPUT10 from dual), (SELECT null as QMPUT10 from dual), (SELECT null as QAPUT10 from dual), (SELECT null as APPLIPUT11 from dual), (SELECT null as QMPUT11 from dual), (SELECT null as QAPUT11 from dual), (SELECT null as APPLIPUT12 from dual), (SELECT null as QMPUT12 from dual), (SELECT null as QAPUT12 from dual), (SELECT null as APPLIPUT13 from dual), (SELECT null as QMPUT13 from dual), (SELECT null as QAPUT13 from dual), (SELECT null as APPLIPUT14 from dual), (SELECT null as QMPUT14 from dual), (SELECT null as QAPUT14 from dual), (SELECT null as APPLIPUT15 from dual), (SELECT null as QMPUT15 from dual), (SELECT null as QAPUT15 from dual), (SELECT null as APPLIPUT16 from dual), (SELECT null as QMPUT16 from dual), (SELECT null as QAPUT16 from dual), (SELECT null as APPLIPUT17 from dual), (SELECT null as QMPUT17 from dual), (SELECT null as QAPUT17 from dual), (SELECT null as APPLIPUT18 from dual), (SELECT null as QMPUT18 from dual), (SELECT null as QAPUT18 from dual), (SELECT null as APPLIPUT19 from dual), (SELECT null as QMPUT19 from dual), (SELECT null as QAPUT19 from dual), (SELECT null as APPLIPUT20 from dual), (SELECT null as QMPUT20 from dual), (SELECT null as QAPUT20 from dual), (SELECT null as APPLIPUT21 from dual), (SELECT null as QMPUT21 from dual), (SELECT null as QAPUT21 from dual), (SELECT null as APPLIPUT22 from dual), (SELECT null as QMPUT22 from dual), (SELECT null as QAPUT22 from dual), (SELECT null as APPLIPUT23 from dual), (SELECT null as QMPUT23 from dual), (SELECT null as QAPUT23 from dual), (SELECT null as APPLIPUT24 from dual), (SELECT null as QMPUT24 from dual), (SELECT null as QAPUT24 from dual), (SELECT null as APPLIPUT25 from dual), (SELECT null as QMPUT25 from dual), (SELECT null as QAPUT25 from dual), (SELECT null as APPLIPUT26 from dual), (SELECT null as QMPUT26 from dual), (SELECT null as QAPUT26 from dual), (SELECT null as APPLIPUT27 from dual), (SELECT null as QMPUT27 from dual), (SELECT null as QAPUT27 from dual), (SELECT null as APPLIPUT28 from dual), (SELECT null as QMPUT28 from dual), (SELECT null as QAPUT28 from dual), (SELECT null as APPLIPUT29 from dual), (SELECT null as QMPUT29 from dual), (SELECT null as QAPUT29 from dual), (SELECT null as APPLIPUT30 from dual), (SELECT null as QMPUT30 from dual), (SELECT null as QAPUT30 from dual), (SELECT null as APPLIPUT31 from dual), (SELECT null as QMPUT31 from dual), (SELECT null as QAPUT31 from dual), (SELECT null as APPLIPUT32 from dual), (SELECT null as QMPUT32 from dual), (SELECT null as QAPUT32 from dual), (SELECT null as APPLIPUT33 from dual), (SELECT null as QMPUT33 from dual), (SELECT null as QAPUT33 from dual),(select app.code as APPLIGET1 from link_appli l1,application app where l1.id_application_link=50222 and app.id_application=l1.id_application) c1GET,(select qm1.mq_name  as QMGET1 from link_appli l1,MQ_QUEUEALIAS qa1,  MQ_QMANAGER qm1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS AND qa1.ID_QMANAGER=qm1.id_qmanager) QM1GET,(select qa1.mq_name  as QAPGET1 from link_appli l1,MQ_QUEUEALIAS qa1 where id_application_link=50222 and l1.id_queue=qa1.ID_QUEUEALIAS) QA1GET, (SELECT null as APPLIGET2 from dual), (SELECT null as QMGET2 from dual), (SELECT null as QAGET2 from dual), (SELECT null as APPLIGET3 from dual), (SELECT null as QMGET3 from dual), (SELECT null as QAGET3 from dual), (SELECT null as APPLIGET4 from dual), (SELECT null as QMGET4 from dual), (SELECT null as QAGET4 from dual), (SELECT null as APPLIGET5 from dual), (SELECT null as QMGET5 from dual), (SELECT null as QAGET5 from dual), (SELECT null as APPLIGET6 from dual), (SELECT null as QMGET6 from dual), (SELECT null as QAGET6 from dual), (SELECT null as APPLIGET7 from dual), (SELECT null as QMGET7 from dual), (SELECT null as QAGET7 from dual), (SELECT null as APPLIGET8 from dual), (SELECT null as QMGET8 from dual), (SELECT null as QAGET8 from dual), (SELECT null as APPLIGET9 from dual), (SELECT null as QMGET9 from dual), (SELECT null as QAGET9 from dual), (SELECT null as APPLIGET10 from dual), (SELECT null as QMGET10 from dual), (SELECT null as QAGET10 from dual), (SELECT null as APPLIGET11 from dual), (SELECT null as QMGET11 from dual), (SELECT null as QAGET11 from dual), (SELECT null as APPLIGET12 from dual), (SELECT null as QMGET12 from dual), (SELECT null as QAGET12 from dual), (SELECT null as APPLIGET13 from dual), (SELECT null as QMGET13 from dual), (SELECT null as QAGET13 from dual), (SELECT null as APPLIGET14 from dual), (SELECT null as QMGET14 from dual), (SELECT null as QAGET14 from dual), (SELECT null as APPLIGET15 from dual), (SELECT null as QMGET15 from dual), (SELECT null as QAGET15 from dual), (SELECT null as APPLIGET16 from dual), (SELECT null as QMGET16 from dual), (SELECT null as QAGET16 from dual), (SELECT null as APPLIGET17 from dual), (SELECT null as QMGET17 from dual), (SELECT null as QAGET17 from dual), (SELECT null as APPLIGET18 from dual), (SELECT null as QMGET18 from dual), (SELECT null as QAGET18 from dual), (SELECT null as APPLIGET19 from dual), (SELECT null as QMGET19 from dual), (SELECT null as QAGET19 from dual), (SELECT null as APPLIGET20 from dual), (SELECT null as QMGET20 from dual), (SELECT null as QAGET20 from dual), (SELECT null as APPLIGET21 from dual), (SELECT null as QMGET21 from dual), (SELECT null as QAGET21 from dual), (SELECT null as APPLIGET22 from dual), (SELECT null as QMGET22 from dual), (SELECT null as QAGET22 from dual), (SELECT null as APPLIGET23 from dual), (SELECT null as QMGET23 from dual), (SELECT null as QAGET23 from dual), (SELECT null as APPLIGET24 from dual), (SELECT null as QMGET24 from dual), (SELECT null as QAGET24 from dual), (SELECT null as APPLIGET25 from dual), (SELECT null as QMGET25 from dual), (SELECT null as QAGET25 from dual), (SELECT null as APPLIGET26 from dual), (SELECT null as QMGET26 from dual), (SELECT null as QAGET26 from dual), (SELECT null as APPLIGET27 from dual), (SELECT null as QMGET27 from dual), (SELECT null as QAGET27 from dual), (SELECT null as APPLIGET28 from dual), (SELECT null as QMGET28 from dual), (SELECT null as QAGET28 from dual), (SELECT null as APPLIGET29 from dual), (SELECT null as QMGET29 from dual), (SELECT null as QAGET29 from dual), (SELECT null as APPLIGET30 from dual), (SELECT null as QMGET30 from dual), (SELECT null as QAGET30 from dual), (SELECT null as APPLIGET31 from dual), (SELECT null as QMGET31 from dual), (SELECT null as QAGET31 from dual), (SELECT null as APPLIGET32 from dual), (SELECT null as QMGET32 from dual), (SELECT null as QAGET32 from dual), (SELECT null as APPLIGET33 from dual), (SELECT null as QMGET33 from dual), (SELECT null as QAGET33 from dual)
    )
    Donc je ne sais pas ce qui construit le SQL dynamique, mais puisque je vois des alias pour chaque colonne, c'est peut être possible.
    @+
    Aux persévérants aucune route n'est interdite.
    Celui qui ne sait pas se contenter de peu ne sera jamais content de rien.
    Current Status
    Avec 40% de pollinisateurs invertébrés menacés d'extinction selon les Nations Unies, l'homme risque fort de passer de la monoculture à la mono diète...
    Faîtes quelque chose de bien avec vos petits sous: Enfants du Mekong

  13. #13
    Membre confirmé
    Profil pro
    Inscrit en
    janvier 2007
    Messages
    1 443
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : janvier 2007
    Messages : 1 443
    Points : 519
    Points
    519

    Par défaut

    Bonsoir

    Mon problème est RÉSOLU, merci beaucoup pour ton aide, cela est rare de nos jours !

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

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo