Import a file to an Oracle BLOB field

Create test directories and files on the database host

$mkdir/test

$CD/test

$echo "Test Subject" >> subject.html

$echo "Test ok!" >> mail.html

Defines the file path (which is all on the database host) and authorizes

$sqlplus User/[email protected]

Sql>create or replace directory Send_file_dir as '/test ';

Sql>grant Read on directory send_file_dir to Test1;

The following is the insertion of content into the mailing table (there are two BLOB fields in the table Subject,message)

$sqlplus User/[email protected]

Sql>declare

Destlocblob;

Destmsg blob;

Srcfilebfile:=bfilename (' Send_file_dir ', ' subject.html ');

Msgfilebfile:=bfilename (' Send_file_dir ', ' mail.html ');

BEGIN

INSERT into email_send values (seq_email_id.nextval,null, ' [email protected] ', Null,null,empty_blob (), Empty_blob (), Sysdate,null, ' Waiting ', 0, ' HTML ', null)

Returning subject,message into destloc,destmsg;--here if you have more than one BLOB field to add.

Dbms_lob.fileopen (Srcfile);

Dbms_lob.loadfromfile (Destloc,srcfile,dbms_lob.getlength (srcfile));

Dbms_lob.fileclose (Srcfile);

Dbms_lob.fileopen (Msgfile);

Dbms_lob.loadfromfile (Destmsg,msgfile,dbms_lob.getlength (msgfile));

Dbms_lob.fileclose (Msgfile);

Commit

END;

/

This inserts two BLOB fields into the table email_send.

Subject field content is subject.html

Message field content is mail.html

Error message

ERROR at line 1:

Ora-22288:file or LOB operation FILEOPEN failed

No such file or directory

Ora-06512:at "SYS. Dbms_lob ", line 805

Ora-06512:at Line 9

Workaround

These files are on the database host and will be error-free if none of the files are on the host. Create directories and files on the host is OK, I have not found a way to read files from the remote, if any brother know, tell the next, thank you!!

This article is from the "11617433" blog, please be sure to keep this source http://11627433.blog.51cto.com/11617433/1897300

Import a file to an Oracle BLOB field