Re: Blob

[Quoted] Yes. You can do it easily with PL/SQL using the DBMS_LOB package.

Here is some sample code that does it ... the only precursor is creating the directory within Oracle and granting read on it. For example:

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp\';

Then grant permissions to the schema:

GRANT READ ON DIRECTORY ctemp TO schema_name;

Here's a simple procedure that reads a file in the c:\temp directory and loads it into a BLOB field.

CREATE OR REPLACE PROCEDURE load_cad (drawname VARCHAR2) IS

src_file   BFILE;
dst_file   BLOB;
lgh_file   BINARY_INTEGER;

BEGIN
   src_file := bfilename('CTEMP', drawname);

• insert a record with a NULL BLOB to lock INSERT INTO cadtest (cadfile, caddraw) VALUES (drawname, EMPTY_BLOB()) RETURNING caddraw INTO dst_file;
• lock record SELECT caddraw INTO dst_file FROM cadtest WHERE cadfile = drawname FOR UPDATE;
• open the file dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
• determine length lgh_file := dbms_lob.getlength(src_file);
• read the file dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
• update the blob field UPDATE cadtest SET caddraw = dst_file WHERE cadfile = drawname;
• close file dbms_lob.fileclose(src_file);

END load_cad;
/

I hope this helps.

Daniel Morgan

Carmine wrote:

> hi friends!!
>
> I would, please, like to know how can i upload a binary file in my
> database!...
> for example if I have a file named "image.gif"in this URL" c:\images"and i
> want to insert it in my DB.
> i thank you for your help.
Received on Mon Apr 15 2002 - 17:21:31 CEST