Re: Loading BLOB / CLOB

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 Mon May 06 2002 - 18:29:41 CDT