Get BLOB from the BFILE Column in Oracle

In Oracle, the BFILE column is a locator or reference for the external file. It has the directory and filename information. Here I am giving an example of PL/SQL code to get BLOB from the BFILE locator column in Oracle.

PL/SQL Procedure Example - Get BLOB from BFILE Column

  l_bfile  BFILE;
  l_blob   BLOB;

  l_dest_offset INTEGER := 1;
  l_src_offset  INTEGER := 1;

  Select your_bfile_column into l_bfile from yourTable
    where yourCondition;
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadblobfromfile (
    dest_lob    => l_blob,
    src_bfile   => l_bfile,
    amount      => DBMS_LOB.lobmaxsize,
    dest_offset => l_dest_offset,
    src_offset  => l_src_offset);



The variable l_blob is containing the BLOB extracted from the BFILE column

Written by Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of and a question and answer forum for developers. You can connect with me on Facebook and Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *