Passage nom de table en paramètre à une procédure

**lu6fer** · 09/01/2009, 14h45

Bonjour,
je rencontre quelque souci avec le passage de paramètre dans un procédure.

Je voudrais passé en paramètre le nom de la table sur laquelle exécuter ma procédure. En effet j'ai 2 tables identiques, et pour des raison de disponibilité, lors d'une chargement hebdomadaire de plus de 30M lignes, j'effectue le chargement dans une table temporaire, puis la renomme (je ne suis pas certain que ce soit la meilleur méthode, mais la n'est pas le sujet

)

j'ai donc 2 tables, et une procédure. J'ai pu voir sur le forum et sur le net, qu'il fallait utilisé le execute immediate pour ce genre de demande.
Mais je ne m'en sort pas trop.

J'obtiens systématiquement des erreurs :

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
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/9      PLS-00103: Encountered the symbol "=" when expecting one of the
         following:
         constant exception <an identifier>
         <a double-quoted delimited-identifier> table LONG_ double ref
         char time timestamp interval date binary national character
         nchar
         The symbol "<an identifier>" was substituted for "=" to continue.
 
 
10/19    PLS-00103: Encountered the symbol "SQL_STR" when expecting one of
         the following:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
         := . ( @ % ; not null range default character
         The symbol ":=" was substituted for "SQL_STR" to continue.
 
15/85    PLS-00103: Encountered the symbol "||Cur.SVC_TECH_LIB from " when
         expecting one of the following:
         * & = - + ; < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
         LIKE4_ LIKEC_ between || member SUBMULTISET_
         The symbol "*" was substituted for "||Cur.SVC_TECH_LIB from " to
         continue.
 
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
26/61    PLS-00103: Encountered the symbol "MLTV" when expecting one of
         the following:
         * & = - + ; < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
         LIKE4_ LIKEC_ between || member SUBMULTISET_

Pour finir voici le procédure en question :

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
create or replace procedure OR2.UPSVCFTTH (table_name varchar2)
IS
 
SQL_STR varchar2(500);
 
SQL_STR := 'cursor C_SERV is
            select distinct PRESTATION_ID, SVC_TECH_LIB from ' || table_name ||
            'where COM_SERVICE is null order by PRESTATION_ID,SVC_TECH_LIB' ;
 
execute immediate SQL_STR;
 
BEGIN
        SQL_STR := 'for Cur in C_SERV LOOP
                    BEGIN
                        update ' || table_name || ' set COM_SERVICE = (select distinct COM_SERVICE||' '||Cur.SVC_TECH_LIB from ' || table_name ||
                        'where PRESTATION_ID = Cur.PRESTATION_ID)
                        where PRESTATION_ID = Cur.PRESTATION_ID;
                        EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
                        WHEN OTHERS then null;
                    END;
                    COMMIT;
                    END LOOP';
 
execute immediate SQL_STR;
 
SQL_STR := 'update ' || table_name || ' set COM_SERVICE = ('MLTV '||COM_SERVICE) where COM_SERVICE like ('%VOD') and COM_SERVICE not like ('MLT%')';
 
execute immediate SQL_STR;
 
COMMIT;
EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' - ERROR- '||SQLERRM);
END;
 
/

merci d'avance

**brassouille** · 09/01/2009, 15h06

Hello,
c'est bien comme ça qu'il faut t'y prendre, mais à l'intérieur de la chaine que tu passes à EXECUTE IMMEDIATE, il doit y avoir seulement une requête, et pas du code PL/SQL.

Par exemple, dans ton bloc

Code :

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

1
2
3
4
5
6
7
8
9
10
execute immediate ('for Cur in C_SERV LOOP
                    BEGIN
                        update ' || table_name || ' set COM_SERVICE = (select distinct COM_SERVICE||' '||Cur.SVC_TECH_LIB from ' || table_name ||
                        'where PRESTATION_ID = Cur.PRESTATION_ID)
                        where PRESTATION_ID = Cur.PRESTATION_ID;
                        EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
                        WHEN OTHERS then null;
                    END;
                    COMMIT;
                    END LOOP');

il faut laisser ta boucle écrite normalement, et lancer le execute immediate seulement sur ta requête update. Ce qui donnerait (mais tu vas voir plus bas que ça ne conviendra pas car tu devras parcourir le curseur manuellement)

Code :

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

1
2
3
4
5
6
7
8
9
10
for Cur in C_SERV LOOP
                    BEGIN
                        EXECUTE IMMEDIATE('update ' || table_name || ' set COM_SERVICE = (select distinct COM_SERVICE||' '||Cur.SVC_TECH_LIB from ' || table_name ||
                        'where PRESTATION_ID = Cur.PRESTATION_ID)
                        where PRESTATION_ID = Cur.PRESTATION_ID;
                        EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
                        WHEN OTHERS then null');
                    END;
                    COMMIT;
                    END LOOP;

Pour le curseur il faut :

- définir un type curseur (soit dans la partie déclarative de ta procédure, soit dans la partie déclarative de ton package...)

Code :

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

TYPE mycurseur IS REF CURSOR;

- dans ton code, tu déclares une variable cur de type mycurseur, puis tu l'initialises après ton begin :

Code :

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

OPEN cur FOR 'select toto from tata'

Bien sûr, tu peux remplacer le "select toto from tata" par une chaine de caractères que tu auras remplie en concaténant le nom de ta table.
Tu dois alors parcourir le curseur en faisant des fetch, tu ne peux pas utiliser de boucle for.
Du coup n'oublies pas de fermer ton curseur.

**mnitu** · 09/01/2009, 15h55

C’est un peu la totale des mauvais habitudes

Update dans une boucle
Commit dans une boucle
When NO_DATA_FOUND après un update – ça sert à quoi ?
When Others Then NULL
When Others Then Raise_Application_Error avec affichage SQLERRM etc.

Respirez un peu et
a) essayez d’effectuer la mise à jour en statique en prennent une des tables et en utilisant un seul update
b) une fois que vous y arrivez passer le nom de la table en dynamique sera un jeu d’enfant

**lu6fer** · 09/01/2009, 16h00

Envoyé par mnitu

C’est un peu la totale des mauvais habitudes

Update dans une boucle
Commit dans une boucle
When NO_DATA_FOUND après un update – ça sert à quoi ?
When Others Then NULL
When Others Then Raise_Application_Error avec affichage SQLERRM etc.

Respirez un peu et
a) essayez d’effectuer la mise à jour en statique en prennent une des tables et en utilisant un seul update
b) une fois que vous y arrivez passer le nom de la table en dynamique sera un jeu d’enfant

