loading zip files.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: loading zip files.

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    loading zip files.

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

    Oracle version 8.1.7.4, sun solaris 2.8

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Never tried this with sqlloader, but we do something like this for importing compressed export dumps:

    Code:
    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

  7. #7
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    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

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Quote Originally Posted by 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
    yeah, I'm kinda surprised it works on stdin also.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    The joys of UNIX-style operating systems.

    There's always a way to do what you want. It just might take you forty years to fine the correct options

    The really sad thing is I've used that import method before (years ago) and I completely forgot. Chump.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you can check note 191043.1

    you can even define "-" as stdin in the controlfile then do

    Code:
    LOAD DATA
    INFILE "-"
    TRUNCATE
    INTO TABLE LDR_TEST
    FIELDS TERMINATED BY ";"
    (
      ID,
      TEXT
    )
    
    cat $1 | sqlldr scott/tiger control=LDR3.CTL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width