Export CLOB
Related articles.
- Import BLOB Contents
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
The following article presents a simple method for exporting the contents of a CLOB datatype to the filesystem. First a directory object is created to point to the relevant filesystem directory.
CREATE OR REPLACE DIRECTORY documents AS 'C:\';
Then we read the contents of the CLOB and write them to a file.
SET SERVEROUTPUT ON DECLARE l_file UTL_FILE.FILE_TYPE; l_clob CLOB; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; BEGIN SELECT col1 INTO l_clob FROM tab1 WHERE rownum = 1; l_file := UTL_FILE.fopen('DOCUMENTS_DIR', 'Sample2.txt', 'w', 32767); LOOP DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer); UTL_FILE.put(l_file, l_buffer); l_pos := l_pos + l_amount; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN -- Expected end. IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; /
The process will always finish with a NO_DATA_FOUND
exception when the end of the CLOB is reached. For simplicity I've not trapped any of the other possible UTL_FILE
exceptions.
In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.
CREATE OR REPLACE PROCEDURE clob_to_file (p_clob IN CLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) AS l_file UTL_FILE.FILE_TYPE; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; BEGIN l_file := UTL_FILE.fopen(p_dir, p_filename, 'w', 32767); LOOP DBMS_LOB.read (p_clob, l_amount, l_pos, l_buffer); UTL_FILE.put(l_file, l_buffer); l_pos := l_pos + l_amount; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN -- Expected end. IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END clob_to_file; /
You might call this like the following.
DECLARE l_clob CLOB; BEGIN -- Get LOB locator SELECT col1 INTO l_clob FROM tab1 WHERE rownum = 1; clob_to_file(p_clob => l_clob, p_dir => 'DOCUMENTS_DIR', p_filename => 'Sample2.txt'); END; /
For more information see:
- Import BLOB Contents
- Export BLOB Contents (8i)
- Export BLOB Contents Using UTL_FILE
- Import CLOB Contents
- DBMS_LOB
- UTL_FILE
Hope this helps. Regards Tim...