Re: Loading BLOB / CLOB

Thank you for your efforts,

well I know of this method loading a file from a directory on database host. Unfortunately There are only few people that have direct access to the filesystem of the database host. I want to enable clients all over the site to load data from there filesystems into the database. So I wanted to write a tool that reads a steam of data from the local disk and pipes it into an Oracle blob, or function or another thingy. Is such a technique possible?

Thanks Thomas

"Daniel Morgan" <dmorgan_at_exesolutions.com> schrieb im Newsbeitrag news:3CD711DE.ABEB4D27_at_exesolutions.com...
> Thomas Ruschival wrote:
>
> > Hi,
> >
> > I don't know, didn't find the right information about "how to load LOB
> > (BLOB/CLOB) into the Database".
> > OK, I have binary Data and Character in Files > 500 MB. How can I load
this
> > stream into a BLOB Field in the Database?
> > - BTW I can't use SQLJ or any Java classes because there is no JServer
> > option on this Oracle 8.1.7 Instance.
> >
> > Thanks alot
> >
> > Thomas
>
> I've been accused lately of not being helpful so I've decided to turn over
a
> new leaf.
>
> Here the BLOB loading demo straight from my University of Washington web
site:
>
> -- define the directory inside Oracle when logged on as SYS
> CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp\';
>
> -- grant read on the directory to the Dev schema
> GRANT READ ON DIRECTORY ctemp TO dev;
>
> -- the storage table for the image file
> CREATE TABLE lob_demo (
> file_name VARCHAR2(30), -- directory name
> image BLOB, -- image file
> comments CLOB) -- formatted text
> tablespace ?????;
>
> -- create the procedure to load the file
> CREATE OR REPLACE PROCEDURE load_file (fname IN VARCHAR2) IS
>
> src_file BFILE;
> dst_file BLOB;
> lgh_file BINARY_INTEGER;
>
> BEGIN
> src_file := bfilename('CTEMP', fname);
>
> -- insert a NULL record to lock
> INSERT INTO lob_demo
> (file_name, image)
> VALUES
> (fname, EMPTY_BLOB())
> RETURNING image INTO dst_file;
>
> -- lock record
> SELECT image
> INTO dst_file
> FROM lob_demo
> WHERE file_name = fname
> FOR UPDATE;
>
> -- open the file
> dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
>
> -- determine size
> lgh_file := dbms_lob.getlength(src_file);
>
> -- read the file
> dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
>
> -- update the blob field
> UPDATE lob_demo
> SET image = dst_file
> WHERE file_name = fname;
>
> -- close file
> dbms_lob.fileclose(src_file);
>
> COMMIT;
> END load_file;
> /
>
> Daniel Morgan
>
Received on Tue May 07 2002 - 01:57:09 CDT