Working With Bfiles

Managing Bfiles

Manipulating Large Objects

A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so

on. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data can

be thousands of times larger. Also, multimedia data may reside in operating system (OS) files, which may need to be

accessed from a database.


There are four large object data types:
BLOB represents a binary large object, such as a video clip.
CLOB represents a character large object.
NCLOB represents a multibyte character large object.
BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a

file locator that points to the external file.

A table can have multiple LOB columns and object type attributes. A table can have only one LONG column.
The maximum size of LONGs is 2 GB; LOBs can be up to 4 GB.

There are two distinct parts to a LOB:
LOB value: The data that constitutes the real object being stored
LOB locator: A pointer to the location of the LOB value stored in the database.



What Are BFILEs?
BFILEs are external large objects (LOBs) stored in OS files that are external to database tables. The BFILE data type

stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.

Securing BFILEs
Unauthenticated access to files on a server presents a security risk.Oracle Database 10g can act as a security mechanism

to shield the operating system from unsecured access while removing the need to manage additional user accounts on an

enterprise computer system.


A DIRECTORY is a nonschema database object that enables administration of access and usage of BFILEs in Oracle Database

10g.


Example :-


create table emp as select * from employees;
ALTER TABLE emp ADD (photo blob);
UPDATE emp
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
--The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object).
--. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a --valid BLOB locator.
SET photo = empty_blob( )
CREATE OR REPLACE DIRECTORY photo_folder AS 'F:\students:'
CONNECT sys as sysdba
GRANT CREATE ANY DIRECTORY TO Hr;

GRANT DROP ANY DIRECTORY TO hr;
connect hr/hr
CREATE OR REPLACE DIRECTORY photo_folder AS 'f:\students'

-----------------------------------------------
CREATE OR REPLACE PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE employee_id = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;

EXECUTE insert_photo(198, '1.GIF')
EXECUTE insert_photo(124, '1.GIF')

SELECT employee_id,first_name,
dbms_lob.getlength(photo) "Photo Size"
FROM emp