loading zip files.

[anandkl]

Hi Friends,
Can we load data in zipped files using sql loader.

Oracle version 8.1.7.4, sun solaris 2.8

regards
anandkl

[TimHall]

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...

[anandkl]

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

[TimHall]

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...

[anandkl]

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

[John Spencer]

Never tried this with sqlloader, but we do something like this for importing compressed export dumps:

mknod pipe.dmp p
imp userid=/ fromuser=a touser=b file=pipe.dmp &
uncompress < $EXP_DIR/exp.dmp.Z > pipe.dmp &
wait

The imp command reads the pipe file, and uncompress gets input from exp.dmp.Z, uncompresses it and sends the output to pipe.dmp.

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

[anandkl]

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

[John Spencer]

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

[samy]

Good one. Very useful to know.

[marist89]

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

yeah, I'm kinda surprised it works on stdin also.