Création d'une table avec curseur

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

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

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
 
 
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:

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

Merci,

11/10/2010, 11h13
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:

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

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

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
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:

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:

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

sans le using ?

11/10/2010, 12h07
xdescamp

Citation:

Envoyé par Jinkas

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.

Citation:

Envoyé par Jinkas

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

Code:

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.

- Je n'ai pas bien compris les variables

Code:

: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):cry:

peut être comme ça ?
Code:

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 ?

11/10/2010, 13h38
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.

Est ce que cela vous semble correcte ?

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
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,

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:

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é...:roll:

11/10/2010, 16h16
Jinkas

Donc,

si je comprends bien ce que vous dites
que là

Code:

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:

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

on aura une structure comme celle là:

Code:

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

?
11/10/2010, 16h18
xdescamp

C'est ça, l'idée est là.
11/10/2010, 16h19
Jinkas

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

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

Code:

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,

11/10/2010, 17h04
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".

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

Code:

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

DECLARE

Code:

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:

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;

Citation:

Envoyé par Jinkas

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

Code:

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

DECLARE

Code:

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:

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.

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:

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:

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:

PLS-00302: component 'CH2' must be declared

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:

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:

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;

Comme ceci :

Code:

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.

12/10/2010, 11h02
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:

IF V_TABLE_EXISTS = 1 THEN

Dois-je le faire aussi avec un execute immediate ?
12/10/2010, 11h35
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.

Voir 40 message(s) de cette discussion en une page