Re: Mass load of BLOBs

"Howard J. Rogers" <howardjr_at_www.com> wrote

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

Why not opt for using BFILEs instead? You keep the photo's on CDROM. You create a directory object in Oracle that points to it. You create a table with a BFILE column (and other descriptive text). You then simply insert the names of the files into this column (done via a SQL*Plus script that simply takes a list of filenames and add that into the table).

To retrieve the data from the file via the column value, you use the DBMS_LOB package. It is pretty easy to write a PL/SQL user function that can be used in a SELECT statement to return the contents of a BFILE as a blob (which means Delphi/VB clients can view it as an image using their normal binary lob functions).

The advantage of such a schema is that you can easily store images and photos on CD (protected media) instead of inside Oracle. Not having it in Oracle, means that your backup overheads are also many times smaller and less complex. In addition, unlike having these images "embedded/locked" in a Oracle data file, the files are still easy accessible in its original format from any other application.

--
Billy