Discussion :

[sam3473]

Bonjour,
Tout d’abord meilleurs vœux.
J’ai besoin de votre aide :
J’ai une base de données Oracle et j’essaye de mettre en place une procédure stockée (utilisant 2 tables) qui consiste à exporter le résultat d’une requête SQL sous forme de plusieurs fichiers xml. Quand je l’exécute j’ai le message d’erreur :

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à "WM.EXPORT_TO_XML", ligne 68
ORA-06512: à ligne 2

La ligne 2 : file_handle UTL_FILE.FILE_TYPE;
La ligne 68 : UTL_FILE.PUT_LINE(file_handle, xml_result);


Merci beaucoup pour votre aide.

Cordialement.

[escartefigue]

Bonjour,

Les messages d'erreur, c'est bien, mais sans le script de la PS ni le script de création des tables...

[vanagreg]

Bonjour,

Il nous faudrait le code qui cause l'erreur.

[sam3473]

Rebonjour et merci pour votre message.

le script de la PS est :

Code SQL :

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
create or replace PROCEDURE EXPORT_TO_XML AS 
    file_handle UTL_FILE.FILE_TYPE;
    val_col1 HISTO_TRACA_INTERVENTION_E.ID_INTERVENTION%TYPE;
    val_col2 HISTO_TRACA_INTERVENTION_E.ID_IPP%TYPE;
    val_col3 HISTO_TRACA_INTERVENTION_E.ID_SEJOUR%TYPE;    
 
    xml_result CLOB;
BEGIN
    FOR data IN (
        SELECT
            histo_traca_intervention_e.id_intervention,
            histo_traca_intervention_e.id_ipp,
            histo_traca_intervention_e.id_sejour,
            histo_traca_intervention_e.id_histo AS id_histo1,
            histo_traca_intervention_d.id_traca AS id_traca1,
            histo_traca_intervention_d.id_ref_tracee AS id_ref_tracee1,
            histo_traca_intervention_d.id_lot AS id_lot1,
            histo_traca_intervention_d.dluo AS dluo1
        FROM
            histo_traca_intervention_e
        INNER JOIN histo_traca_intervention_d ON histo_traca_intervention_e.id_intervention = histo_traca_intervention_d.id_intervention
        WHERE 
            histo_traca_intervention_d.ID_HISTO = histo_traca_intervention_e.ID_HISTO
            AND ID_IPP=lpad(ID_IPP,9,'0')
            and histo_traca_intervention_e.date_cloture >= TO_DATE('2022-12-15', 'YYYY-MM-DD')
            AND statut_historique <> 'INACTIF'
    ) LOOP
        val_col1 := data.ID_INTERVENTION;
        val_col2 := data.ID_IPP;
        val_col3 := data.ID_SEJOUR;
 
        SELECT XMLElement("Root",
            XMLAgg(
                XMLElement("data",
                    XMLForest(
                        histo_traca_intervention_e.id_intervention AS "HISTO_TRACA_INTERVENTION_E.ID_INTERVENTION",
                        ID_IPP AS "HISTO_TRACA_INTERVENTION_E.ID_IPP",
                        ID_SEJOUR AS "HISTO_TRACA_INTERVENTION_E.ID_SEJOUR",
                        histo_traca_intervention_e.id_histo AS "id_histo1",
                        histo_traca_intervention_d.id_traca AS "id_traca1",
                        histo_traca_intervention_d.id_ref_tracee AS "id_ref_tracee1",
                        histo_traca_intervention_d.id_lot AS "id_lot1",
                        histo_traca_intervention_d.dluo AS "dluo1"
                    )
                )
            )
        ).getClobVal()
        INTO xml_result
        FROM HISTO_TRACA_INTERVENTION_E,histo_traca_intervention_d
 
        WHERE 
 
             HISTO_TRACA_INTERVENTION_E.ID_INTERVENTION = val_col1 
            AND HISTO_TRACA_INTERVENTION_E.ID_IPP = val_col2 
            AND HISTO_TRACA_INTERVENTION_E.ID_SEJOUR = val_col3;
 
        file_handle := UTL_FILE.FOPEN('IMPORT', val_col1 || '_' || val_col2 || '_' || val_col3 || '.xml', 'W');
        UTL_FILE.PUT_LINE(file_handle, xml_result);
        UTL_FILE.FCLOSE(file_handle);
    END LOOP;
