We help IT Professionals succeed at work.
Get Started
zip and unzip in PLSQL
Hello Experts,
I was going through zip and unzip process from PLSQL , and I found from my earlier question that some one has already done this.
And I was able to unzip the zip file using DBMS_OUTPUT but what I want is to unzip on the same directory like :
If the my zip file (test.zip )has two files like :
test1.txt and test2.txt .
Then once I unzip from PLSQL it should unzip on the same drive.
Here is the code for as_zip :
I was going through zip and unzip process from PLSQL , and I found from my earlier question that some one has already done this.
And I was able to unzip the zip file using DBMS_OUTPUT but what I want is to unzip on the same directory like :
If the my zip file (test.zip )has two files like :
test1.txt and test2.txt .
Then once I unzip from PLSQL it should unzip on the same drive.
Here is the code for as_zip :
CREATE OR REPLACE PACKAGE "AS_ZIP"
is
type file_list is table of clob;
--
function get_file_list(
p_dir in varchar2
, p_zip_file in varchar2
, p_encoding in varchar2 := null
)
return file_list;
--
function get_file_list(
p_zipped_blob in blob
, p_encoding in varchar2 := null /* Use CP850 for zip files created with a German Winzip to see umlauts, etc */
)
return file_list;
--
function get_file(
p_dir in varchar2
, p_zip_file in varchar2
, p_file_name in varchar2
, p_encoding in varchar2 := null
)
return blob;
--
function get_file(
p_zipped_blob in blob
, p_file_name in varchar2
, p_encoding in varchar2 := null
)
return blob;
--
procedure add1file(
p_zipped_blob in out blob
, p_name in varchar2
, p_content in blob
);
--
procedure finish_zip(
p_zipped_blob in out blob
);
--
procedure save_zip(
P_ZIPPED_BLOB IN BLOB
, P_DIR IN VARCHAR2 := 'ORA_DIR'
, p_filename in varchar2 := 'TEST.zip'
);
--
/*
declare
g_zipped_blob blob;
begin
as_zip.add1file( g_zipped_blob, 'test1.txt', utl_raw.cast_to_raw( 'Dit is de laatste test! Waarom wordt dit dan niet gecomprimeerd?' ) );
as_zip.add1file( g_zipped_blob, 'test1234.txt', utl_raw.cast_to_raw( 'En hier staat wat anders' ) );
as_zip.finish_zip( g_zipped_blob );
as_zip.save_zip( g_zipped_blob, 'MY_DIR', 'my.zip' );
end;
--
declare
t_dir varchar2(100) := 'MY_DIR';
t_zip varchar2(100) := 'my.zip';
zip_files as_zip.file_list;
begin
zip_files := as_zip.get_file_list( t_dir, t_zip );
for i in zip_files.first() .. zip_files.last
loop
dbms_output.put_line( zip_files( i ) );
dbms_output.put_line( utl_raw.cast_to_varchar2( as_zip.get_file( t_dir, t_zip, zip_files( i ) ) ) );
end loop;
end;
*/
end;
/
CREATE OR REPLACE package body as_zip
is
--
function raw2num(
p_value in raw
)
return number
is
begin -- note: FFFFFFFF => -1
return utl_raw.cast_to_binary_integer( p_value
, utl_raw.little_endian
);
end;
--
function file2blob(
p_dir in varchar2
, p_file_name in 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
);
dbms_lob.loadfromfile( file_blob
, file_lob
, dbms_lob.lobmaxsize
);
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;
--
function raw2varchar2(
p_raw in raw
, p_encoding in varchar2
)
return varchar2
is
begin
return nvl
( 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 get_file_list(
p_dir in varchar2
, p_zip_file in varchar2
, p_encoding in varchar2 := null
)
return file_list
is
begin
return get_file_list( file2blob( p_dir
, p_zip_file
)
, p_encoding
);
end;
--
function get_file_list(
p_zipped_blob in blob
, p_encoding in varchar2 := null
)
return file_list
is
t_ind integer;
t_hd_ind integer;
t_rv file_list;
begin
t_ind := dbms_lob.getlength( p_zipped_blob ) - 21;
loop
exit when dbms_lob.substr( p_zipped_blob
, 4
, t_ind
) = hextoraw( '504B0506' )
or t_ind < 1;
t_ind := t_ind - 1;
end loop;
--
if t_ind <= 0
then
return null;
end if;
--
t_hd_ind := raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_ind + 16
) ) + 1;
t_rv := file_list( );
t_rv.extend( raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_ind + 10
) ) );
for i in 1 .. raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_ind + 8
) )
loop
t_rv( i ) :=
raw2varchar2
( dbms_lob.substr( p_zipped_blob
, raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 28
) )
, t_hd_ind + 46
)
, p_encoding
);
t_hd_ind :=
t_hd_ind
+ 46
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 28
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 30
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 32
) );
end loop;
--
return t_rv;
end;
--
function get_file(
p_dir in varchar2
, p_zip_file in varchar2
, p_file_name in varchar2
, p_encoding in varchar2 := null
)
return blob
is
begin
return get_file( file2blob( p_dir
, p_zip_file
)
, p_file_name
, p_encoding
);
end;
--
function get_file(
p_zipped_blob in blob
, p_file_name in varchar2
, p_encoding in varchar2 := null
)
return blob
is
t_tmp blob;
t_ind integer;
t_hd_ind integer;
t_fl_ind integer;
begin
t_ind := dbms_lob.getlength( p_zipped_blob ) - 21;
loop
exit when dbms_lob.substr( p_zipped_blob
, 4
, t_ind
) = hextoraw( '504B0506' )
or t_ind < 1;
t_ind := t_ind - 1;
end loop;
--
if t_ind <= 0
then
return null;
end if;
--
t_hd_ind := raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_ind + 16
) ) + 1;
for i in 1 .. raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_ind + 8
) )
loop
if p_file_name =
raw2varchar2
( dbms_lob.substr( p_zipped_blob
, raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 28
) )
, t_hd_ind + 46
)
, p_encoding
)
then
if dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 10
) = hextoraw( '0800' ) -- deflate
then
t_fl_ind :=
raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_hd_ind + 42
) );
t_tmp := hextoraw( '1F8B0800000000000003' ); -- gzip header
dbms_lob.copy( t_tmp
, p_zipped_blob
, raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_fl_ind + 19
) )
, 11
, t_fl_ind
+ 31
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_fl_ind + 27
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_fl_ind + 29
) )
);
dbms_lob.append( t_tmp
, dbms_lob.substr( p_zipped_blob
, 4
, t_fl_ind + 15
)
);
dbms_lob.append( t_tmp
, dbms_lob.substr( p_zipped_blob, 4, t_fl_ind + 23 )
);
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 :=
raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_hd_ind + 42
) );
return dbms_lob.substr( p_zipped_blob
, raw2num( dbms_lob.substr( p_zipped_blob
, 4
, t_fl_ind + 19
) )
, t_fl_ind
+ 31
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_fl_ind + 27
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_fl_ind + 29
) )
);
end if;
end if;
t_hd_ind :=
t_hd_ind
+ 46
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 28
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 30
) )
+ raw2num( dbms_lob.substr( p_zipped_blob
, 2
, t_hd_ind + 32
) );
end loop;
--
return null;
end;
--
function little_endian(
p_big in number
, p_bytes in pls_integer := 4
)
return raw
is
begin
return utl_raw.substr
( utl_raw.cast_from_binary_integer( p_big
, utl_raw.little_endian
)
, 1
, p_bytes
);
end;
--
procedure add1file(
p_zipped_blob in out blob
, p_name in varchar2
, p_content in blob
)
is
t_now date;
t_blob blob;
t_clen integer;
begin
t_now := sysdate;
t_blob := utl_compress.lz_compress( p_content );
t_clen := dbms_lob.getlength( t_blob );
if p_zipped_blob is null
then
dbms_lob.createtemporary( p_zipped_blob
, true
);
end if;
dbms_lob.append
( p_zipped_blob
, utl_raw.concat
( hextoraw( '504B0304' ) -- Local file header signature
, hextoraw( '1400' ) -- version 2.0
, hextoraw( '0000' ) -- no General purpose bits
, hextoraw( '0800' ) -- deflate
, 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
, dbms_lob.substr( t_blob
, 4
, t_clen - 7
) -- CRC-32
, little_endian( t_clen - 18 ) -- compressed size
, little_endian( dbms_lob.getlength( p_content ) )
-- uncompressed size
, little_endian( length( p_name )
, 2
) -- File name length
, hextoraw( '0000' ) -- Extra field length
, utl_raw.cast_to_raw( p_name ) -- File name
)
);
dbms_lob.copy( p_zipped_blob
, t_blob
, t_clen - 18
, dbms_lob.getlength( p_zipped_blob ) + 1
, 11
); -- compressed content
dbms_lob.freetemporary( t_blob );
end;
--
procedure finish_zip(
p_zipped_blob in out blob
)
is
t_cnt pls_integer := 0;
t_offs integer;
t_offs_dir_header integer;
t_offs_end_header integer;
t_comment raw( 32767 )
:= utl_raw.cast_to_raw( 'Implementation by Anton Scheffer' );
begin
t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
t_offs := dbms_lob.instr( p_zipped_blob
, hextoraw( '504B0304' )
, 1
);
while t_offs > 0
loop
t_cnt := t_cnt + 1;
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( '0100' ) -- Internal file attributes
, hextoraw( '2000B681' ) -- External file attributes
, little_endian( t_offs - 1 )
-- Relative offset of local file header
, dbms_lob.substr
( p_zipped_blob
, utl_raw.cast_to_binary_integer
( dbms_lob.substr( p_zipped_blob
, 2
, t_offs + 26
)
, utl_raw.little_endian
)
, t_offs + 30
) -- File name
)
);
t_offs :=
dbms_lob.instr( p_zipped_blob
, hextoraw( '504B0304' )
, t_offs + 32
);
end loop;
t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
dbms_lob.append
( p_zipped_blob
, utl_raw.concat
( hextoraw( '504B0506' ) -- 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 )
-- Relative offset of local file header
, little_endian
( nvl( utl_raw.length( t_comment )
, 0
)
, 2
) -- ZIP file comment length
, t_comment
)
);
end;
--
procedure save_zip(
p_zipped_blob in blob
, p_dir in varchar2 := 'ORA_DIR'
, p_filename in varchar2 := 'TEST.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;
/
Why Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE