Extraire un PDF encodé intégré dans un fichier XML

Version imprimable

Bonjour à tous,

Je voudrais extraire le contenu d'une balise XML qui est un fichier PDF encodé et intégré dans un fichier XML, l'extraction doit être faite avec du PL/SQL.
Exemple ci-dessous , le contenu du fichier PDF nommé FAC3477118220008523D000339.pdf est entre les balises <Contenu> et </Contenu>

Exemple:
   <PJ NumOrdre="1">
        <Contenu>UEsDBBQACAgIACU0fFYAAAAAAAAAAAAAAAAeAAAARkFDMzQ3NzExODIyMDAwODUyM0QwMDAzMzkucGRm3Lp1XNRb2zc6AgIiIQISUgJKKUOXyEgL0t2IMDQM3TAi3SANIiVdIt3d3S3d3TXMnMG97...</Contenu>
        <NomPJ>FAC3477118220008523D000339.pdf</NomPJ>
       <TypePJ>01</TypePJ>
        <MimeTypePJ xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="xs:string">application/pdf</MimeTypePJ>
    </PJ>
</CPPFacturePivotUnitaire>
</CPPFactures>
</CPPFacturePivot>
Je vous remercie.

02/04/2023, 11h50
JeitEmgie

Il y a un exemple sur le site d'ORACLE :

How to Encode / Decode a .pdf File Using Base64_Encode and Embed the File in an XML Document Using XMLELEMENT? (Doc ID 1468013.1)

https://support.oracle.com/epmos/fac...te=hq6zqg21z_4
03/04/2023, 12h38
Habib91

Accès au site Oracle

Merci.

je n'ai pas pu accéder au site de support Oracle , il faut un Support Identifier.

Citation:

Envoyé par JeitEmgie

Il y a un exemple sur le site d'ORACLE :

How to Encode / Decode a .pdf File Using Base64_Encode and Embed the File in an XML Document Using XMLELEMENT? (Doc ID 1468013.1)

https://support.oracle.com/epmos/fac...te=hq6zqg21z_4
03/04/2023, 20h59
JeitEmgie

Un compte ORACLE gratuit suffit.

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
/*

create directory TestAA_DIR as '/testcases/base64';
grant all on directory TESTAA_DIR to refresh;

*/


declare
l_directory_name VARCHAR2(40) := 'TESTAA_DIR';
l_test blob;
l_attachment_clob clob;
file_out                       utl_file.file_type;
l_test_xml xmltype;
l_test_xml_blob blob;
l_blob_length number;
l_pos number := 1;
l_chunk_size number := 1000;

/* define code that will open a file, load it and encode it */
FUNCTION utl_base64_encode_pdf_file
( p_pdf_bfile                    IN BFILE
) RETURN BLOB
IS
l_pdf_length                     NUMBER;
l_pdf_blob                       BLOB;
l_pdf_bfile                      BFILE;
l_dest_offset                    INTEGER := 1;
l_src_offset                     INTEGER := 1;
l_pdf_blob_encoded               BLOB;
BEGIN
  l_pdf_blob_encoded := NULL;
  l_pdf_bfile := p_pdf_bfile;
  IF dbms_lob.fileexists(l_pdf_bfile) <> 1
  THEN
    NULL;
  ELSE
    l_pdf_length := dbms_lob.getlength(l_pdf_bfile);
    dbms_lob.createtemporary(l_pdf_blob,true);
    dbms_lob.fileopen(l_pdf_bfile, dbms_lob.file_readonly);
    dbms_lob.loadblobfromfile( dest_lob    => l_pdf_blob
                             , src_bfile   => l_pdf_bfile
                             , amount      => dbms_lob.lobmaxsize
                             , dest_offset => l_dest_offset
                             , src_offset  => l_src_offset
                             );  
    dbms_lob.fileclose(l_pdf_bfile);
    l_pdf_blob_encoded := utl_encode.base64_encode(l_pdf_blob);
  END IF;
  RETURN l_pdf_blob_encoded;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END utl_base64_encode_pdf_file;
begin

 l_test := utl_base64_encode_pdf_file(BFILENAME( l_directory_name, 'TESTPDF.pdf' ));

