Example of Read, Delete, insert on Binary File External LOB datatype in Oracle
Binary file lob datatype is stored in external storage only pointer is stored in Oracle Database.
It is also called as External LOB.
Note:
INSERT DML, Only the file pointer is stored into the table by BFILENAME function but file is stored in filesystem.
DELTE DML, Only delete the file pointer, the file is undeleted in the filesystem.
UPDATE DML, Only file pointer can be changed, the file is untouched.
We cannot update or modified the binary file, it is placed in Filesystem. We can read the file by converting into other lob format with help of DBMS_LOB package.
Following are the example of Insert, Read, Delete DML operation on Binary File LOB data type.
1. Create a Directory used by BFILE operations
--Check present directory and locations
SELECT * FROM DBA_DIRECTORIES;
CREATE DIRECTORY dir_name AS 'location';
Example:
CREATE DIRECTORY IMG_DIR AS 'C:\IMAGES';
Note: Permission need to user for create and drop directory
CREATE ANY DIRECTORY – for creating or altering the directory object creation
DROP ANY DIRECTORY – for deleting the directory object
2. Insert and update the table with BFILENAME function.
INSERT INTO lob_table VALUES (21, NULL, NULL, NULL, BFILENAME('IMG', 'image1.gif'));
UPDATE lob_table SET f_lob = BFILENAME('IMG', 'image3.gif') WHERE key_value = 22;
INSERT INTO lob_table VALUES (1002, NULL 'abcde',NULL,BFILENAME('SCOTT_DIR', 'scott.dat'));
Note:
Limited number of BFILEs can be open simultaneously per session
SESSION_MAX_OPEN_FILES=20
3. Read example of Binary file with PL/SQL packages DBMS_LOB.
set serveroutput on
Declare
-- Specify the Directory name
l_dir CONSTANT VARCHAR2(30) := 'TXT_DIR';
-- Specify the Input File name
l_fil CONSTANT VARCHAR2(30) := 'INIT.TXT';
-- Separator Character as BLANK (ascii = 32)
l_seb CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(32));
-- Character for the end of the file is NEWLINE (ascii = 10)
l_sen CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10));
-- Locator or Pointer to the BFILE
l_loc BFILE;
-- Starting position in the file
l_pos NUMBER := 1;
-- For characters to be read
l_sum BINARY_INTEGER := 0;
-- Read Buffer
l_buf VARCHAR2(5000);
-- End of the current word which will be read
l_end NUMBER;
-- Return value
l_ret integer := 0;
BEGIN
-- Set the pointer to the BFILE
l_loc := BFILENAME(l_dir,l_fil);
-- Check the file exists
l_ret := DBMS_LOB.FILEEXISTS(l_loc) ;
IF (l_ret = 1) THEN
dbms_output.put_line('File ' || l_fil || ' in Directory ' || l_dir || ' exists');
-- Open the file in READ_ONLY mode
DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
--Start reading the file
LOOP
-- Calculate the end of the current word
l_end := DBMS_LOB.INSTR(l_loc,l_seb,l_pos,1);
-- When end-of-file reached.
IF (l_end = 0) THEN
l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
l_sum := l_end - l_pos - 1;
--Read function
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
--Print the file
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
EXIT;
END IF;
-- Read until end-of-file reached.
l_sum := l_end - l_pos;
--Read function
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
--Print the file
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
l_pos := l_pos + l_sum + 1;
END LOOP;
--Close the file
DBMS_LOB.CLOSE(l_loc);
ELSE
dbms_output.put_line('File '|| l_fil || ' in Directory ' || l_dir || ' does not exist');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error:' || SQLERRM);
DBMS_LOB.CLOSE(l_loc);
END;
/
Note:
Syntax of DBMS_LOB.READ
dbms_lob.read(lob_loc IN BLOB, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT RAW);
4. Delete the Bfile or External LOBs.
Note: It only delete pointer from the database, operating system file is undeleted in filesystem.
DELETE FROM lob_table WHERE key_value = 21;
DROP TABLE lob_table;
TRUNCATE TABLE lob_table;
Smart way of Technology 
Pingback: Example LOB DATA Type DML operation in Oracle | Smart way of Technology