Bonjour à tous,


Dans le cadre d'un développement je dois extraire depuis une base de données des fichiers XML, afin de les envoyer à des tiers.

Pour générer le XML, j'ai codé ce qu'il faut dans une vue en passant pas des objets créé pour le besoin : pas de soucis de ce côté là, ça répond plutôt bien.

L'envoi des fichiers est piloté par une table tierce qui contient les méta-données.

Là où je ne suis pas satisfait de mon process, c'est sur l'extraction de ces XML vers les fichiers.

Certaines limitations me sont imposées :
  • dans ma table d'envoi figure un code et je dois déclarer un fichier par code.
  • un fichier ne doit pas dépasser 10.000 objets.


Mon environnement est Oracle Database Enterprise Edition 11gR1 11.1.0.7, 64 bits qui opère sur Windows Server 2003.

J'ai créé un jeu de test relativement simple pour illustrer tout ceci :
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
create directory TST as 'c:\TEMP\TST';
 
create table donnees
(
    col1      number(1)         not null,
    col2      varchar2(10 char) not null,
    constraint pk_donnees
      primary key (col1)
      using index
);
 
insert into donnees (col1, col2) values (1, 'ABC');
insert into donnees (col1, col2) values (2, 'DEF');
insert into donnees (col1, col2) values (3, 'GHI');
insert into donnees (col1, col2) values (4, 'JKL');
insert into donnees (col1, col2) values (5, 'MNO');
insert into donnees (col1, col2) values (6, 'PQR');
insert into donnees (col1, col2) values (7, 'STU');
insert into donnees (col1, col2) values (8, 'VWX');
insert into donnees (col1, col2) values (9, 'YZ' );
 
commit;
 
create view v_donnees_xml
as
select col1, XMLElement("SuperBalise", col2) as col_XML
  from donnees;
 
create table envoi
(
    col1      number(1)         not null,
    code      varchar2(1 char)  not null,
    chk       varchar2(1 char)      null,
    constraint pk_envoi
      primary key (col1)
      using index
);
 
insert into envoi (col1, code, chk) values (1, 'A', 'X');
insert into envoi (col1, code, chk) values (2, 'A', 'X');
insert into envoi (col1, code, chk) values (3, 'A', 'X');
insert into envoi (col1, code, chk) values (4, 'A', 'X');
insert into envoi (col1, code, chk) values (5, 'A', 'X');
insert into envoi (col1, code, chk) values (6, 'B', 'X');
insert into envoi (col1, code, chk) values (7, 'B', 'X');
insert into envoi (col1, code, chk) values (8, 'B', 'X');
insert into envoi (col1, code, chk) values (9, 'C', 'X');
 
commit;
D'après ce que j'ai lu ici (probablement un lien posté par mnitu, comme souvent ), le plus rapide sans parallélisation pour exporter des données dans un fichier plat sont de passer par un CLOB et de ne l'écrire qu'une fois.

Dans le code PL/SQL qui va suivre, ce qui me déplaît c'est que j'ai créé un premier curseur pour parcourir les éléments à envoyer, et à partir des données de celui-ci je vais chercher le bout de XML qui me convient.
Avec ma volumétrie, effectuer la jointure à ce niveau en pur SQL est malheureusement trop coûteux, j'annule le teste après plus de trente minutes sans rien voir arriver.

Dans ce code j'ai simulé la limitation à trois objets par fichier :
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
declare
    v_file                  CLOB;
    v_clob                  CLOB;
    v_date_extract          VARCHAR2(30);
    v_nb_rows               NUMBER(10) DEFAULT 0;
    v_file_name             VARCHAR2(50);
    v_file_dir     CONSTANT VARCHAR2( 3) DEFAULT 'TST';
    v_header                VARCHAR2(100);
    v_footer                VARCHAR2(100);
 
begin
 
    for c_query in
    (
    select col1, code,
           lag(code, 1, ' ') over(order by code asc) as code_prec
      from envoi
     where chk = 'X'
    )
    loop
 
      if (c_query.code <> c_query.code_prec or v_nb_rows = 3)
      then -- Nouveau fichier 
 
        if c_query.code_prec <> ' '
        then -- Écriture du fichier précédent
          v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
          -- Rajout du footer
          DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
          DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
          DBMS_LOB.FREETEMPORARY(v_file);
        end if;
 
        -- Initialisation du CLOB 
        DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
        v_nb_rows      := 0;
        v_date_extract := to_char(systimestamp, 'yyyymmdd_hh24missff');
        v_file_name    := 'Fichier_' || c_query.code || '_' || v_date_extract || '.xml';
 
        v_header       := '<MonFichier>' || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<MonCode>'   || c_query.code   || '</MonCode>'   || chr(13) || chr(10) ||
                          lpad(' ', 4, ' ') || '<DateEnvoi>' || v_date_extract || '</DateEnvoi>' || chr(13) || chr(10);
 
        -- Rajout du header
        DBMS_LOB.WRITEAPPEND(v_file, length(v_header), v_header);
 
      end if;
 
      -- Sélection du XML dans la vue 
      select lpad(' ', 8, ' ') || x.col_xml.getClobVal() || chr(13) || chr(10) into v_clob
        from v_donnees_xml x
       where x.col1 = c_query.col1;
 
      -- Concaténation du XML dans le CLOB
      DBMS_LOB.APPEND(v_file, v_clob);
 
      v_nb_rows := v_nb_rows + 1;
 
    end loop;
 
    -- Écriture du dernier fichier
    v_footer := lpad(' ', 4, ' ') || '<NbItem>' || to_char(v_nb_rows) || '</NbItem>' || chr(13) || chr(10) || '</MonFichier>';
    -- Rajout du footer
    DBMS_LOB.WRITEAPPEND(v_file, length(v_footer), v_footer);
    DBMS_XSLPROCESSOR.CLOB2FILE(v_file, v_file_dir, v_file_name);
    DBMS_LOB.FREETEMPORARY(v_file);
 
end;
/
Évidemment sur ce genre de jeu de test, tout est instantané.

Voici le contenu d'un fichier, qui est bien celui escompté :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
<MonFichier>
    <MonCode>A</MonCode>
    <DateEnvoi>20110329_151939417000000</DateEnvoi>
        <SuperBalise>JKL</SuperBalise>
        <SuperBalise>MNO</SuperBalise>
    <NbItem>2</NbItem>
</MonFichier>
Sur mon environnement de développement, j'écris environ 1Go de données en quinze minutes.
Ce n'est pas tellement l'écriture qui est lente, c'est vraiment d'aller chercher les bouts de XML un par un.

C'est sur ce point qu'à mon avis le code peut être améliorer, je suis quasiment sûr que ce temps de traitement est divisible par deux ou trois.
J'ai imaginé stocker tous mes identifiants dans une collection et la passer d'un coup à la vue, avec du XMLAgg afin de ne faire plus qu'un seul appel à ce niveau, mais je n'ai pas réussi à l'implémenter.

Ayant relativement peu de pratique en pur PL/SQL, toutes expériences et idées sont les bienvenues !

Merci d'être parvenu au terme de ce long sujet.

PS : Pour nettoyer votre environnement :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
drop view v_donnees_xml;
 
drop table envoi;
 
drop table donnees;
 
drop directory TST;