Re: Mass load of BLOBs

Howard,

I suggest using sqlldr to load the blobs. It is simpler, little faster and you can do it from a remote computer. For PL/SQL BLOB loading, the files need to be on the server (?).
So if you decide on using sqlldr, here is how to do it. NOTE: Though this example is for a table with a single column (blob column), other cases just need a little tweaking.

Assume:
Photo Dir is d:\photoalbum

Step 1. Get the dir listing and store it in a file (files.lst). Get full path listing.

             In DOS: dir /B /S d:\photoalbum\*.* > files.lst

Step 2. Create control file (loadblob.ctl)

              LOAD DATA
              INFILE files.lst
              INTO TABLE album
              (ext_fname FILLER  CHAR(200),
                photo  LOBFILE(ext_fname) TERMINATED BY EOF)

Step 3.  Run sqlldr
              sqlldr  userid=scott/tiger_at_avarma control=loadblob.ctl

Step 4. Check loadblob.log for any errors encountered!! If you want you can check the table also if the blobs were loaded correctly

            select dbms_lob.getlength(blobcol) from blobtab; ... and you are done!

... for future loads .. you'll just need to update the files.lst table

... You can automate the step 1 -3 by putting them in a bat/cmd file.

Anurag

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3b8f675c_at_news.iprimus.com.au...
> Any one got a PL/SQL procedure that would scan the contents of
D:\PhotoAlbum
> and load into a table ALBUM contain a single column PHOTO (blob)?
>
> Sorry, but my PL/SQL's not up to it, and I'm not going to sit there
> inserting all 10,000 photos one-by-one!
>
> Regards
> HJR
>
>
>
>
Received on Fri Aug 31 2001 - 11:47:42 CDT