Discussion :

[Jinkas]

Bonjour,

Je voudrais savoir ce qui ne va pas dans le code ci-dessous:

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
 
 
DECLARE
 
    V_T_EXISTS  NUMBER :=0;
    I  NUMBER:=0;
 
    CURSOR CUR
    IS
        SELECT ...;
BEGIN
 
    SELECT COUNT(*) INTO V_T_EXISTS FROM ALL_TABLES WHERE TABLE_NAME='TEST';
 
    IF V_T_EXISTS = 1 THEN
 
        FOR REC IN CUR LOOP
            I := I + 1;
            BEGIN
                UPDATE TEST
                   SET CH1 = REC.CH1
                 WHERE CH2 = REC.CH2;
            EXCEPTION
                WHEN OTHERS THEN
                    NULL;
            END;
        END LOOP;
    ELSE
 
        EXECUTE IMMEDIATE 'CREATE TABLE TEST AS
                            SELECT ....';
        EXECUTE IMMEDIATE 'CREATE INDEX INDEX_TEST ON TEST (...)';
 
    END IF;
 
    COMMIT;
 
END;

J'ai le message d'erreur suivant:

Code :

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

1
2
 
PL/SQL: ORA-00942: table or view does not exist

Merci,

[xdescamp]

Si la table TEST n'existe pas, le bloc PL/SQL ne compilera pas à cause de l'update sur cette table.
Il faut cacher cet update dans un EXECUTE IMMEDIATE pour que cela fonctionne :

Code :

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

1
2
3
...
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = :b1 WHERE CH2 = :b2' USING IN REC.CH1, REC.CH2;
...

[Jinkas]

Le curseur fait aussi référence à la table test

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
DECLARE
 
    V_T_EXISTS  NUMBER :=0;
    I  NUMBER:=0;
 
    CURSOR CUR
    IS
        SELECT ...
          FROM TEST T, ....
        WHERE ...;
BEGIN
 
    SELECT COUNT(*) INTO V_T_EXISTS FROM ALL_TABLES WHERE TABLE_NAME='TEST';
 
    IF V_T_EXISTS = 1 THEN
 
        FOR REC IN CUR LOOP
            I := I + 1;
            BEGIN
               EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = :b1 WHERE CH2 = :b2' USING IN REC.CH1, REC.CH2;
            EXCEPTION
                WHEN OTHERS THEN
                    NULL;
            END;
        END LOOP;
    ELSE
 
        EXECUTE IMMEDIATE 'CREATE TABLE TEST AS
                            SELECT ....';
        EXECUTE IMMEDIATE 'CREATE INDEX INDEX_TEST ON TEST (...)';
 
    END IF;
 
    COMMIT;
 
END;

d'où le même message d'erreur:

Code :

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

PL/SQL: ORA-00942: TABLE OR VIEW does NOT exist

Est ce que l'update peut se faire de de la façon suivante:

Code :

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

  EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 =' || REC.CH1 || ' WHERE CH2 = '  || REC.CH2 ||';

sans le using ?

[xdescamp]

Le curseur fait aussi référence à la table test

Au temps pour moi, je n'avais pas vu.
Il faudrait :
- déclarer un tableau (TABLE OF...)
- utiliser un EXECUTE IMMEDIATE 'SELECT...' BULK COLLECT INTO... pour alimenter le tableau
- puis boucler sur le tableau.

Est ce que l'update peut se faire de de la façon suivante:

Code :

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

  EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 =' || REC.CH1 || ' WHERE CH2 = '  || REC.CH2 ||';

sans le using ?

Oui, mais ça sera beaucoup moins performant : l'optimiseur ne verra pas que c'est toujours la même requête; il refera donc le parsing à chaque fois, ce qui finira par remplir la mémoire s'il y a trop de volume. Autant utiliser le USING.

[Jinkas]

- Je n'ai pas bien compris les variables

Code :

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

:b1 WHERE CH2 = :b2'

dans le EXECUTE IMMEDIATE de l'update

- ni comment déclarer le tableau

- je ne sais pas non plus ou faire ce 'SELECT...' BULK COLLECT INTO... pour alimenter le tableau (à la place du curseur ??? ou bien supprimer le curseur et parcourir le tableau dans le begin)

peut être comme ça ?

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
declare
  type t_bc_a is table of bc.a%type;
  type t_bc_b is table of bc.b%type;
 
  l_bc_a t_bc_a;
  l_bc_b t_bc_b;
begin
  select a, b bulk collect into l_bc_a, l_bc_b from bc;
 
  for i in l_bc_a.first .. l_bc_a.last loop
    dbms_output.put_line(l_bc_a(i) || ', ' || l_bc_b(i));
  end loop;
 
end;
/

si oui, il faudrait simplement déclarer les variables qui sont récupérées dans le curseur ?

[xdescamp]

- Pour les variables, il s'agit de "bind variables". Cela permet de déclarer des variables dans l'instruction, puis de leur assigner des valeurs au moment de l'exécution. La syntaxe et des exemples : http://download.oracle.com/docs/cd/B...3_elems017.htm ou http://download.oracle.com/docs/cd/B...61/dynamic.htm.

