Hi Friends,
Can we load data in zipped files using sql loader.
Oracle version 8.1.7.4, sun solaris 2.8
regards
anandkl
Printable View
Hi Friends,
Can we load data in zipped files using sql loader.
Oracle version 8.1.7.4, sun solaris 2.8
regards
anandkl
Hi.
It depends what you mean.
1) If you want to load the whole zipped file into a single BLOB column, then yes you can.
2) If you want to unzip the files and load them as normal, then this can't be done using SQL*Loader alone. You need to write shell script to unzip the files then load them.
Cheers
Tim...
Hi Tim,
Thanks for the fast response.
I have a zip file which has 50 column values and i want to load this zip file without unziping it.
I want to know if this can be done so that i can save space by not unziping it. we are running short of space.
I have the script for unziping the file and then using sql loader to load the unziped file.
regards
anandkl
So you want to load the data that is within files within the zip file.
I don't know of a way to do that without extracting it first.
If you are a UNIX whizz there may be a way to pipe the extract directly into the SQL*Loader program, but I'm not so I don't. Sorry.
Cheers
Tim...
Hi Tim,
Thanks once again.
I am not a unix whizz, let me see if i can get help else where and will post it if i get a solution.
regards
anandkl
Never tried this with sqlloader, but we do something like this for importing compressed export dumps:
The imp command reads the pipe file, and uncompress gets input from exp.dmp.Z, uncompresses it and sends the output to pipe.dmp.Code:mknod pipe.dmp p
imp userid=/ fromuser=a touser=b file=pipe.dmp &
uncompress < $EXP_DIR/exp.dmp.Z > pipe.dmp &
wait
The pipe.dmp file is essentially a FIFO queue, with uncompress filling the queue and imp reading the queue. It is size limited (O/S dependent), and the writer pauses if the queue is filled while the reader thinks.
HTH
John
Hi John,
That was a good one.
I used the below and it worked for me.
gzcat ananda.txt.gz | sqlldr userid=ananda/ananda control=ananda_ldr.ctl log=input.log errors=10000000 bad=input.bad readsize
=2500000 rows= 50000 bindsize=2500000.
gzcat will not unzip but read the contents of the file and sqlldr will load each record read.
Regards
aandkl
Anandkl:
Glad I gave you a hint. I am surprised that sql loader would accept input from stdin. I would have thought that it would look for a physical file, even if only a pipe, but as long as it works.
John
Good one. Very useful to know.
yeah, I'm kinda surprised it works on stdin also.Quote:
Originally Posted by John Spencer