Re: Loading BLOB / CLOB

Thomas Ruschival wrote:

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

To enable clients to load directly create a directory on the server into which they can FTP their files.

Daniel Morgan Received on Tue May 07 2002 - 10:07:11 CDT