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;
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;