IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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

PL/SQL Oracle Discussion :

UPDATE en EXECUTE IMMEDIATE ne s'effectue pas [11g]


Sujet :

PL/SQL Oracle

  1. #1
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut UPDATE en EXECUTE IMMEDIATE ne s'effectue pas
    Bonjour,

    Afin de faciliter la maintenance de mon code, j'ai souhaité Exécuter du SQL dynamique dans une procédure UPDATE.

    Le code se compile bien et les instructions semble bien s'exécuter, sauf que mon EXECUTE IMMEDIATE ne met pas du tout ma table à jour.

    Voici mon code :

    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
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    CREATE OR REPLACE PROCEDURE "UPDATE_LOT_PRE"
     
     AS
    BEGIN
      DECLARE
        /*
        **      Variables
        */
        Wrk_Traitement VARCHAR2(60);
        Wrk_NomTraite  VARCHAR2(60);
        Wrk_FlagTraite CHAR(1); -- Statut du traitement ("0"=Ok, "1"=Warning, "2"=Erreur passante, "3"=Erreur bloquante
        -- )
        Wrk_LibelAction  VARCHAR2(255);
        Wrk_ObjetAction  VARCHAR2(255);
        Wrk_LibErrAction VARCHAR2(255);
        Wrk_LibErrObjet  VARCHAR2(255);
        TYPE TYPE_TAB IS VARRAY(50) OF VARCHAR2(3);
        ma_TABle   TYPE_TAB := TYPE_TAB('ACH', 'ACN', 'FBN', 'FTN', 'DDN', 'CLN',
                                        'FIN', 'ECH', 'QDV', 'PKN', 'QCF', 'FFN',
                                        'CEE', 'CFF', 'TBL', 'FFB', 'FOT', 'TMP',
                                        'PKB', 'TPF', 'ECN', 'FFC', 'ACM', 'FRB',
                                        'FRT', 'CIF', 'DOU', 'CLE', 'FIE', 'CDV',
                                        'CFE', 'SRP', 'FRF', 'PRE', 'FIB', 'SKB',
                                        'REC', 'FBB', 'PTD', 'FSA', 'AFT', 'FTB',
                                        'INS', 'DDB', 'TCO', 'RCP', 'IN2', 'RE2');
        LC$Requete VARCHAR2(256);
        /*
        **                      PROCEDURE
        */
      BEGIN
        DBMS_OUTPUT.PUT_LINE('debut traitement');
        /*
        **      Initialisation des valeurs
        */
        Wrk_Traitement := 'Chargement DWH';
        Wrk_NomTraite  := 'UPDATE_LOT_PRE';
        BEGIN
          FOR I IN 1 .. 48 LOOP
            BEGIN
              LC$Requete := 'UPDATE LOT_PRE SET COUT_E' ||
                            trim(TO_CHAR(I + 1, '000')) ||
                            ' = F_EW_COUT_PRE_LOT(CODSOC, CODPRO, ''' ||
                            ma_TABLE(I) ||
                            ''', NUMLOT, OST001, OST002, OST003);';
              DBMS_OUTPUT.PUT_LINE(LC$Requete);
              --        execute immediate 'select sysdate from dual';
              BEGIN
                  EXECUTE immediate LC$Requete;
               DBMS_OUTPUT.PUT_LINE('execution de la requete');
               EXCEPTION
                WHEN OTHERS THEN
                  BEGIN
                    Wrk_FlagTraite   := '3';
                    Wrk_LibelAction  := 'MAJ COUT_E' ||
                                        trim(TO_CHAR(I + 1, '000')) ||
                                        ' DANS LOT_PRE';
                    Wrk_ObjetAction  := 'MAJ COUT_E' ||
                                        trim(TO_CHAR(I + 1, '000')) ||
                                        'DANS LOT_PRE';
                    Wrk_LibErrAction := 'ERREUR DANS MAJ DE LOT_PRE';
                    Wrk_LibErrObjet  := SUBSTR(SQLERRM, 1, 255);
                    INSERT_LOGALIM(Wrk_Traitement, Wrk_NomTraite,
                                   Wrk_FlagTraite, Wrk_LibelAction,
                                   Wrk_ObjetAction, Wrk_LibErrAction,
                                   Wrk_LibErrObjet);
                  END;
              END;
             DBMS_OUTPUT.PUT_LINE('COMMIT');
     
              COMMIT;
            END;
          END LOOP;
        END;
      END;
    end;
    Lorsque j’exécute manuellement l'instruction suivante (issue du DBMS_OUTPUT.PUT_LINE(LC$Requete) ci-dessus)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    UPDATE LOT_PRE SET COUT_E002 = F_EW_COUT_PRE_LOT(CODSOC, CODPRO, 'ACH', NUMLOT, OST001, OST002, OST003);
    COMMIT;
    La table se met bien à jour
    Je pense que je doit passer un paramètre en USING, mais je ne vois pas du tout lequel.

    Merci d'avance pour vos réponses éclairées
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  2. #2
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Points : 19 073
    Points
    19 073
    Par défaut
    il ne faut pas de ; à la fin de la chaine de caractère de EXECUTE IMMEDIATE.

    Supprime la gestion d'exception dans un premier temps pour voir les erreurs.

  3. #3
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par dumas.blr Voir le message
    Bonjour,

    Afin de faciliter la maintenance de mon code, j'ai souhaité Exécuter du SQL dynamique dans une procédure UPDATE.
    ...
    Vous est en train de faire fausse route.

  4. #4
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    il ne faut pas de ; à la fin de la chaine de caractère de EXECUTE IMMEDIATE.
    J'ai remplacé l'instruction

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     LC$Requete := 'UPDATE LOT_PRE SET COUT_E' ||
                            trim(TO_CHAR(I + 1, '000')) ||
                            ' = F_EW_COUT_PRE_LOT(CODSOC, CODPRO, ''' ||
                            ma_TABLE(I) ||
                            ''', NUMLOT, OST001, OST002, OST003);';
    par
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     LC$Requete := 'UPDATE LOT_PRE SET COUT_E' ||
                            trim(TO_CHAR(I + 1, '000')) ||
                            ' = F_EW_COUT_PRE_LOT(CODSOC, CODPRO, ''' ||
                            ma_TABLE(I) ||
                            ''', NUMLOT, OST001, OST002, OST003)';
    et effectivement ça a l'air de marcher.
    Pouvez vous m'expliquer la subtilité ?

    de plus, les traces ne s'affichent pas au fur et à mesure de l'exécution de la boucle, mais en fin de procédure. comment est gérée la dé-bufferisation des DBMS_OUTPUT.PUT_LINE ?
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  5. #5
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    Bonjour mnitu,

    Vous est en train de faire fausse route.
    Je pensais justement que répéter 50 fois la même instruction en changeant à chaque fois un paramètres ne permettait pas une une maintenance aisée du code, et qu'il me semblait plus pertinent d'exécuter dynamiquement cette instruction.
    Quelle solution me préconiseriez vous à la place ?
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  6. #6
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    N'utilisez pas Sql dynamique là ou le Sql statique suffit. Il y en a plusieurs raison pour cela.
    Faire 48 fois update dans une boucle est de loin moins performant que faire un seul update de 48 colonnes.
    Idéal est concevoir votre fonction pour qu'il y a un update de type
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    update table 
      set (col1, col2, col3, ... col48) = (Select val1, val2, ... val48
                                                       from table(fonction(p_collection))
                                                   )

  7. #7
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    Bonjour Mnitu,

    Faire 48 fois update dans une boucle est de loin moins performant que faire un seul update de 48 colonnes.
    Je suis tout à fait d’accords avec vous sur le principe, sauf que nous avions déjà essayé de le faire un nombre de colonne équivalent avec une volumétrie identique, ce qui a eu pour résultat de saturer les espaces temporaire (le TEMP001). Nous avons alors été obligés de splitter l'update. Ceci explique mon besoin. A part une augmentation de l'extent dans le tablespace par le DBA, je ne vois pas trop comment faire autrement ...
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  8. #8
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par dumas.blr Voir le message
    Je suis tout à fait d’accords avec vous sur le principe, sauf que nous avions déjà essayé de le faire un nombre de colonne équivalent avec une volumétrie identique, ce qui a eu pour résultat de saturer les espaces temporaire (le TEMP001). ...
    Un update qui fait sauter l'espace temporaire c'est bizarre! C'est quoi le type des colonnes que vous mettez à jour ? Analysez alors aussi la fonction qui fourni les valeur pour l'update.
    Bref si cela est vrai alors probablement que ce n'est pas un update qu'il faut faire.

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

Discussions similaires

  1. execute immediate ne fonctionne pas
    Par goute dans le forum Débuter
    Réponses: 2
    Dernier message: 31/08/2009, 10h43
  2. Réponses: 4
    Dernier message: 11/10/2007, 09h51
  3. EXECUTE IMMEDIATE pas exécuté immédiatement
    Par dyvim dans le forum Oracle
    Réponses: 4
    Dernier message: 11/05/2006, 23h43
  4. [sgbd] [CGI + MySQL] Insert ne s'effectuant pas
    Par GLDavid dans le forum SGBD
    Réponses: 3
    Dernier message: 27/07/2004, 03h43
  5. [Executable] Intégrer les DLL ou pas?
    Par Superstivix dans le forum C++Builder
    Réponses: 3
    Dernier message: 14/06/2004, 11h17

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