END EXPORT_TO_XML;

===> Messages d'erreur :

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à "WM.EXPORT_TO_XML", ligne 58
ORA-06512: à ligne 2


Cordialement.

[vanagreg]

La procédure UTL_FILE.PUT_LINE a un buffer max de 32767, donc si ton CLOB dépasse cette limite tu vas avoir une erreur. Il faudra que tu découpes en plusieurs morceaux le fichier pour les concaténer.
Voici un exemple
https://oracle-base.com/articles/8i/export-clob

[sam3473]

Merci beaucoup pour votre réponse rapide.

J'ai suivi votre conseil et votre lien et j'ai essayé de découper en plusieurs morceaux puis de les concaténer mais j'ai des difficultés :

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
CREATE OR REPLACE PROCEDURE EXPORT_TO_XML_3 AS 
    file_handle UTL_FILE.FILE_TYPE;
    val_col1 HISTO_TRACA_INTERVENTION_E.ID_INTERVENTION%TYPE;
    val_col2 HISTO_TRACA_INTERVENTION_E.ID_IPP%TYPE;
    val_col3 HISTO_TRACA_INTERVENTION_E.ID_SEJOUR%TYPE;
 
    xml_result CLOB;
    chunk_size INTEGER := 30000; -- Taille de chaque segment à écrire dans le fichier
    offset INTEGER := 1;
BEGIN
    FOR data IN (
   SELECT
            histo_traca_intervention_e.id_intervention,
            histo_traca_intervention_e.id_ipp,
            histo_traca_intervention_e.id_sejour,
            histo_traca_intervention_d.id_traca
        FROM
            histo_traca_intervention_e
        INNER JOIN histo_traca_intervention_d ON histo_traca_intervention_e.id_intervention = histo_traca_intervention_d.id_intervention
        WHERE 
            histo_traca_intervention_d.ID_HISTO = histo_traca_intervention_e.ID_HISTO
            AND ID_IPP = LPAD(ID_IPP, 9, '0')
            AND date_cloture >= TRUNC(SYSDATE - 1) 
            AND statut_historique <> 'INACTIF'
    ) LOOP
        val_col1 := data.ID_INTERVENTION;
        val_col2 := data.ID_IPP;
        val_col3 := data.ID_SEJOUR;
 
        xml_result := 'XML_CLOB';
 
        file_handle := UTL_FILE.FOPEN('IMPORT', val_col1 || '_' || val_col2 || '_' || val_col3 || '.xml', 'W');
 
        LOOP
            EXIT WHEN offset > DBMS_LOB.GETLENGTH(xml_result);
 
            -- Écriture du prochain segment dans le fichier
            UTL_FILE.PUT(file_handle, DBMS_LOB.SUBSTR(xml_result, chunk_size, offset));
            offset := offset + chunk_size;
        END LOOP;
 
        UTL_FILE.FCLOSE(file_handle);
    END LOOP;
END EXPORT_TO_XML_3;

Merci pour votre aide.

Cordialement.

[Waldar]

La dernière fois que j'ai pratiqué du XML en PL/SQL c'était il y a plus d'une dizaine d'années, mais j'étais arrivé à la conclusion que tout mettre dans un CLOB et l'écrire en une fois était plus performant que de l'écrire bout par bout.
Je concaténais tout dans un CLOB :

• DBMS_LOB.CREATETEMPORARY pour la création.
• DBMS_LOB.WRITEAPPEND / DBMS_LOB.APPEND pour écrire dedans (le premier pour des varchar, le second pour un clob).
• DBMS_XSLPROCESSOR.CLOB2FILE pour faire une seule écriture sur disque.
• DBMS_LOB.FREETEMPORARY pour rester propre.

Et vu qu'a priori votre requête retourne déjà un clob complet, peut-être que seul le CLOB2FILE est utile pour vous.