Discussion :

[donny]

Bonjour,

Je souhaite automatisé l'alimentation de plusieurs tables.
J'ai créer un procédure qui boucle et alimente chaque tables (60 tables) une après l'autre.
Sachant que certaine table dispose de volumétrie conséquente, je voulais modifier mon code pour essayer de le faire en bulk collect.
J'ai récupéré un exemple de code,
mais je ne comprends pas certaine chose.Dans mon cas, le nom des tables est passé en paramètre (le nom, le nombre de colonne....changent) donc
je n'ai pas la possibilité de faire ce genre de déclaration :
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;

Existe-t-il un type générique compatible pour toutes les tables ?

Mon code :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CURSOR c_table_l IS SELECT * FROM conf_usi;
table_l c_table_l%ROWTYPE;
 
BEGIN
 
FOR table_l IN c_table_l LOOP
 
EXECUTE IMMEDIATE 'INSERT INTO ' || table_l.usi_tab ||' SELECT * FROM '|| table_l.usi_mac ||'';
COMMIT;
 
END LOOP;

L'exemple de Bulk :

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
 
CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
 
CURSOR c IS SELECT * FROM all_objects;
 
BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
 
    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);
 
    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/

Est-ce vraiment possible de faire du bulk collect avec le sql dynamique ?

Merci

[skuatamad]

Pas besoin de faire du BULK COLLECT FORALL si on peut faire un INSERT INTO SELECT.
Et pas besoin de déclarer de variable %ROWTYPE pour faire du INSERT INTO SELECT.

Techniquement il possible de ne pas préciser la liste des colonnes et d'utiliser *, mais comme ça n'est pas très propre il est possible de générer la liste des colonnes :

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
SQL> declare
  2    l_query long;
  3  begin
  4  for c in (select table_name, listagg(column_name, ',') within group (order by column_id) as list_column
  5              from all_tab_columns
  6             where owner = 'SCOTT'
  7               and table_name in ('EMP', 'DEPT')
  8             group by table_name) loop
  9        l_query := 'insert into ' || c.table_name || ' ( ' || c.list_column || ') '||chr(10);
 10        l_query := l_query || 'select ' || c.list_column || ' from scott.'||c.table_name;
 11        dbms_output.put_line (l_query);
 12        execute immediate l_query;
 13  end loop;
 14  end;
 15  /
insert into DEPT ( DEPTNO,DNAME,LOC)
select DEPTNO,DNAME,LOC from scott.DEPT
insert into EMP ( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from scott.EMP
 
PL/SQL procedure successfully completed.
 
SQL>

Après comme c'est du sql dynamique ça n'apporte vraiment pas grand chose de générer la liste des colonnes.

Par contre je ne suis pas fan de l'approche : je modifie mon sql statique pour faire du sql dynamique plus ou moins générique.

PS pour du 10g regardez String Aggregation Techniques pour remplacer listagg

[donny]

Merci de ta réponse,
en faite je t'explique je voulais passer par un bulk car j'ai des tables à forte volumétrie et si jamais ma procédure échoue en cours d'insertion(table de 9 millions de lignes). Oracle va faire un rollback et je pense que ça va prendre énormement de temps, voir faire exploser le tablespace ou d'autres erreurs.

Je n'ai pas compris ce que tu entendais par ça :

Par contre je ne suis pas fan de l'approche : je modifie mon sql statique pour faire du sql dynamique plus ou moins générique.