Discussion :

[Hurricae]

Bonjour,

J'ai un soucis pour extraire un CLOB dans un fichier texte. Ce clob contient un flux XML (pas de possibilité de modifier le type de la colonne, cela doit rester un CLOB et pas de BLOB). J'ai essayé mult bout de code trouvé sur internet. J'arrive a exporter un clob d'une taille inférieure à 32K mais dès que cette taille est dépassé, mon script tombe KO. Je pense au buffer qui ne doit pas dépassé 32767 mais je bloque.

Auriez vous une solution pour parcourir ce CLOB > 32K pour le mettre dans ce fichier texte. Ce CLOB doit être contenu sur une ligne est pas deux.

Pour information, je ne peux pas utiliser DBMS_XSLPROCESSOR.

Merci d'avance

Damien

[Waldar]

Qu'entendez-vous par cette phrase :

Ce CLOB doit être contenu sur une ligne est pas deux.

Sur un besoin similaire (export d'un XML), j'utilise quelque chose comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
declare
    v$_sel_clob    CLOB;
 
begin
    select XMLElement("Balise", 'Contenu').getClobVal() into v$_sel_clob
      from dual;
 
    DBMS_XSLPROCESSOR.CLOB2FILE(v$_sel_clob, 'TST', 'Fichier_XML.xml');
end;
/

Testé avec succès à l'instant sur une 11gR1.

Pourquoi ne pouvez-vous pas utiliser DBMS_XSLPROCESSOR ?

[mnitu]

Export CLOB Contents

[Hurricae]

je ne peux pas utiliser DBMS_XSLPROCESSOR car il n'est pas installé sur la base (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)


L'exemple d'export CLOB contents ne fonctionne pas non plus, j'obtiens :
ERREUR à la ligne 1 :
ORA-29285: erreur d'écriture sur le fichier
ORA-06512: à "SYS.UTL_FILE", ligne 77
ORA-06512: à "SYS.UTL_FILE", ligne 690
ORA-06512: à ligne 21
ORA-01403: aucune donnée trouvée

Je précise que j'appel mon script via sqlplus.

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
whenever sqlerror exit 1
whenever oserror exit 1
SET SERVEROUTPUT ON
SET HEAD OFF
SET ECHO ON
SET FEED 0
SET VERIFY OFF
SET SERVEROUTPUT ON
 
DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
 
SELECT LIGFOR INTO l_clob FROM EXTMVTACT WHERE IDNACT = 'ACTE_10010000123';
 
  l_file := UTL_FILE.fopen('DMPDIR', 'Sample2.txt', 'w', 32767);
 
  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    l_pos := l_pos + l_amount;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Expected end.
    UTL_FILE.fclose(l_file);
  WHEN OTHERS THEN
    UTL_FILE.fclose(l_file);
    RAISE;
END;
/
EXIT 0;

Pour info, 'DMPDIR' existe bien et ce script fonctionne pour un clob plus petit que 32k

[mnitu]

Voilà

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> create table tab1 (
  2    col1 clob
  3  )
  4  /
 
Table created
SQL> Declare
  2    lob_loc   Clob;
  3  Begin
  4    insert into tab1(col1)
  5    values (empty_clob)
  6    returning col1 into lob_loc;
  7    --
  8    dbms_lob.write(lob_loc, 32767, 1, lpad('x',32767,'x'));
  9    dbms_lob.writeappend(lob_loc, 32767, lpad('y',32767,'y')) ;
 10    --
 11    commit;
 12  end;
 13  /
 
PL/SQL procedure successfully completed
SQL> Declare
  2    l_file    UTL_FILE.FILE_TYPE;
  3    l_clob    CLOB;
  4    l_buffer  Varchar2(32767);
  5    l_amount  BINARY_INTEGER := 32767;
  6    l_pos     INTEGER := 1;
  7  BEGIN
  8    SELECT col1
  9    INTO   l_clob
 10    FROM   tab1
 11    WHERE  rownum = 1;
 12  
 13    l_file := UTL_FILE.fopen('MRS_DOC', 'Sample2.txt', 'wb', 32767);
 14    LOOP
 15      DBMS_LOB.read( l_clob, l_amount, l_pos, l_buffer);
 16      UTL_FILE.put_raw(l_file, utl_raw.cast_to_raw(l_buffer));
 17      l_pos := l_pos + l_amount;
 18    END LOOP;
 19  EXCEPTION
 20    WHEN NO_DATA_FOUND THEN
 21      -- Expected end.
 22      UTL_FILE.fclose(l_file);
 23    WHEN OTHERS THEN
 24      UTL_FILE.fclose(l_file);
 25      RAISE;
 26  END;
 27  /
 
PL/SQL procedure successfully completed
 
SQL>

[Hurricae]

Merci beaucoup cela fonctionne! Vraiment sympa!

Maintenant je bloque sur un problème tout bête mais qui me bloque pas mal. Le script fonctionne pour un clob, mais je dois en inscrire plusieurs (un clob par ligne), et la je bloque.

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 
 
l_file    UTL_FILE.FILE_TYPE;
l_clob    CLOB;
l_buffer  Varchar2(32767);
l_amount  BINARY_INTEGER := 32767;
l_pos     INTEGER := 1;
leen   NUMBER;
 
CURSOR C1 IS
    select LIGFOR, IDNACT
    from EXTMVTACT WHERE DATFRAI <= sysdate;
 
begin
 
OPEN C1;
 
 
LOOP
    FETCH C1
      INTO l_clob;
    EXIT WHEN C1%NOTFOUND;
 
    l_file := UTL_FILE.fopen('DMPDIR', 'toto.txt', 'wb', 32767);
 
 
    leen := dbms_lob.getlength(l_clob);
    dbms_output.put_line('Column Length: ' || TO_CHAR(leen));
 
     LOOP
       DBMS_LOB.READ(l_clob, l_amount, l_pos, l_buffer);
       UTL_FILE.put_raw(l_file, utl_raw.cast_to_raw(l_buffer));
       l_pos := l_pos + l_amount;
        IF l_pos >= leen THEN
         GOTO sortie;
        END IF;
     END LOOP;
     <<sortie>>
     l_pos :=1;
     l_buffer := NULL;
END LOOP;
CLOSE C1;
 
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
       -- Expected end.
       UTL_FILE.fclose(l_file);
     WHEN OTHERS THEN
       UTL_FILE.fclose(l_file);
       RAISE;
end;

J'ai utilisé un curseur mais je ne suis pas sur de moi. De plus je ne sais pas si UTL_FILE va passer à la ligne automatiquement à la fin d'un CLOB

[McM]

Dans les scripts il y a un truc qui me gêne, c'est que jamais on ne ferme le fichier quand tout marche...
Ensuite, attention, le dbms_lob.read a le paramètre amount qui est en in out, et il me semble qu'il vaut 0 à la fin, donc dans le cas d'une boucle, il faut remettre la valeur par défaut du param.

Dernière chose, la boucle est correcte, mais il faut que ton fichier ne soit ouvert qu'au premier clob trouvé (ou alors tu l'ouvres avant).