/* after encoding, covert the file to CLOB to embed it into an XML file */
    IF l_test IS NOT NULL
    THEN
      --
      l_blob_length := dbms_lob.getlength(l_test);
      WHILE l_pos < l_blob_length
      LOOP
        IF l_pos = 1
        THEN
          l_attachment_clob :=  
             TO_CLOB (utl_raw.cast_to_varchar2(dbms_lob.substr(l_test,l_chunk_size,l_pos)));
        ELSE
          l_attachment_clob := l_attachment_clob ||
             TO_CLOB (utl_raw.cast_to_varchar2(dbms_lob.substr(l_test,l_chunk_size,l_pos)));
        END IF;
        --
        l_pos := l_pos + l_chunk_size;
        --
      END LOOP;
      --
    END IF;


/* Now, create an xml file with the <Attachment> tag, and embed the encoded PDF in it */
select XMLELEMENT("Attachment",l_attachment_clob)
  into l_test_xml
  from dual;


/* Now, write the resulting file to disk */
dbms_output.put_line('length: ' || dbms_lob.getlength(l_test_xml.getclobval));

file_out := utl_file.fopen(l_directory_name,'result-xml-clob.txt','W');
utl_file.put_line(file_out, l_test_xml.getclobval);
utl_file.fflush(file_out);
utl_file.fclose(file_out);

end;
[FONT=Courier New, Courier, monospace]/
[/FONT]

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
/*
This should have been run during the encode step.

create directory TestAA_DIR as '/testcases/base64';
grant all on directory TESTAA_DIR to refresh;

*/

set serveroutput on

declare
l_directory_name VARCHAR2(40) := 'TESTAA_DIR';
l_test blob;
file_out                       utl_file.file_type;
l_clob_length number;
l_attachment_clob clob;
bfile_data BFILE;
v_size_element number :=0;
v_size_endelement number := 0;
l_pos number := 1;
l_chunk_size number := 1000;
l_dest_offset                    INTEGER := 1;
l_src_offset                     INTEGER := 1;
l_src_csid NUMBER := NLS_CHARSET_ID('UTF8');
l_lang_ctx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; 
l_warn INTEGER; 


FUNCTION utl_base64_decode_pdf_file
( l_pdf_blob                   IN BLOB
) RETURN BLOB
IS
l_pdf_blob_decoded               BLOB;
BEGIN
 l_pdf_blob_decoded := NULL;
 l_pdf_blob_decoded := utl_encode.base64_decode(l_pdf_blob);
 RETURN l_pdf_blob_decoded;
EXCEPTION
 WHEN OTHERS THEN
   RETURN NULL;
END utl_base64_decode_pdf_file;

begin
/* read the file as character [clob] and strip out the xml tag before decoding */

   dbms_lob.createtemporary(l_attachment_clob, true);


   bfile_data := BFILENAME( l_directory_name, 'result-xml-clob.txt' );
   dbms_lob.fileopen(bfile_data, dbms_lob.file_readonly);

   dbms_lob.loadclobfromfile( dest_lob    => l_attachment_clob
                            , src_bfile   => bfile_data
                            , amount      => dbms_lob.lobmaxsize
                            , dest_offset => l_dest_offset
                            , src_offset  => l_src_offset   
                            , bfile_csid  => l_src_csid
                            , lang_context => l_lang_ctx
                            , warning     => l_warn 
                            );  
   dbms_lob.fileclose(bfile_data);


/* now strip out the <Attachment> tag added when encoding */

   v_size_element := dbms_lob.instr(l_attachment_clob, '>' /*default offset and default first occurrence*/);
   v_size_endelement := v_size_element + 1;

/* strip off the last one first, as not to affect the instr location of the first tag */
   dbms_lob.erase(l_attachment_clob, v_size_endelement, dbms_lob.getlength(l_attachment_clob) - v_size_endelement);

/* then strip off the first xml tag */
   dbms_lob.erase(l_attachment_clob, v_size_element /*default offset*/ );


/* now the file should have the XML tag stripped out, though white space is there */
dbms_output.put_line('clob size w/o xml tag ' ||dbms_lob.getlength(l_attachment_clob));


      l_clob_length := dbms_lob.getlength(l_attachment_clob);

/* reset variables used when loading clob */
l_dest_offset :=1;
l_src_offset  :=1;  
l_warn := null;
l_lang_ctx := DBMS_LOB.DEFAULT_LANG_CTX; 