Malheureusement, cette procédure, n'est pas de moi, je doit juste l'adapter, de plus je ne suis pas forcement au point avec le PL/SQL, donc toute ces notions de mauvaises habitudes, sont très floue pour moi

**brassouille** · 09/01/2009, 16h12

Envoyé par mnitu

C’est un peu la totale des mauvais habitudes

Update dans une boucle
Commit dans une boucle
When NO_DATA_FOUND après un update – ça sert à quoi ?
When Others Then NULL
When Others Then Raise_Application_Error avec affichage SQLERRM etc.

Respirez un peu et
a) essayez d’effectuer la mise à jour en statique en prennent une des tables et en utilisant un seul update
b) une fois que vous y arrivez passer le nom de la table en dynamique sera un jeu d’enfant

J'avoue que je n'ai même pas lu ni cherché à comprendre le code, j'ai seulement répondu à la question sur le execute immediate

**brassouille** · 12/01/2009, 14h28

Tu y arrives avec ce que je t'ai écrit??

**lu6fer** · 12/01/2009, 15h57

pour le moment, a cause d'un manque de temps, je laisse ca de coté, et je créer 2 procédures identique, en changent le nom de la table sur laquelle elle s'applique.

c'est pas super, mais ça va plus vite.

Passage nom de table en paramètre à une procédure

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager