BFILE datatype

BFILE Datatype
The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. BFILEs can store up to 8 terabytes of data.

BFILEs are read only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. The underlying operating system must maintain the file integrity, security, and durability for BFILEs. The database administrator must ensure that the file exists and that Oracle processes have operating-system read permissions on the file.

SQL> --Create logical directory


SQL> create directory DATA as 'c:\lob';

Directory created.


SQL> --Grant READ,WRITE privs to PUBLIC

SQL> grant read,write on directory data to public;

Grant succeeded.


SQL> --Create Table to store pointer for large binary objects


SQL> create table bfileobj ( id number, type varchar2(20), image bfile);

Table created.


SQL> --Insert locator/populate table


SQL> insert into bfileobj values (&n,'&a',bfilename('DATA','&A'));
Enter value for n: 1
Enter value for a: winRAR
Enter value for a: 1.rar
old 1: insert into bfileobj values (&n,'&a',bfilename('DATA','&A'))
new 1: insert into bfileobj values (1,'winRAR',bfilename('DATA','1.rar'))

1 row created.

SQL> commit;

Commit complete.


SQL> --Select Table ( First Create function)


SQL> create or replace function get_filename( p_bfile in bfile ) return
2 varchar2
3 as
4 l_dir varchar2(4000);
5 l_fname varchar2(4000);
6 begin
7 dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
8 return l_dir ':' l_fname;
9 end;
10 /

Function created.

SQL> column fname format a30
SQL> select id,type, get_filename( image ) fname from bfileobj;

ID TYPE FNAME
---------- -------------------- ------------------------------
1 winRAR DATA:1.rar
2 Windows Media DATA:2.wmv
3 PowerPoint DATA:3.pps
4 Adobe Acrobat DATA:4.pdf
5 jpeg image DATA:5.jpg




SQL> -- Check BFILE is exists or not on physical location



SQL> set serveroutput on
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure fileexists ( nn number) is
2 file_loc bfile;
3 begin
4 dbms_output.put_line ( '----------bfile fileexists example --------');
5 select image into file_loc from bfileobj
6 where id = nn;
7 if (dbms_lob.fileexists(file_loc) != 0)
8 then
9 dbms_output.put_line('Processing given that the bfile exists');
10 else
11 dbms_output.put_line('Processing given that the bfile does not exists');
12 end if;
13 exception
14 when others then
15 dbms_output.put_line('operation failed');
16* end;
SQL> /

Procedure created.

SQL> exec fileexists(1);
----------bfile fileexists example --------
Processing given that the bfile exists

PL/SQL procedure successfully completed.

SQL> exec fileexists(5);
----------bfile fileexists example --------
Processing given that the bfile does not exists

PL/SQL procedure successfully completed.




SQL> --Display data from bfile

SQL> insert into bfileobj values (7,'WORD DOC',bfilename('DATA','7.DOC'));

1 row created.

SQL> commit;

Commit complete.


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE displayBFILE ( nn varchar2)IS
2 file_loc BFILE := BFILENAME('DATA', nn);
3 Buffer RAW(1024);
4 Amount BINARY_INTEGER := 200;
5 Position INTEGER := 1;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('------------ BFILE DISPLAY EXAMPLE ------------');

8 /* Opening the BFILE: */
9 DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY);
10 LOOP
11 DBMS_LOB.READ (file_loc, Amount, Position, Buffer);
12 /* Display the buffer contents: */
13 DBMS_OUTPUT.PUT_LINE(substr(utl_raw.cast_to_varchar2(Buffer), 1, 250)
);
14 Position := Position + Amount;
15 END LOOP;
16 /* Closing the BFILE: */
17 DBMS_LOB.CLOSE (file_loc);
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 DBMS_OUTPUT.PUT_LINE('End of data');
21* END;
SQL> /

Procedure created.

SQL> exec displaybfile('6.log');

PL/SQL procedure successfully completed.

SQL> set serveroutput on size 1000000
SQL> exec displaybfile('6.log');

------------ BFILE DISPLAY EXAMPLE ------------

Starting up ORACLE RDBMS Version: 10.1.0.5.0.
System parameters with
non-default values:
processes = 150
sga_target
= 285212672
control_files = C:\O
RACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
db_block_size
= 8192
compatib
le = 10.1.0.5.0
db_file_multiblock_read_count= 16

db_recovery_file_dest = C:\oracle\product\10.1.0\flash_recovery_area

db_recovery_file_dest_size= 2147483648
undo_manageme
nt = AUTO
undo_tablespace = UNDOTBS1

remote_login_passwordfile= EXCLUSIVE
db_domain =

dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_q
ueue_processes = 10
background_dump_dest =
C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\BDUMP
user_dump_dest =
C:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\UDUMP
core_dump_dest = C
:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\CDUMP
db_name = orcl

open_cursors = 300
pga_aggregate_target = 94371840

End of data


PL/SQL procedure successfully completed.


SQL> --Getting the length of a bfile


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE getLengthBFILE ( nn varchar2) IS
2 file_loc BFILE := BFILENAME('DATA',nn);
3 Length INTEGER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('------------ BFILE LENGTH EXAMPLE ------------');
6 /* Open the BFILE: */
7 DBMS_LOB.OPEN(file_loc, DBMS_LOB.LOB_READONLY);
8 /* Get the length of the LOB: */
9 Length := DBMS_LOB.GETLENGTH(file_loc);
10 IF Length IS NULL THEN
11 DBMS_OUTPUT.PUT_LINE('BFILE is null.');
12 ELSE
13 DBMS_OUTPUT.PUT_LINE('The length is ' length);
14 END IF;
15 /* Close the BFILE: */
16 DBMS_LOB.CLOSE(file_loc);
17* END;
SQL> /

Procedure created.

SQL> exec getlengthbfile('6.log');
------------ BFILE LENGTH EXAMPLE ------------
The length is 1150

PL/SQL procedure successfully completed.

SQL> exec getlengthbfile('1.rar');
------------ BFILE LENGTH EXAMPLE ------------
The length is 6953658

PL/SQL procedure successfully completed.


Note: you can display only text file.

-------------------------------------------------------------------------

BLOB datatype

In above we see how to store pdf,txt file through BFILE datatype now in this section we use BLOB datatype to store those files.

1. Create directory for store pdf,txt files which we want to store in database

create or replace directory MY_FILES as 'e:\';

2. Create table in database which store target files

create table demo (id number primary key, theBlob BLOB);

3. Write Small plsql for upload pdf,txt etc files from OS to Database

declare

l_blob blob;

l_bfile bfile;

begin

insert into demo values(1,empty_blob() )

returning theBlob into l_blob;

l_bfile := bfilename ('MY_FILES','test1.pdf');

dbms_lob.fileopen(l_bfile);

dbms_lob.loadfromfile(l_blob,l_bfile,

dbms_lob.getlength(l_bfile));

dbms_lob.fileclose(l_bfile);

end;

4. Check file is upload successfully?

SQL> column theblob format a30

SQL> select * from demo;
ID THEBLOB

---------- ------------------------------

1 255044462D312E330D332030206F62 6A0D3C3C2F54797065202F50616765 0D2F506172656E742031203020520D 2F5265736F75726365732032203020 520D2F436F6E74656E747320342030

declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values(2,empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename ('MY_FILES','11gDBA.pdf');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_blob,l_bfile,
dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end;