UPDATE en EXECUTE IMMEDIATE ne s'effectue pas

Version imprimable

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:

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:

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

09/12/2013, 14h00
orafrance

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.
09/12/2013, 14h13
mnitu

Citation:

Envoyé par dumas.blr

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.

Citation:

il ne faut pas de ; à la fin de la chaine de caractère de EXECUTE IMMEDIATE.

J'ai remplacé l'instruction
Code:

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:

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 ?

09/12/2013, 14h36
dumas.blr

Bonjour mnitu,

Citation:

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 ?

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:

1 2 3 4 5 update table set (col1, col2, col3, ... col48) = (Select val1, val2, ... val48 from table(fonction(p_collection)) )

09/12/2013, 15h52
dumas.blr

Bonjour Mnitu,

Citation:

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 ...
09/12/2013, 16h12
mnitu

Citation:

Envoyé par dumas.blr

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.