Writing a large text value to a file used to take a UTL_FILE loop over chunks of the CLOB. DBMS_LOB.CLOB2FILE does it in one call: give it the CLOB, a directory object, and a file name, and it writes the whole value to a file on the database server.
Code for This Guide
The main examples are in the examples/pkg-dbms-lob folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_lob.clob2file(cl => clob, flocation => 'DIRECTORY', fname => 'file name',
csid => 0, openmode => 'wb');CSID 0 writes in the database character set; pass another character set ID to convert. OPENMODE 'wb' replaces the file and 'ab' appends to it.
The examples write to the NIMBUS_FILES directory, which the NIMBUS setup creates.
Write a CSV File
Example:
declare
v_report clob;
begin
select listagg(airport_code || ',' || city, chr(10)) within group (order by airport_code)
into v_report
from airports where country_code = 'IN';
dbms_lob.clob2file(v_report, 'NIMBUS_FILES', 'indian_airports.csv');
end;
/
select dbms_lob.getlength(bfilename('NIMBUS_FILES',
'indian_airports.csv')) as bytes_written;Output:
PL/SQL procedure successfully completed.
BYTES_WRITTEN
________________
34LISTAGG builds a CSV of the airports in India as one CLOB, and CLOB2FILE writes it as indian_airports.csv. Reading the file length through a BFILE confirms 34 bytes were written.
Write, Check, and Remove
Example:
declare
v_report clob := 'code,city' || chr(10) || 'DXB,Dubai' || chr(10) || 'SIN,Singapore';
begin
dbms_lob.clob2file(v_report, 'NIMBUS_FILES', 'hubs.csv');
end;
/
-- read the file back
select to_clob(bfilename('NIMBUS_FILES', 'hubs.csv'), nls_charset_id('AL32UTF8'))
as contents;
exec utl_file.fremove('NIMBUS_FILES', 'hubs.csv')Output:
PL/SQL procedure successfully completed. CONTENTS ________________ code,city DXB,Dubai SIN,Singapore PL/SQL procedure successfully completed.
TO_CLOB reads the file back through BFILENAME to show its contents, and UTL_FILE.FREMOVE deletes it.
Things to Know
- Writing needs WRITE on the directory object, and the file is written by the database server's operating system user.
- Lines end with whatever the CLOB contains: add CHR(10), or CHR(13) || CHR(10) for Windows tools.
- To write a BLOB, such as an image, open the file with UTL_FILE in binary mode instead.
Related Guides
Conclusion
DBMS_LOB.CLOB2FILE writes a whole CLOB to a server file in one call, in the database character set or another one you name. Use it for exports and reports, and check the result by reading the file back through a BFILE.
