CREATE OR REPLACE PACKAGE as_zip
IS
/**********************************************
**
** Author: Anton Scheffer
** Date: 25-01-2012
** Website: http://technology.amis.nl/blog
** https://technology.amis.nl/wp-content/uploads/2010/06/as_zip8.txt
**
** Changelog:
**   Date: 29-04-2012
**    fixed bug for large uncompressed files, thanks Morten Braten
**   Date: 21-03-2012
**     Take CRC32, compressed length and uncompressed length from Central file header instead of Local file header
**   Date: 17-02-2012
**     Added more support for non-ascii filenames
**   Date: 25-01-2012
**     Added MIT-license
**     Some minor improvements
**   Date: 31-01-2014
**     file limit increased to 4GB
** v9 MC le 27.03.2015: Ajout param Compression (add1file)
** 										: Ajout Date Maj, size_comp et size_uncomp dans File_List
**										: Ajout Procédure Init_zip pour récupérer un zip existant et pouvoir rajouter des fichiers
**										: Debug Finish_zip pour les zip avec extra comment + Opti
**										: Debug File2Blob si source est à 0 octet
******************************************************************************
******************************************************************************
Copyright (C) 2010,2011 by Anton Scheffer

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
IN THE SOFTWARE.

************************************************************************************************************************** */

	TYPE REC_FILE_LIST IS RECORD(filename VARCHAR2(2000), datemaj DATE, size_comp NUMBER, size_uncomp NUMBER);
  TYPE file_list IS TABLE OF REC_FILE_LIST;

  FUNCTION file2blob( p_dir varchar2, p_file_name varchar2)
  RETURN blob;

  FUNCTION get_file_list( p_dir varchar2, p_zip_file varchar2, p_encoding varchar2 := NULL)
  RETURN file_list;

  FUNCTION get_file_list( p_zipped_blob blob, p_encoding varchar2 := NULL)
  RETURN file_list;

	FUNCTION get_file( p_dir varchar2, p_zip_file varchar2, p_file_name varchar2, p_encoding varchar2 := NULL)
	RETURN blob;

  FUNCTION get_file( p_zipped_blob blob, p_file_name varchar2, p_encoding varchar2 := NULL)
  RETURN blob;

  PROCEDURE add1file( p_zipped_blob IN OUT NOCOPY blob, p_name varchar2, p_content blob, p_compress NUMBER := 6);

	PROCEDURE INIT_ZIP (p_zipped_blob IN OUT NOCOPY BLOB);
	/* Init_zip : Permet d'initialiser un zip (supprimer les infos de directory de fichier) pour pouvoir rajouter des fichiers */

  PROCEDURE finish_zip( p_zipped_blob IN OUT NOCOPY blob );
	/* Finish_zip : Finalise le fichier zip (crée les infos de directory de fichier) */

  PROCEDURE save_zip( p_zipped_blob blob, p_dir varchar2 := 'MY_DIR', p_filename varchar2 := 'my.zip');


/*
declare
  g_zipped_blob blob;
begin
  as_zip.add1file( g_zipped_blob, 'test4.txt', null ); -- a empty file
  as_zip.add1file( g_zipped_blob, 'dir1/test1.txt', utl_raw.cast_to_raw( q'<A file with some more text, stored in a subfolder which isn't added>' ) );
  as_zip.add1file( g_zipped_blob, 'test1234.txt', utl_raw.cast_to_raw( 'A small file' ) );
  as_zip.add1file( g_zipped_blob, 'dir2/', null ); -- a folder
  as_zip.add1file( g_zipped_blob, 'dir3/', null ); -- a folder
  as_zip.add1file( g_zipped_blob, 'dir3/test2.txt', utl_raw.cast_to_raw( 'A small filein a previous created folder' ) );
  as_zip.add1file( g_zipped_blob, '2013.zip', as_zip.file2blob( 'EEDI', '2013.zip' ) );
  as_zip.finish_zip( g_zipped_blob );
  as_zip.save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' );
  dbms_lob.freetemporary( g_zipped_blob );
end;
--
DECLARE
  v_doc BLOB;
  zip_files as_zip.file_list;
BEGIN
	SELECT blob_zip
	INTO v_doc
	FROM archive_fic
	WHERE nomdoc = 'A';
  zip_files  := as_zip.get_file_list( v_doc );
  FOR i IN zip_files.FIRST() .. zip_files.LAST
  LOOP
		dbms_output.put_line(DBMS_LOB.SUBSTR(zip_files(i).filename,2000,1) ||' '|| TO_CHAR(zip_files(i).datemaj,'DD/MM/RRRR HH24:MI:SS') ||' size:'|| zip_files(i).size_comp ||' / '|| zip_files(i).size_uncomp);
    dbms_output.put_line( utl_raw.cast_to_varchar2( as_zip.get_file(v_doc, zip_files(i))));
  END LOOP;
END;


Récupération d'un zip existant pour rajouter un fichier (et le sauvegarder ailleurs)

	dbms_lob.createtemporary( g_zipped_blob, TRUE );
	SELECT BLOB_ZIP 
  INTO v_lob
  FROM ARCHIVE_FIC
  WHERE NOMDOC = 'archiveEDI_2015';
  DBMS_LOB.append(g_zipped_blob, v_lob);
  as_zip.init_zip(g_zipped_blob);
  as_zip.add1file( g_zipped_blob, 'nouveaufichier.txt', UTL_RAW.cast_to_raw( 'A small file' ) );
  as_zip.finish_zip( g_zipped_blob );
	...
 	dbms_lob.freetemporary( g_zipped_blob );
  

Récupération d'un zip existant pour rajouter un fichier et l'updater directement

	SELECT BLOB_ZIP 
  INTO g_zipped_blob
  FROM ARCHIVE_FIC
  WHERE NOMDOC = 'archiveEDI_2015'
  FOR UPDATE;
  as_zip.init_zip(g_zipped_blob);
  as_zip.add1file( g_zipped_blob, 'nouveaufichier.txt', UTL_RAW.cast_to_raw( 'A small file' ) );
  as_zip.finish_zip( g_zipped_blob );
-- Automatiquement le BLOB est mis à jour.. /!\ en cas de plantage
*/
END;
/
CREATE OR REPLACE PACKAGE BODY as_zip
IS

  c_LOCAL_FILE_HEADER        CONSTANT raw(4) := HEXTORAW( '504B0304' ); -- Local file header signature
  c_END_OF_CENTRAL_DIRECTORY CONSTANT raw(4) := HEXTORAW( '504B0506' ); -- End of central directory signature
	T_COMMENT RAW(32767) := UTL_RAW.cast_to_raw('Implementation by Anton Scheffer.'|| CHR(10) ||'Amélioration par MC');

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION blob2num( p_blob blob, p_len integer, p_pos integer )
RETURN number
IS
  rv number;
BEGIN
  rv := UTL_RAW.cast_to_binary_integer( dbms_lob.substr( p_blob, p_len, p_pos ), UTL_RAW.little_endian );
  IF rv < 0
  THEN
    rv := rv + 4294967296;
  END IF;
  RETURN rv;
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION raw2num( p_raw raw )
RETURN NUMBER
IS
  rv NUMBER;
BEGIN
  rv := UTL_RAW.cast_to_binary_integer( p_raw, UTL_RAW.little_endian );
  IF rv < 0
  THEN
    rv := rv + 4294967296;
  END IF;
  RETURN rv;
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION raw2varchar2( p_raw raw, p_encoding varchar2 )
RETURN varchar2
IS
BEGIN
  RETURN COALESCE( utl_i18n.raw_to_char( p_raw, p_encoding )
                 , utl_i18n.raw_to_char( p_raw, utl_i18n.map_charset( p_encoding, utl_i18n.GENERIC_CONTEXT, utl_i18n.IANA_TO_ORACLE ) )
                 );
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION little_endian( p_big number, p_bytes PLS_INTEGER := 4 )
RETURN raw
IS
  t_big number := p_big;
BEGIN
  IF t_big > 2147483647
  THEN
    t_big := t_big - 4294967296;
  END IF;
  RETURN UTL_RAW.SUBSTR( UTL_RAW.cast_from_binary_integer( t_big, UTL_RAW.little_endian ), 1, p_bytes );
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION RAW2DATE (p_data IN VARCHAR2) RETURN DATE
IS
	v_date DATE;
	vj NUMBER;
	vh NUMBER;
BEGIN

  IF NVL(LENGTH(p_data),0) <> 8
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Erreur de format de date : reçu ['|| p_data ||']');
  END IF;

  -- On récup le nombre correspond au codage des heures ( SS/2 + 32xMI + 2048*HH24 )
  vh := raw2num(SUBSTR(p_data, 1, 4));
    
  -- On récup le nombre correspond au codage du jour ( DD+ 32xMM + 512*RRRR-1980 )
  vj := raw2num(SUBSTR(p_data, 5, 4));

	BEGIN
    v_date := TO_DATE(MOD(vj, 32) ||'/'|| TRUNC(MOD(vj, 512)/32) ||'/'|| (TRUNC(vj/512) +1980) 
            ||' '|| TRUNC(vh/2048) ||':'|| TRUNC(MOD(vh, 2048)/32) ||':'|| (CASE WHEN MOD(vh, 32) = 30 THEN 59 ELSE 2*MOD(vh, 32) END), 'DD/MM/RRRR HH24:MI:SS');
	EXCEPTION WHEN OTHERS THEN
  	RAISE_APPLICATION_ERROR(-20002, 'Erreur de format de date : reçu ['|| p_data ||']');
  END;
  
      
  /*SELECT dt, c, TRUNC(c/2048) hh, TRUNC(MOD(c, 2048)/32) mi, 2*MOD(c, 32) ss,
        d, TRUNC(d/512) +1980 rrrr, TRUNC(MOD(d, 512)/32) mm, MOD(d, 32) dd
  FROM t*/
                        
  RETURN v_date;
  
END RAW2DATE;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION FILE2BLOB( p_dir varchar2, p_file_name varchar2)
RETURN blob
IS
  file_lob bfile;
  file_blob blob;
BEGIN
  file_lob := BFILENAME( p_dir, p_file_name );
  dbms_lob.open( file_lob, dbms_lob.file_readonly );
  dbms_lob.createtemporary( file_blob, TRUE );
  IF DBMS_LOB.getlength(file_lob) > 0
  THEN 
	  dbms_lob.loadfromfile( file_blob, file_lob, dbms_lob.lobmaxsize );
  END IF;
  dbms_lob.close( file_lob );
  RETURN file_blob;
EXCEPTION
  WHEN others THEN
    IF dbms_lob.isopen( file_lob ) = 1 THEN dbms_lob.close( file_lob ); END IF;
    IF dbms_lob.istemporary( file_blob ) = 1 THEN dbms_lob.freetemporary( file_blob ); END IF;
    RAISE;
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PROCEDURE INIT_ZIP (p_zipped_blob IN OUT NOCOPY BLOB)
IS
 t_ind integer;
 v_lencomment integer;
BEGIN

  t_ind := dbms_lob.getlength( p_zipped_blob ) - 21;
  IF NVL(t_ind,0) <= 0 THEN RETURN; END IF;

  LOOP
    exit WHEN t_ind < 1 OR dbms_lob.substr(p_zipped_blob, 4, t_ind ) = c_END_OF_CENTRAL_DIRECTORY;
    t_ind := t_ind - 1;
  END LOOP;
  IF t_ind <= 0 THEN RETURN; END IF;

	-- On récupère le commentaire du zip.  
  v_lencomment := blob2num( p_zipped_blob, 2, t_ind + 20);
  IF v_lencomment > 0
  THEN
   T_COMMENT := dbms_lob.substr(p_zipped_blob, v_lencomment, t_ind + 22);
  END IF;

	-- Taille des fichiers zippés  (Offset of start of central directory, relative to start of archive)
  t_ind := blob2num(p_zipped_blob, 4, t_ind + 16 );
  -- On supprime le central directory.
  DBMS_LOB.TRIM (p_zipped_blob, t_ind);
  

END INIT_ZIP;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION GET_FILE_LIST( p_zipped_blob IN blob, p_encoding varchar2 := NULL)
RETURN file_list
IS
  t_ind integer;
  t_hd_ind integer;
  t_rv file_list;
  t_encoding varchar2(32767);
BEGIN
  t_ind := NVL(dbms_lob.getlength( p_zipped_blob ),0) - 21;
  LOOP
    exit WHEN t_ind < 1 OR dbms_lob.substr( p_zipped_blob, 4, t_ind ) = c_END_OF_CENTRAL_DIRECTORY;
    t_ind := t_ind - 1;
  END LOOP;

  IF t_ind <= 0
  THEN
    RETURN NULL;
  END IF;

  t_hd_ind := blob2num( p_zipped_blob, 4, t_ind + 16 ) + 1;
  t_rv := file_list();
    
  t_rv.EXTEND( blob2num( p_zipped_blob, 2, t_ind + 10 ) );

  FOR i IN 1 .. blob2num( p_zipped_blob, 2, t_ind + 8 )
  LOOP
    IF p_encoding IS NULL
    THEN
      IF UTL_RAW.bit_and( dbms_lob.substr( p_zipped_blob, 1, t_hd_ind + 9 ), HEXTORAW( '08' ) ) = HEXTORAW( '08' )
      THEN
        t_encoding := 'AL32UTF8'; -- utf8
      ELSE
        t_encoding := 'US8PC437'; -- IBM codepage 437
      END IF;
    ELSE
      t_encoding := p_encoding;
    END IF;

    t_rv(i).datemaj 		:= RAW2DATE(dbms_lob.substr( p_zipped_blob, 4, t_hd_ind + 12 ));
    t_rv(i).size_comp 	:= raw2num(dbms_lob.substr( p_zipped_blob, 4, t_hd_ind + 20 ));
    t_rv(i).size_uncomp := raw2num(dbms_lob.substr( p_zipped_blob, 4, t_hd_ind + 24 ));

    t_rv(i).filename := raw2varchar2
                   ( dbms_lob.substr( p_zipped_blob
                                    , blob2num( p_zipped_blob, 2, t_hd_ind + 28 )
                                    , t_hd_ind + 46
                                    )
                   , t_encoding
                   );
    t_hd_ind := t_hd_ind + 46
              + blob2num( p_zipped_blob, 2, t_hd_ind + 28 )  -- File name length
              + blob2num( p_zipped_blob, 2, t_hd_ind + 30 )  -- Extra field length
              + blob2num( p_zipped_blob, 2, t_hd_ind + 32 ); -- File comment length
  END LOOP;

  RETURN t_rv;
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION GET_FILE_LIST (p_dir varchar2, p_zip_file varchar2, p_encoding varchar2 := NULL)
RETURN file_list
IS
BEGIN
  RETURN get_file_list( file2blob( p_dir, p_zip_file ), p_encoding );
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION GET_FILE (p_zipped_blob blob, p_file_name varchar2, p_encoding varchar2 := NULL)
RETURN blob
IS
  t_tmp blob;
  t_ind integer;
  t_hd_ind integer;
  t_fl_ind integer;
  t_encoding varchar2(32767);
  t_len integer;
BEGIN
  t_ind := dbms_lob.getlength( p_zipped_blob ) - 21;
  LOOP
    exit WHEN t_ind < 1 OR dbms_lob.substr( p_zipped_blob, 4, t_ind ) = c_END_OF_CENTRAL_DIRECTORY;
    t_ind := t_ind - 1;
  END LOOP;

  IF t_ind <= 0
  THEN
    RETURN NULL;
  END IF;

  t_hd_ind := blob2num( p_zipped_blob, 4, t_ind + 16 ) + 1;
  FOR i IN 1 .. blob2num( p_zipped_blob, 2, t_ind + 8 )
  LOOP
    IF p_encoding IS NULL
    THEN
      IF UTL_RAW.bit_and( dbms_lob.substr( p_zipped_blob, 1, t_hd_ind + 9 ), HEXTORAW( '08' ) ) = HEXTORAW( '08' )
      THEN
        t_encoding := 'AL32UTF8'; -- utf8
      ELSE
        t_encoding := 'US8PC437'; -- IBM codepage 437
      END IF;
    ELSE
      t_encoding := p_encoding;
    END IF;
    IF p_file_name = raw2varchar2( dbms_lob.substr( p_zipped_blob
                                        , blob2num( p_zipped_blob, 2, t_hd_ind + 28 )
                                        , t_hd_ind + 46
                                        ), t_encoding)
    THEN
      t_len := blob2num( p_zipped_blob, 4, t_hd_ind + 24 ); -- uncompressed length
      IF t_len = 0
      THEN
        IF SUBSTR( p_file_name, -1 ) IN ( '/', '\' )
        THEN  -- directory/folder
          RETURN NULL;
        ELSE -- empty file
          RETURN EMPTY_BLOB();
        END IF;
      END IF;

      IF dbms_lob.substr( p_zipped_blob, 2, t_hd_ind + 10 ) = HEXTORAW( '0800' ) -- deflate
      THEN
        t_fl_ind := blob2num( p_zipped_blob, 4, t_hd_ind + 42 );
        t_tmp := HEXTORAW( '1F8B0800000000000003' ); -- gzip header
        dbms_lob.copy( t_tmp
                     , p_zipped_blob
                     ,  blob2num( p_zipped_blob, 4, t_hd_ind + 20 )
                     , 11
                     , t_fl_ind + 31
                     + blob2num( p_zipped_blob, 2, t_fl_ind + 27 ) -- File name length
                     + blob2num( p_zipped_blob, 2, t_fl_ind + 29 ) -- Extra field length
                     );
        dbms_lob.append( t_tmp, UTL_RAW.CONCAT( dbms_lob.substr( p_zipped_blob, 4, t_hd_ind + 16 ) -- CRC32
                                              , little_endian( t_len ) -- uncompressed length
                                              )
                       );
        RETURN utl_compress.lz_uncompress( t_tmp );
      END IF;

      IF dbms_lob.substr( p_zipped_blob, 2, t_hd_ind + 10 ) = HEXTORAW( '0000' ) -- The file is stored (no compression)
      THEN
        t_fl_ind := blob2num( p_zipped_blob, 4, t_hd_ind + 42 );
        dbms_lob.createtemporary( t_tmp, TRUE );
        dbms_lob.copy( t_tmp
                     , p_zipped_blob
                     , t_len
                     , 1
                     , t_fl_ind + 31
                     + blob2num( p_zipped_blob, 2, t_fl_ind + 27 ) -- File name length
                     + blob2num( p_zipped_blob, 2, t_fl_ind + 29 ) -- Extra field length
                     );
        RETURN t_tmp;
      END IF;
    END IF;
    t_hd_ind := t_hd_ind + 46
              + blob2num( p_zipped_blob, 2, t_hd_ind + 28 )  -- File name length
              + blob2num( p_zipped_blob, 2, t_hd_ind + 30 )  -- Extra field length
              + blob2num( p_zipped_blob, 2, t_hd_ind + 32 ); -- File comment length
  END LOOP;

  RETURN NULL;
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

FUNCTION GET_FILE (p_dir varchar2, p_zip_file varchar2, p_file_name varchar2, p_encoding varchar2 := NULL)
RETURN blob
IS
BEGIN
  RETURN get_file( file2blob( p_dir, p_zip_file ), p_file_name, p_encoding );
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PROCEDURE ADD1FILE( p_zipped_blob IN OUT NOCOPY blob, p_name varchar2, p_content blob, p_compress NUMBER := 6)
IS
  t_now date;
  t_blob blob;
  t_len integer;
  t_clen integer;
  t_crc32 raw(4) := HEXTORAW( '00000000' );
  t_compressed BOOLEAN := FALSE;
  t_name raw(32767);
BEGIN
  t_now := SYSDATE;
  t_len := NVL( dbms_lob.getlength( p_content ), 0 );
  IF t_len > 0
  THEN
    t_blob := utl_compress.lz_compress( p_content, p_compress);
    t_clen := dbms_lob.getlength( t_blob ) - 18;
    t_compressed := t_clen < t_len;
    t_crc32 := dbms_lob.substr( t_blob, 4, t_clen + 11 );
  END IF;
  IF NOT t_compressed
  THEN
    t_clen := t_len;
    t_blob := p_content;
  END IF;
  IF p_zipped_blob IS NULL
  THEN
    dbms_lob.createtemporary( p_zipped_blob, TRUE );
  END IF;
  t_name := utl_i18n.string_to_raw( p_name, 'AL32UTF8' );
 
/*     local file header signature     4 bytes  (0x04034b50)
        version needed to extract       2 bytes
        general purpose bit flag        2 bytes
        compression method              2 bytes
        last mod file time              2 bytes
        last mod file date              2 bytes
        crc-32                          4 bytes
        compressed size                 4 bytes
        uncompressed size               4 bytes
        file name length                2 bytes
        extra field length              2 bytes

        file name (variable size)
        extra field (variable size)
   */
   
	 dbms_lob.append( p_zipped_blob
                 , UTL_RAW.CONCAT( c_LOCAL_FILE_HEADER -- Local file header signature
                                 , HEXTORAW( '1400' )  -- version 2.0
                                 , CASE WHEN t_name = utl_i18n.string_to_raw( p_name, 'US8PC437' )
                                     THEN HEXTORAW( '0000' ) -- no General purpose bits
                                     ELSE HEXTORAW( '0008' ) -- set Language encoding flag (EFS)
                                   END
                                 , CASE WHEN t_compressed
                                      THEN HEXTORAW( '0800' ) -- deflate
                                      ELSE HEXTORAW( '0000' ) -- stored
                                   END
                                 , little_endian( TO_NUMBER( TO_CHAR( t_now, 'ss' ) ) / 2
                                                + TO_NUMBER( TO_CHAR( t_now, 'mi' ) ) * 32
                                                + TO_NUMBER( TO_CHAR( t_now, 'hh24' ) ) * 2048
                                                , 2
                                                ) -- File last modification time
                                 , little_endian( TO_NUMBER( TO_CHAR( t_now, 'dd' ) )
                                                + TO_NUMBER( TO_CHAR( t_now, 'mm' ) ) * 32
                                                + ( TO_NUMBER( TO_CHAR( t_now, 'yyyy' ) ) - 1980 ) * 512
                                                , 2
                                                ) -- File last modification date
                                 , t_crc32 -- CRC-32
                                 , little_endian( t_clen )                      -- compressed size
                                 , little_endian( t_len )                       -- uncompressed size
                                 , little_endian( UTL_RAW.LENGTH( t_name ), 2 ) -- File name length
                                 , HEXTORAW( '0000' )                           -- Extra field length
                                 , t_name                                       -- File name
                                 )
                 );
                   
  IF t_compressed
  THEN
    dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 11 ); -- compressed content
  ELSIF t_clen > 0
  THEN
    dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength( p_zipped_blob ) + 1, 1 ); --  content
  END IF;

  IF dbms_lob.istemporary( t_blob ) = 1 THEN dbms_lob.freetemporary( t_blob ); END IF;

END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PROCEDURE FINISH_ZIP( p_zipped_blob IN OUT NOCOPY blob )
IS
  t_cnt PLS_INTEGER := 0;
  t_offs integer;
  t_offs_dir_header integer;
  t_offs_end_header integer;
  v_filename_len integer;
  v_extra_len 		integer;
BEGIN
  t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
  t_offs := 1;
  WHILE dbms_lob.substr( p_zipped_blob, UTL_RAW.LENGTH( c_LOCAL_FILE_HEADER ), t_offs ) = c_LOCAL_FILE_HEADER
  LOOP
    t_cnt := t_cnt + 1;
    
    v_filename_len := blob2num( p_zipped_blob, 2, t_offs + 26);
    v_extra_len := blob2num( p_zipped_blob, 2, t_offs + 28);
    
    
    -- Dans le zip créé sous linux, l'extrafield est différent entre l'entête et le directory (8 bytes de plus)
    /*central file header signature   4 bytes  (0x02014b50)
        version made by                 2 bytes
        version needed to extract       2 bytes
        general purpose bit flag        2 bytes
        compression method              2 bytes
        last mod file time              2 bytes
        last mod file date              2 bytes
        crc-32                          4 bytes
        compressed size                 4 bytes
        uncompressed size               4 bytes
        file name length                2 bytes
        extra field length              2 bytes
        file comment length             2 bytes
        disk number start               2 bytes
        internal file attributes        2 bytes
        external file attributes        4 bytes
        relative offset of local header 4 bytes

        file name (variable size)
        extra field (variable size)
        file comment (variable size)
       */
    dbms_lob.append( p_zipped_blob
                   , UTL_RAW.CONCAT( HEXTORAW( '504B0102' )      -- Central directory file header signature
                                   , HEXTORAW( '1400' )          -- version 2.0
                                   , dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
                                   , HEXTORAW( '0000' )          -- File comment length
                                   , HEXTORAW( '0000' )          -- Disk number where file starts
                                   , HEXTORAW( '0000' )          -- Internal file attributes =>
                                                                 --     0000 binary file
                                                                 --     0100 (ascii)text file
                                   , CASE
                                       WHEN dbms_lob.substr( p_zipped_blob, 1, t_offs + 30 + v_filename_len - 1) IN ( HEXTORAW( '2F' ), HEXTORAW( '5C' )) -- /  et \ 
                                       THEN HEXTORAW( '10000000' ) -- a directory/folder
                                       ELSE HEXTORAW( '2000B681' ) -- a file
                                     END                         -- External file attributes
                                   , little_endian( t_offs - 1 ) -- Relative offset of local file header
                                   , dbms_lob.substr( p_zipped_blob, v_filename_len, t_offs + 30) -- File name
                                   )
                   );
                   
		-- On rajoute l'extra du zip
    IF  v_extra_len > 0
    THEN             
     dbms_lob.append( p_zipped_blob, dbms_lob.substr( p_zipped_blob, v_extra_len, t_offs + 30 + v_filename_len)) ;
    END IF;    
                   
    t_offs := t_offs + 30 + blob2num( p_zipped_blob, 4, t_offs + 18 )  -- compressed size
                          + v_filename_len  -- File name length
                          + v_extra_len; -- Extra field length
  END LOOP;
  t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
  dbms_lob.append( p_zipped_blob
                 , UTL_RAW.CONCAT( c_END_OF_CENTRAL_DIRECTORY                                -- End of central directory signature
                                 , HEXTORAW( '0000' )                                        -- Number of this disk
                                 , HEXTORAW( '0000' )                                        -- Disk where central directory starts
                                 , little_endian( t_cnt, 2 )                                 -- Number of central directory records on this disk
                                 , little_endian( t_cnt, 2 )                                 -- Total number of central directory records
                                 , little_endian( t_offs_end_header - t_offs_dir_header )    -- Size of central directory
                                 , little_endian( t_offs_dir_header )                        -- Offset of start of central directory, relative to start of archive
                                 , little_endian( NVL( UTL_RAW.LENGTH( T_COMMENT ), 0 ), 2 ) -- ZIP file comment length
                                 , t_comment
                                 )
                 );
END;

--  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

PROCEDURE SAVE_ZIP (p_zipped_blob blob, p_dir varchar2 := 'MY_DIR', p_filename varchar2 := 'my.zip')
IS
  t_fh UTL_FILE.file_type;
  t_len PLS_INTEGER := 32767;
BEGIN
  t_fh := UTL_FILE.fopen( p_dir, p_filename, 'wb' );
  FOR i IN 0 .. TRUNC( ( dbms_lob.getlength( p_zipped_blob ) - 1 ) / t_len )
  LOOP
    UTL_FILE.put_raw( t_fh, dbms_lob.substr( p_zipped_blob, t_len, i * t_len + 1 ) );
  END LOOP;
  UTL_FILE.fclose( t_fh );
END;


END;
/