/* convert the clob to a blob to decode */
dbms_lob.createtemporary(l_test, true);
dbms_lob.converttoblob(dest_lob     => l_test
                     ,src_clob     => l_attachment_clob
                     ,amount       => l_clob_length
                     ,dest_offset  => l_dest_offset
                     ,src_offset   => l_src_offset    
                     ,blob_csid    => l_src_csid
                     ,lang_context => l_lang_ctx
                     ,warning      => l_warn);

/* decode */
l_test := utl_base64_decode_pdf_file(l_test);                            


/* write the decoded file to disk */
dbms_output.put_line('length: ' || dbms_lob.getlength(l_test));

file_out := utl_file.fopen(l_directory_name,'decoded-file.pdf','WB');
utl_file.put_raw(file_out, l_test);
utl_file.fflush(file_out);
utl_file.fclose(file_out);

end;
/

05/04/2023, 16h18
Habib91

valeur null après le décodage du Blob

Merci JeitEmgie,
En exécutant le 2ème script, la variable l_test se retrouve à 0, juste après le décodage du blob.
après l'appel utl_base64_decode_pdf_file

Code:

l_test := utl_base64_decode_pdf_file(l_test);

Rappel: l'objection est de extraire uniquement le fichier PDF joint au fichier XML.

réponse

Merci JeitEmgie,
En exécutant le 2ème script, la variable l_test se retrouve à 0, juste après le décodage du blob.
après l'appel utl_base64_decode_pdf_file
Code : Sélectionner tout - Visualiser dans une fenêtre à part

Code:

l_test := utl_base64_decode_pdf_file(l_test);

Citation:

Envoyé par JeitEmgie

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
/*

create directory TestAA_DIR as '/testcases/base64';
grant all on directory TESTAA_DIR to refresh;

*/


declare
l_directory_name VARCHAR2(40) := 'TESTAA_DIR';
l_test blob;
l_attachment_clob clob;
file_out                       utl_file.file_type;
l_test_xml xmltype;
l_test_xml_blob blob;
l_blob_length number;
l_pos number := 1;
l_chunk_size number := 1000;

/* define code that will open a file, load it and encode it */
FUNCTION utl_base64_encode_pdf_file
( p_pdf_bfile                    IN BFILE
) RETURN BLOB
IS
l_pdf_length                     NUMBER;
l_pdf_blob                       BLOB;
l_pdf_bfile                      BFILE;
l_dest_offset                    INTEGER := 1;
l_src_offset                     INTEGER := 1;
l_pdf_blob_encoded               BLOB;
BEGIN
  l_pdf_blob_encoded := NULL;
  l_pdf_bfile := p_pdf_bfile;
  IF dbms_lob.fileexists(l_pdf_bfile) <> 1
  THEN
    NULL;
  ELSE
    l_pdf_length := dbms_lob.getlength(l_pdf_bfile);
    dbms_lob.createtemporary(l_pdf_blob,true);
    dbms_lob.fileopen(l_pdf_bfile, dbms_lob.file_readonly);
    dbms_lob.loadblobfromfile( dest_lob    => l_pdf_blob
                             , src_bfile   => l_pdf_bfile
                             , amount      => dbms_lob.lobmaxsize
                             , dest_offset => l_dest_offset
                             , src_offset  => l_src_offset
                             );  
    dbms_lob.fileclose(l_pdf_bfile);
    l_pdf_blob_encoded := utl_encode.base64_encode(l_pdf_blob);
  END IF;
  RETURN l_pdf_blob_encoded;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END utl_base64_encode_pdf_file;
begin

 l_test := utl_base64_encode_pdf_file(BFILENAME( l_directory_name, 'TESTPDF.pdf' ));

/* after encoding, covert the file to CLOB to embed it into an XML file */
    IF l_test IS NOT NULL
    THEN
      --
      l_blob_length := dbms_lob.getlength(l_test);
      WHILE l_pos < l_blob_length
      LOOP
        IF l_pos = 1
        THEN
          l_attachment_clob :=  
             TO_CLOB (utl_raw.cast_to_varchar2(dbms_lob.substr(l_test,l_chunk_size,l_pos)));
        ELSE
          l_attachment_clob := l_attachment_clob ||
             TO_CLOB (utl_raw.cast_to_varchar2(dbms_lob.substr(l_test,l_chunk_size,l_pos)));
        END IF;
        --
        l_pos := l_pos + l_chunk_size;
        --
      END LOOP;
      --
    END IF;


/* Now, create an xml file with the <Attachment> tag, and embed the encoded PDF in it */
select XMLELEMENT("Attachment",l_attachment_clob)
  into l_test_xml
  from dual;