- Pour le BULK COLLECT, il y a des exemples dans la doc Oracle : http://download.oracle.com/docs/cd/B...mic.htm#i18972. Le 7-4 est un exemple basique; le reste est intéressant à lire également.

[Jinkas]

Est ce que cela vous semble correcte ?

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
DECLARE
 
    V_TABLE_EXISTS  NUMBER :=0;
    G_COUNTER  NUMBER:=0;
 
    type t_b IS TABLE of test.b%type;
    l_b t_b;
 
    CURSOR CUR
    IS
        SELECT * FROM test;
 
BEGIN
 
    SELECT COUNT(*) INTO V_TABLE_EXISTS FROM ALL_TABLES WHERE TABLE_NAME='TEST';
 
    IF V_TABLE_EXISTS = 1 THEN
        -- OUVERTURE DU TAMPON --
        DBMS_OUTPUT.ENABLE(1000000) ;
        DBMS_OUTPUT.PUT_LINE(V_TABLE_EXISTS || ' - CHECK -> : TABLE ALREADY EXISTS ');
 
        SELECT b bulk collect INTO l_b FROM test;
 
        FOR i IN l_b.first .. l_b.last loop
 
            BEGIN
                --EXECUTE IMMEDIATE 'UPDATE TEST SET a = :a1' USING IN REC.a;
                dbms_output.put_line(l_b(i));
 
            EXCEPTION
                WHEN OTHERS THEN
                    NULL;
            END;
        END LOOP;
    ELSE
 
        DBMS_OUTPUT.PUT_LINE(V_TABLE_EXISTS || ' - CHECK -> : TABLE DOESN''T EXIST');
 
        EXECUTE IMMEDIATE 'create table test (
  a number,
  b varchar2(10)
)';
        EXECUTE IMMEDIATE 'CREATE INDEX index_test ON test
                            (
                                b ASC
                            )';
    END IF;
 
    COMMIT;
 
END;

Merci,

[xdescamp]

Non car il reste des références à la table TEST pour la déclaration du type et du curseur.
Quelque chose comme ça :

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
DECLARE
 
    V_TABLE_EXISTS  NUMBER :=0;
    G_COUNTER  NUMBER:=0;
 
    type t_rec is record (a number, b varchar2(10)); -- Mettre ici la structure d'un enregistrement de la table
    type t_b IS TABLE of t_rec;
    l_b t_b;
 
 
BEGIN
 
    SELECT COUNT(*) INTO V_TABLE_EXISTS FROM ALL_TABLES WHERE TABLE_NAME='TEST';
 
    IF V_TABLE_EXISTS = 1 THEN
        -- OUVERTURE DU TAMPON --
        DBMS_OUTPUT.ENABLE(1000000) ;
        DBMS_OUTPUT.PUT_LINE(V_TABLE_EXISTS || ' - CHECK -> : TABLE ALREADY EXISTS ');
 
        EXECUTE IMMEDIATE 'select a, b from test' BULK COLLECT INTO l_b;
 
        IF l_b.COUNT > 0 THEN
            FOR i IN l_b.first .. l_b.last loop
 
                BEGIN
                    EXECUTE IMMEDIATE 'UPDATE TEST SET a = :a1' USING IN l_b(i).a;
                    dbms_output.put_line(l_b(i).a);
                    dbms_output.put_line(l_b(i).b);
 
                EXCEPTION
                    WHEN OTHERS THEN
                        NULL;
                END;
            END LOOP;
        END IF;
    ELSE
 
        DBMS_OUTPUT.PUT_LINE(V_TABLE_EXISTS || ' - CHECK -> : TABLE DOESN''T EXIST');
 
        EXECUTE IMMEDIATE 'create table test (
  a number,
  b varchar2(10)
)';
        EXECUTE IMMEDIATE 'CREATE INDEX index_test ON test
                            (
                                b ASC
                            )';
    END IF;
 
    COMMIT;
 
END;

Mais je n'ai pas testé...

[Jinkas]

Donc,

si je comprends bien ce que vous dites
que là

Code :

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

type t_rec IS record (a number, b varchar2(10)); -- Mettre ici la structure d'un enregistrement de la table

on déclare les variables qu'on manipule dans le select soit par exemple:

Code :

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

select ch1, ch2, ch3 from test where ....

on aura une structure comme celle là:

Code :

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

type t_rec IS record (v_ch1 number, v_ch2 varchar2(10), v_ch3 ...);

?

[xdescamp]

C'est ça, l'idée est là.

[Jinkas]

Ok merci je vais faire mes tests et je vous tiens au courant!

[Jinkas]

Je ne vois pas comment mettre en place l'update dans l'exemple suivant
en utilisant le using in

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:v_ch1,....),
                                   CH2 = :v_ch2,
                                   CH3 = :v_ch3
                         WHERE CH4 = :v_ch4'
 
                         USING IN 
                                    L_B(i).CH1,
                                    L_B(i).CH2,
                                    L_B(i).CH3,
                                    L_B(i).CH4;

Merci,

