This article presents simple methods to write or read CLOB to/from file using following packages:

• DBMS_XSLPROCESSOR
• DBMS_LOB

Prepare testing data

create test user TEST_USER in database

GRANT CONNECT TO test_user IDENTIFIED BY test1234;
GRANT CREATE ANY DIRECTORY TO test_user;
GRANT CREATE PROCEDURE TO test_user;
GRANT EXECUTE ON DBMS_LOB TO test_user;
GRANT EXECUTE ON DBMS_XSLPROCESSOR TO test_user;

create directory in Unix

[oracle@oel6 ~]$ mkdir /home/oracle/test_dir

create two sample files:

• empty_file.txt – empty file
• file.txt – file with some data

[oracle@oel6 ~]$ cd /home/oracle/test_dir
[oracle@oel6 test_dir]$ touch empty_file.txt
[oracle@oel6 test_dir]$ echo "simple text" > file.txt
[oracle@oel6 test_dir]$ ls
empty_file.txt  file.txt

logon to database as TEST_USER

sqlplus> CONNECT test_user/test1234

create Oracle directory

CREATE DIRECTORY test_dir AS '/home/oracle/test_dir';

Package DBMS_XSLPROCESSOR

Simple function to read file content into CLOB using DBMS_XSLPROCESSOR

create or replace FUNCTION fn_read_file_to_clob
(
  in_dir_name    IN VARCHAR2,
  in_file_name   IN VARCHAR2
)
RETURN CLOB
IS
  l_clob1      CLOB;
BEGIN
  l_clob1 := dbms_xslprocessor.read2clob(in_dir_name, in_file_name);
  
  RETURN l_clob1;
END fn_read_file_to_clob;
/

test

SELECT fn_read_file_to_clob('TEST_DIR', 'file.txt') clob_data
  FROM dual;

CLOB_DATA            
---------------------
simple text

unfortunately this function fails when it tries to read empty file

SELECT fn_read_file_to_clob('TEST_DIR', 'empty_file.txt') clob_data
  FROM dual;

SQL Error: ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 978
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 289
ORA-06512: at "TEST_USER.FN_READ_FILE_TO_CLOB", line 10

This problem can be addressed by adding EXCEPTION handler – not presented here

Here is simple procedure to write CLOB to file using DBMS_XSLPROCESSOR

CREATE OR REPLACE PROCEDURE pro_write_clob_to_file
(
  in_dir_name     IN VARCHAR2,
  in_file_name    IN VARCHAR2,
  in_clob         IN CLOB
)
AS 
BEGIN
  dbms_xslprocessor.clob2file
  (
    in_clob, 
    in_dir_name, 
    in_file_name
  );
END PRO_WRITE_CLOB_TO_FILE;
/

test

--write to new file
BEGIN
  pro_write_clob_to_file('TEST_DIR', 'new_file.txt', 'new data');
END;
/

SELECT fn_read_file_to_clob('TEST_DIR', 'new_file.txt') clob_data
  FROM dual;

CLOB_DATA           
--------------------
new data

--overwrite old file
BEGIN
  pro_write_clob_to_file('TEST_DIR', 'file.txt', 'overwrite data');
END;
/

SELECT fn_read_file_to_clob('TEST_DIR', 'file.txt') clob_data
  FROM dual;

CLOB_DATA           
--------------------
overwrite data

unfortunately it fails for NULL clob

BEGIN
  pro_write_clob_to_file('TEST_DIR', 'file.txt', NULL);
END;
/

ORA-06502: PL/SQL: numeric or value error: 
           invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 991
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 324
ORA-06512: at "TEST_USER.PRO_WRITE_CLOB_TO_FILE", line 9

Package DBMS_LOB

Function to read file content into CLOB using DBMS_LOB

create or replace FUNCTION fn_read_file_to_clob
(
  in_dir_name    IN VARCHAR2,
  in_file_name   IN VARCHAR2
)
RETURN CLOB
IS
  l_bfile      BFILE;
  l_clob       CLOB  := empty_clob();
  l_clob1      CLOB;
  l_wrn        INT;
  l_src_off    INT :=1;
  l_dest_off   INT :=1;
  l_lang_ctx   INT :=0;
BEGIN
  l_bfile := bfilename(in_dir_name, in_file_name);
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);

  IF dbms_lob.getlength(l_bfile) > 0 THEN
    dbms_lob.createtemporary(l_clob, TRUE);
    dbms_lob.loadclobfromfile(l_clob, l_bfile,
                              dbms_lob.getlength(l_bfile),
                              l_src_off, l_dest_off,
                              0,l_lang_ctx, l_wrn);
    l_clob1 := l_clob;
    dbms_lob.freetemporary(l_clob);
  END IF;

  dbms_lob.fileclose(l_bfile);
  
  RETURN l_clob1;
END fn_read_file_to_clob;
/

Test

SELECT fn_read_file_to_clob('TEST_DIR', 'new_file.txt') clob_data
  FROM dual;

CLOB_DATA            
---------------------
new data

--it works for empty files s well :)

SELECT fn_read_file_to_clob('TEST_DIR', 'empty_file.txt') clob_data
  FROM dual;

CLOB_DATA              
---------------------

Simple procedure to write CLOB into a file using DBMS_LOB

create or replace PROCEDURE pro_write_clob_to_file
(
  in_dir_name    IN VARCHAR2,
  in_file_name   IN VARCHAR2,
  in_clob        IN CLOB
)
IS
  l_file    utl_file.file_type;
  l_amt     NUMBER := 32000;
  l_offset  NUMBER := 1;
  l_length  NUMBER := nvl(dbms_lob.getlength(in_clob), 0);
  l_buff    VARCHAR2(32760);
BEGIN
  --open file
  l_file := utl_file.fopen(in_dir_name, in_file_name, 'w', 32760);
 
  --read clob and upload into file
  while (l_offset < l_length) 
  loop
    dbms_lob.READ(in_clob, l_amt, l_offset, l_buff);
    
    utl_file.put(l_file, l_buff);
    utl_file.fflush(l_file);
    utl_file.new_line(l_file);
    
    l_offset := l_offset + l_amt;
  END LOOP;
  
  --close file
  utl_file.fclose(l_file);
END pro_write_clob_to_file;
/

Test

BEGIN
  pro_write_clob_to_file('TEST_DIR', 'file.txt', 'simple data');
END;
/

SELECT fn_read_file_to_clob('TEST_DIR', 'file.txt') clob_data
  FROM dual;

CLOB_DATA               
------------------------
simple data

--works for nulls as well
BEGIN
  pro_write_clob_to_file('TEST_DIR', 'empty_file.txt', null);
END;
/

SELECT fn_read_file_to_clob('TEST_DIR', 'empty_file.txt') clob_data
  FROM dual;

CLOB_DATA               
------------------------

Have a fun 🙂
Tomasz