Pour le retour à la ligne, il faut le gérer manuellement avec un put_char (ou peut être new_line, je ne sais pas je ne l'ai jamais utilisé)

Enfin, pas besoin de tester la longueur du retour.. dbms_lob.read sort en erreur NO_DATA_FOUND quand tout a été lu.. (donc il faut que tu testes cette exception à l'intérieur de la boucle).

Après.. faut tester pour apprendre

[mnitu]

Dans les scripts il y a un truc qui me gêne, c'est que jamais on ne ferme le fichier quand tout marche...
...

Quand tout marche il sort par le l'exception NO_DATA_FOUND
Mais, je suis d'accord avec vous: un code utilisé en production devrait être un peu plus soigné.

[mnitu]

...
Ensuite, attention, le dbms_lob.read a le paramètre amount qui est en in out, et il me semble qu'il vaut 0 à la fin, donc dans le cas d'une boucle, il faut remettre la valeur par défaut du param.
...

Non, il vaut le nombre des caractères lus.

[mnitu]

...
Pour le retour à la ligne, il faut le gérer manuellement avec un put_char (ou peut être new_line, je ne sais pas je ne l'ai jamais utilisé)
...

Plutôt put_raw et transmettre l(es)'octet(s) voulue(s) pour séparer les "enregistrements".

[Hurricae]

Alors voici mon script (plus propre je l'espère) mais qui ne fonctionne pas. J'ai l'erreur :
ORA 29283 : invalid file operation
ORA 06512: at SYS.UTL_FILE, line 536
ORA 29283 : invalid file operation
ORA 06512: at line 65

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
 
declare 
 
l_file    UTL_FILE.FILE_TYPE;
l_clob    CLOB;
l_buffer  Varchar2(32767);
l_amount  BINARY_INTEGER := 32767;
l_pos     INTEGER := 1;
l_idActe EXTMVTACT.Idnact%TYPE;
leen   NUMBER;
 
CURSOR C1 IS
    SELECT LIGFOR, IDNACT
    FROM EXTMVTACT WHERE DATFRAI <= sysdate;
 
begin
    -- Ouverture du fichier
    l_file := UTL_FILE.fopen('DMPDIR', 'toto.txt', 'wb', 32767);
    -- Ouverture du curseur 
    OPEN C1;
 
    LOOP
         FETCH C1
          INTO l_clob,l_idActe;
         EXIT WHEN C1%NOTFOUND;
 
              LOOP
                   begin
                   -- Lecture du clob 
                   DBMS_LOB.READ(l_clob, l_amount, l_pos, l_buffer);
                   UTL_FILE.put_raw(l_file, utl_raw.cast_to_raw(l_buffer));
                   l_pos := l_pos + l_amount;
 
                   EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                         -- Si fin du CLOB, ajout de la ligne vide et reset du buffeur
                         UTL_FILE.put_raw(l_file, '0D');
                         l_pos :=1;
                         l_buffer := 0;
                         WHEN OTHERS THEN
                         -- Si autre erreur(s), on sort de la boucle
                         GOTO sortie;
                   END; 
              END LOOP;
     -- Fin du CLOB
   END LOOP;
     <<sortie>>
     -- Fermeture du cuseur
     CLOSE C1;
     -- Fermeture du fichier si ouvert
     IF UTL_FILE.IS_OPEN(l_file) THEN
          UTL_FILE.FCLOSE(l_file);
       END IF;
 
EXCEPTION
     WHEN NO_DATA_FOUND THEN
       -- Si pas de CLOB trouvé
       IF UTL_FILE.IS_OPEN(l_file) THEN
          UTL_FILE.FCLOSE(l_file);
       END IF;
     WHEN OTHERS THEN
       -- Si autre erreur(s), fermeture du fichier si ouvert
       IF UTL_FILE.IS_OPEN(l_file) THEN
          UTL_FILE.FCLOSE(l_file);
       END IF;
       RAISE;
end;

[mnitu]

Faite une procédure qui devrait écrire le contenu d'un clob dans un fichier. Appelez cette procédure dans la boucle qui ramène les clobs.

Dans

Code :

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

1
2
 
UTL_FILE.put_raw(l_file, '0D');

'0D' n'est pas de type raw.
Je pense que vous devez utilisez quelque chose comme

Code :

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

1
2
 
utl_file.put_raw(l_file, utl_raw.cast_to_raw(chr(10))); -- 10 pour LF

[McM]

Le when-others pour le goto ne sert à rien.
L'affectation l_buffer :=0; est incompatible avec le varchar et ne sert à rien
La variable l_amount n'est pas réinitialisée lors d'un changement de clob
privilégie les for loop que les open cursor .. fetch, plus simple à coder.

Voici le code épuré (non 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
37
38
39
40
41
DECLARE 
  l_file    UTL_FILE.FILE_TYPE;
  l_buffer  Varchar2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
    -- Ouverture du fichier
    l_file := UTL_FILE.fopen('DMPDIR', 'toto.txt', 'wb', 32767);
 
    -- Ouverture du curseur 
    FOR r IN (SELECT LIGFOR FROM EXTMVTACT WHERE DATFRAI <= SYSDATE)
    LOOP
    		l_amount := 32767; l_pos :=1;
    		LOOP
           BEGIN
               -- Lecture du clob 
               DBMS_LOB.READ(r.LIGFOR, l_amount, l_pos, l_buffer);
               UTL_FILE.put_raw(l_file, UTL_RAW.cast_to_raw(l_buffer));
               l_pos := l_pos + l_amount;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
             -- Si fin du CLOB, ajout de la ligne vide
             UTL_FILE.put_raw(l_file, UTL_RAW.cast_to_raw(CHR(10)));
             EXIT; -- On sort de la boucle
          END; 
				END LOOP;
     -- Fin du CLOB
   END LOOP;
 
   -- Fermeture du fichier si ouvert
   IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
 
EXCEPTION
     WHEN NO_DATA_FOUND THEN
       -- Si pas de CLOB trouvé
       IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
     WHEN OTHERS THEN
       -- Si autre erreur(s), fermeture du fichier si ouvert
       IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
       RAISE;
END;

[Hurricae]

Merci beaucoup à vous, tout fonctionne parfaitement maintenant! Vous m'aviez super bien aidé!