/* Now, write the resulting file to disk */
dbms_output.put_line('length: ' || dbms_lob.getlength(l_test_xml.getclobval));

file_out := utl_file.fopen(l_directory_name,'result-xml-clob.txt','W');
utl_file.put_line(file_out, l_test_xml.getclobval);
utl_file.fflush(file_out);
utl_file.fclose(file_out);

end;
[FONT=Courier New, Courier, monospace]/
[/FONT]

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
/*
This should have been run during the encode step.

create directory TestAA_DIR as '/testcases/base64';
grant all on directory TESTAA_DIR to refresh;

*/

set serveroutput on

declare
l_directory_name VARCHAR2(40) := 'TESTAA_DIR';
l_test blob;
file_out                       utl_file.file_type;
l_clob_length number;
l_attachment_clob clob;
bfile_data BFILE;
v_size_element number :=0;
v_size_endelement number := 0;
l_pos number := 1;
l_chunk_size number := 1000;
l_dest_offset                    INTEGER := 1;
l_src_offset                     INTEGER := 1;
l_src_csid NUMBER := NLS_CHARSET_ID('UTF8');
l_lang_ctx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; 
l_warn INTEGER; 


FUNCTION utl_base64_decode_pdf_file
( l_pdf_blob                   IN BLOB
) RETURN BLOB
IS
l_pdf_blob_decoded               BLOB;
BEGIN
 l_pdf_blob_decoded := NULL;
 l_pdf_blob_decoded := utl_encode.base64_decode(l_pdf_blob);
 RETURN l_pdf_blob_decoded;
EXCEPTION
 WHEN OTHERS THEN
   RETURN NULL;
END utl_base64_decode_pdf_file;

begin
/* read the file as character [clob] and strip out the xml tag before decoding */

   dbms_lob.createtemporary(l_attachment_clob, true);


   bfile_data := BFILENAME( l_directory_name, 'result-xml-clob.txt' );
   dbms_lob.fileopen(bfile_data, dbms_lob.file_readonly);

   dbms_lob.loadclobfromfile( dest_lob    => l_attachment_clob
                            , src_bfile   => bfile_data
                            , amount      => dbms_lob.lobmaxsize
                            , dest_offset => l_dest_offset
                            , src_offset  => l_src_offset   
                            , bfile_csid  => l_src_csid
                            , lang_context => l_lang_ctx
                            , warning     => l_warn 
                            );  
   dbms_lob.fileclose(bfile_data);


/* now strip out the <Attachment> tag added when encoding */

   v_size_element := dbms_lob.instr(l_attachment_clob, '>' /*default offset and default first occurrence*/);
   v_size_endelement := v_size_element + 1;

/* strip off the last one first, as not to affect the instr location of the first tag */
   dbms_lob.erase(l_attachment_clob, v_size_endelement, dbms_lob.getlength(l_attachment_clob) - v_size_endelement);

/* then strip off the first xml tag */
   dbms_lob.erase(l_attachment_clob, v_size_element /*default offset*/ );


/* now the file should have the XML tag stripped out, though white space is there */
dbms_output.put_line('clob size w/o xml tag ' ||dbms_lob.getlength(l_attachment_clob));


      l_clob_length := dbms_lob.getlength(l_attachment_clob);

/* reset variables used when loading clob */
l_dest_offset :=1;
l_src_offset  :=1;  
l_warn := null;
l_lang_ctx := DBMS_LOB.DEFAULT_LANG_CTX; 

/* convert the clob to a blob to decode */
dbms_lob.createtemporary(l_test, true);
dbms_lob.converttoblob(dest_lob     => l_test
                     ,src_clob     => l_attachment_clob
                     ,amount       => l_clob_length
                     ,dest_offset  => l_dest_offset
                     ,src_offset   => l_src_offset    
                     ,blob_csid    => l_src_csid
                     ,lang_context => l_lang_ctx
                     ,warning      => l_warn);

/* decode */
l_test := utl_base64_decode_pdf_file(l_test);                            


/* write the decoded file to disk */
dbms_output.put_line('length: ' || dbms_lob.getlength(l_test));

file_out := utl_file.fopen(l_directory_name,'decoded-file.pdf','WB');
utl_file.put_raw(file_out, l_test);
utl_file.fflush(file_out);
utl_file.fclose(file_out);

end;
/