External LOB BFILE Data types READ, DELETE, INSERT operations in Oracle

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;

1 thought on “External LOB BFILE Data types READ, DELETE, INSERT operations in Oracle

  1. Pingback: Example LOB DATA Type DML operation in Oracle | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.