[xdescamp]

Juste comme vous l'avez écrit là, ça doit fonctionner.
On passe les valeurs avec USING autant de fois et dans le même ordre qu'il y a besoin de "bind variable".

[Jinkas]

Lorsque j'exécute le script, j'ai ce message suivant:

Code :

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

1
2
 
PLS-00302: component 'CH1' must be declared ORA-06550

DECLARE

Code :

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

1
2
3
4
    TYPE T_REC IS RECORD (V_CH1 TEST.CH1%TYPE, 
                          V_CH2 TEST.CH2%TYPE, 
                          V_CH4 TEST.CH4%TYPE, 
                          V_CH5 TEST.CH5%TYPE);

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:V_CH1,...),
                                   CH2 = :V_CH2,
                                   CH3 = :V_CH1,
                                   CH4 = :V_CH4,
                         WHERE CH5 = :V_CH5'
                               
                         USING IN 
                                    L_B(i).CH1,
                                    L_B(i).CH2,
                                    L_B(i).CH4,
                                    L_B(i).CH5;

[xdescamp]

Lorsque j'exécute le script, j'ai ce message suivant:

Code :

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

1
2
 
PLS-00302: component 'CH1' must be declared ORA-06550

DECLARE

Code :

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

1
2
3
4
    TYPE T_REC IS RECORD (V_CH1 TEST.CH1%TYPE, 
                          V_CH2 TEST.CH2%TYPE, 
                          V_CH4 TEST.CH4%TYPE, 
                          V_CH5 TEST.CH5%TYPE);

=> Ne pas faire référence à TEST, elle n'existe pas. Il faut mettre VARCHAR2(...), NUMBER, etc...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:V_CH1,...),
                                   CH2 = :V_CH2,
                                   CH3 = :V_CH1,
                                   CH4 = :V_CH4,
                         WHERE CH5 = :V_CH5'
                               
                         USING IN 
                                    L_B(i).V_CH1,
                                    L_B(i).V_CH2,
                                    L_B(i).V_CH4,
                                    L_B(i).V_CH5;

=> Il faut mettre les noms des composants du type que vous venez de définir.

[Jinkas]

Désolé je me suis trompé, les variables que je déclare ne viennent pas de la table test mais d'autres tables de la base:

Code :

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

1
2
3
4
 TYPE T_REC IS RECORD (V_T1 TABLE1.T1%TYPE, 
V_T2 TABLE2.T2%TYPE, 
V_T3 TABLE3.T3%TYPE, 
V_T4 TABLE4.T4%TYPE);

et je fais l'update dans la table test

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:V_T3,...),
CH2 = :V_T2,
CH3 = :V_T3,
CH4 = :V_T4,
WHERE CH5 = :V_T1'
 
USING IN 
L_B(i).V_CH3,
L_B(i).V_CH2,
L_B(i).V_CH4,
L_B(i).V_CH1;

et j'ai le message suivant:

Code :

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

 PLS-00302: component 'CH2' must be declared

[Jinkas]

Vu que dans l'update, je mets à jour 4 champs en fonction d'un 5ème champs.
Le 1er champs et le 3ème champs utilisent la valeur d'une même variable qui est déclarée dans le RECORD

Comment devrais-je appeler mes variables dans le USING IN? Dans quel ordre?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:V_CH3,...),
                                   CH2 = :V_CH2,
                                   CH3 = :V_CH3,
                                   CH4 = :V_CH4,
                         WHERE CH5 = :V_CH1'
 
                         USING IN 
                                    L_B(i).V_CH1,
                                    L_B(i).V_CH2,
                                    L_B(i).V_CH4,
                                    L_B(i).V_CH5;

Je dirai que l'ordre c'est:

Code :

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

1
2
3
4
5
                         USING IN 
                                    L_B(i).V_CH3,
                                    L_B(i).V_CH2,
                                    L_B(i).V_CH4,
                                    L_B(i).V_CH1;

?

[xdescamp]

Comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
EXECUTE IMMEDIATE 'UPDATE TEST SET CH1 = DECODE (:V_CH3,...),
                                   CH2 = :V_CH2,
                                   CH3 = :V_CH3,
                                   CH4 = :V_CH4,
                         WHERE CH5 = :V_CH1'
 
                         USING IN 
                                    L_B(i).V_CH3,
                                    L_B(i).V_CH2,
                                    L_B(i).V_CH3,
                                    L_B(i).V_CH4,
                                    L_B(i).V_CH1;

Exactement l'ordre des bind variables dans la requête.

[Jinkas]

Merci beaucoup pour votre aide ça m'a servi énormément et j'ai appris de nouvelles fonctionnalités. Cependant, je voudrais pouvoir mettre en place un trigger sur la table TEST au niveau

Code :

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

IF V_TABLE_EXISTS = 1 THEN

Dois-je le faire aussi avec un execute immediate ?

[xdescamp]

Oui : on ne peut pas créer ou modifier d'objet dans la base (table, package, vue, trigger, etc...) en PL/SQL. La solution est effectivement de "cacher" cette instruction dans un EXECUTE IMMEDIATE.