- Databases
- PL/SQL
- Oracle Database
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;
/
Where you have dbms_output.put_line, replace with UTL_FILE calls to open a new file and write it out to disk in chunks.
There are many links out there what have the code to write a BLOB to a file.
I still say doing this externally using dbms_scheduler to run the external script/program is a much better option here.
There are many links out there what have the code to write a BLOB to a file.
I still say doing this externally using dbms_scheduler to run the external script/program is a much better option here.
as suggested in previous question. Don't use pl/sql, since you want to process data from the os, use os tools. You could use dbms_scheduler, or just invoke an unzip tool with the external table preprocessor.
or, if you're going to through the bother of using Anton's code, then write it to a clob and parse the clob, eliminate the need for extra file access
or, if you're going to through the bother of using Anton's code, then write it to a clob and parse the clob, eliminate the need for extra file access
@slightwv : Can you please guide me on how to use this UTL file to export the unzipped file to the directory ?
Here is the plsql block that I am using :
And my zip file has
And I am able to display it but how ti implement the logic of UTL file to export it.
Here is the plsql block that I am using :
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 ) );
END LOOP;
end;
And my zip file has
test.txt
test2.txt
And I am able to display it but how ti implement the logic of UTL file to export it.
@sdstuber: Yes you are correct but my requirement is to get this done from PLSQL , for which I am try to get this.
>>Can you please guide me on how to use this UTL file to export the unzipped file to the directory ?
Google around on writing a BLOB to file. There are quite a few examples out there:
http://www.google.com/#hl=en&output=search&q=oracle+write+blob+to+file
Google around on writing a BLOB to file. There are quite a few examples out there:
http://www.google.com/#hl=en&output=search&q=oracle+write+blob+to+file
if the goal is to load the data and you must use pl/sql, then why do you want to write the unzipped files back to the os and then re-read the content again?
especially since your previous question says you're just going to delete the unzipped content anyway.
especially since your previous question says you're just going to delete the unzipped content anyway.
@sdstuber : We already have the procedure to load the text files but only issue is on the zip files.
If I use as_zip package I can view the contains of the file but each file can contain more than millions of records .
For which I want to load it individually.
If I use as_zip package I can view the contains of the file but each file can contain more than millions of records .
For which I want to load it individually.
right, so rather that write the data back to a file just so you can read it again, simply parse the data you already have in memory and write it to your tables.
that's not what this question is about though.
what I'm suggesting is to NOT follow through on your plan.
after all, if you DO get your files written, you'll still need to use utl_file to read them again, in which case, why bother?
or, you'll use sql*loader in which case you'll have to figure out how to invoke it from pl/sql with a dbms_scheduler job
or you'll use external tables, but, if you use external tables, then you're right back where I first suggested and that is don't use pl/sql to unzip the content. Let the external table do it for you
that's not what this question is about though.
what I'm suggesting is to NOT follow through on your plan.
after all, if you DO get your files written, you'll still need to use utl_file to read them again, in which case, why bother?
or, you'll use sql*loader in which case you'll have to figure out how to invoke it from pl/sql with a dbms_scheduler job
or you'll use external tables, but, if you use external tables, then you're right back where I first suggested and that is don't use pl/sql to unzip the content. Let the external table do it for you
To help draw the picture of what we are saying, here is how we understand your process.
Hopefully you will see that it really doesn't make much sense:
1: ZIP file is uploaded and stored on file system
2: Read it into Oracle memory using PL/SQL. Extract individual files.
3: Write individual files back to file system.
4: Read files from file system back into Oracle memory to parse them as a CSV.
5: Load into Oracle table
6: Remove individual files using PL/SQL.
7: Rename ZIP file using PL/SQL.
You are doing a LOT more "from OS to Oracle back to OS back to Oracle" than is necessary. Not to mention the memory Oracle will burn up holding these files in memory.
The proposed solution:
1: Unzip at OS
2: load files using sql loader
3: delete individual files
4: rename zip file
Very little memory usage on Oracle's parts with minimized disk IO.
Hopefully you will see that it really doesn't make much sense:
1: ZIP file is uploaded and stored on file system
2: Read it into Oracle memory using PL/SQL. Extract individual files.
3: Write individual files back to file system.
4: Read files from file system back into Oracle memory to parse them as a CSV.
5: Load into Oracle table
6: Remove individual files using PL/SQL.
7: Rename ZIP file using PL/SQL.
You are doing a LOT more "from OS to Oracle back to OS back to Oracle" than is necessary. Not to mention the memory Oracle will burn up holding these files in memory.
The proposed solution:
1: Unzip at OS
2: load files using sql loader
3: delete individual files
4: rename zip file
Very little memory usage on Oracle's parts with minimized disk IO.
actually, what I suggest is...
1 unzip and load via external table
2 delete individual files
3 rename zip file
under the covers, 1 is really the same as 1 and 2 in slightwv's suggestion, but as far as oracle syntax is concerned it's one operation.
1 unzip and load via external table
2 delete individual files
3 rename zip file
under the covers, 1 is really the same as 1 and 2 in slightwv's suggestion, but as far as oracle syntax is concerned it's one operation.
or, if you REALLY want a pl/sql solution...
1-read zip into blob
2-extract files individually into blobs
3-for each file load it into table (you'll only have one extracted file in memory at a time)
4-rename zip file
note, none of the recommendations actually answer the question here.
they indicate that any answer that actually does what you asked will be sub-optimal to what you are really trying to do
So, if it seems like we're trying to avoid answering your question, it's because we are.
We would both MUCH rather answer what you really want, than what you literally asked.
1-read zip into blob
2-extract files individually into blobs
3-for each file load it into table (you'll only have one extracted file in memory at a time)
4-rename zip file
note, none of the recommendations actually answer the question here.
they indicate that any answer that actually does what you asked will be sub-optimal to what you are really trying to do
So, if it seems like we're trying to avoid answering your question, it's because we are.
We would both MUCH rather answer what you really want, than what you literally asked.
>>if it seems like we're trying to avoid answering your question, it's because we are.
I think the Google link I posted in http:#a38305285 does answer the question asked. I just chose not to copy/paste WRITE_BLOB_TO_FILE code from the links into here since examples are already written by others.
I think the Google link I posted in http:#a38305285 does answer the question asked. I just chose not to copy/paste WRITE_BLOB_TO_FILE code from the links into here since examples are already written by others.
Thanks for all you comments.
I have a function which loads the txt files into my oracle database, as I asked in my earlier questions i.e. :
http://www.experts-exchange.com/Database/Oracle/Q_27830094.html
Here I am able to load the file and rename it too. (Where actually the file has to rename and kept over the same directory ).
But the issue was all the flat files are coming as into one zip file.
What I was trying to get is to unzip the files and then call my function which will load and rename the file. And finally delete zip file (its optional) .
The examples provided from links are good but here by clob is having 3 text files.
now if I need to implement the logic what was suggested then a new function or procedure has to be written where as I was trying to use the existing function on the code.
The only issue I am facing is to unzip the files in such a way that all the files will be unzipped on the same folder.
What I think is to write a shell script (on unix) or a batch script (on windows) which will unzip the file and then I can run my function to load it into the tables.
Is my way to get this done correct : Correct me if I am thinking wrongly.
I have a function which loads the txt files into my oracle database, as I asked in my earlier questions i.e. :
http://www.experts-exchange.com/Database/Oracle/Q_27830094.html
Here I am able to load the file and rename it too. (Where actually the file has to rename and kept over the same directory ).
But the issue was all the flat files are coming as into one zip file.
What I was trying to get is to unzip the files and then call my function which will load and rename the file. And finally delete zip file (its optional) .
The examples provided from links are good but here by clob is having 3 text files.
now if I need to implement the logic what was suggested then a new function or procedure has to be written where as I was trying to use the existing function on the code.
The only issue I am facing is to unzip the files in such a way that all the files will be unzipped on the same folder.
What I think is to write a shell script (on unix) or a batch script (on windows) which will unzip the file and then I can run my function to load it into the tables.
Is my way to get this done correct : Correct me if I am thinking wrongly.
writing the shell script would basically be slightwv's suggestion above.
I hope your function to load the files use sql*loader or external tables (same thing really)
if not, it's likely to be less efficient than it could be
I hope your function to load the files use sql*loader or external tables (same thing really)
if not, it's likely to be less efficient than it could be
>>unzip the files in such a way that all the files will be unzipped on the same folder.
I don't see an issue here. You control where the files are written. Don't you?
>>which will unzip the file and then I can run my function to load it into the tables.
In addition to sdstubers post above: You you are going to write a script to do half of it, why not do all of it and do it all more efficiently.
I realize that you already have code to parse the CSV, and I believe we discouraged you from writing it, but we have all written code that we had to throw away. Just because you have it, doesn't mean you have to use it.
I don't see an issue here. You control where the files are written. Don't you?
>>which will unzip the file and then I can run my function to load it into the tables.
In addition to sdstubers post above: You you are going to write a script to do half of it, why not do all of it and do it all more efficiently.
I realize that you already have code to parse the CSV, and I believe we discouraged you from writing it, but we have all written code that we had to throw away. Just because you have it, doesn't mean you have to use it.
