BFILE Function Example

Use BFILE to extract and reload LOB data.

Create table LobA:

create table LobA
  (rowid  int primary key,
    col1   clob null,
    col2   blob null)

Assume LobA has two rows of data.

Extract the non-LOB data and the paths to the files into which the LOB data is extracted:

BEGIN
  SET TEMPORARY OPTION 
    Temp_Extract_Name1 = LobA_data.txt';
  SELECT rowid,
    'row' + string(rowid) + '.' + 'col1',
    'row' + string(rowid) + '.' + 'col2'
  FROM LobA;
END

The file LobA_data.txt is created and contains this non-LOB data and these filenames:

  1,row1.col1,row1.col2,
  2,row2.col1,row2.col2,

Perform the LOB data extraction:

SELECT
  BFILE('row' + string(rowid) + '.' + 'col1',col1),
  BFILE('row' + string(rowid) + '.' + 'col2',col2)
FROM LobA;

After the extraction, there is a file for each cell of LOB data extracted. For example, if table LobA contains two rows of data with rowid values of 1 and 2, you have these files:

row1.col1
row1.col2
row2.col1
row2.col2

Reload the extracted data:

LOAD TABLE LobA
(rowid,
  col1 ASCII FILE (',') NULL('NULL'),
  col2 BINARY FILE (',') NULL('NULL'))
FROM LobA_data.txt'
DELIMITED BY ','
ROW DELIMITED BY '\n'
ESCAPES OFF;