Research on the use of bfile fields in Oracle

Oracle
Because of the project, you need to use the bfile field to store image files, so some research has been done.


bfile Field The actual file is stored in the file system, and the file location pointer is stored in the field. bfile to



Oracle is read-only and does not participate in transactional control and data recovery



The basic operations of bfile are as follows:





1. First in the Oracle database, we create a directory alias that maps the file location pointer to the text



Parts System:



Create DIRECTORY ' tmpdir ' as '/tmp ';

Tmpdir represents the logical directory name, '/tmp ' is the actual directory. Note that the directory Oracle should have Read permission



and authorize it as needed.

GRANT READ on DIRECTORY Bfile_dir1 to Scott;

Create a table with bfile fields

CREATE TABLE Bfiletest (ID number (3), fname bfile);

Create a table with BLOB fields


CREATE TABLE BLOBTest (ID number (3), Ablob blob);

2. Inserting data

Here you need to use Bfilename for INSERT or update operations on the bfile field



INSERT into Bfiletest

VALUES (1, bfilename (' Tmpdir ', ' tmptest '));



The Bfilename parameter 1 is the directory name, and parameter 2 is the filename. Note: This line is inserted in the



A file location pointer mapping that points to/tmp/tmptest, not the file itself.



3. Read bfile



You can read the bfile to the Blob/clob object (read-only) by using the Dbms_lob package

CREATE OR REPLACE PROCEDURE Loadlobfrombfile_proc (
TID in Number,rfilename, Varchar2,rfiledir in Varchar2,upmessage out varchar2)
As
Dest_loc BLOB;
Src_loc BFILE;
BEGIN
INSERT into BLOBTest (Id,ablob) VALUES (Tid,empty_blob ()) return ablob into Dest_loc;

Src_loc: = Bfilename (Rfiledir,rfilename);


/* If bfile file is actually present * *
IF (Dbms_lob. FileExists (Src_loc)!= 0)
THEN
/* Open bfile Source file * *
Dbms_lob. OPEN (Src_loc, Dbms_lob. LOB_READONLY);
/* Open Target BLOB: * *
Dbms_lob. OPEN (Dest_loc, Dbms_lob. Lob_readwrite);
/* Load from File/*
Dbms_lob. LoadFromFile (Dest_loc, Src_loc,dbms_lob. GetLength (Src_loc));
/* Remember to close: * *
Dbms_lob. Close (Dest_loc);
Dbms_lob. Close (Src_loc);
COMMIT;
Upmessage: = ' 0 ';
End IF;
EXCEPTION
When others THEN
ROLLBACK;
Upmessage: = ' operation failed ';

End;