- 3,708,899 Users
- 2,241,152 Discussions
- 7,840,694 Comments
Forum Stats
Discussions
Categories
- 99 Community Feedback - NEW! (No Product Questions)
- 43 General Community Platform Concerns/Kudos/Feedback
- 37 Community Platform Bug Reports
- 55 How Do I? (Community Platform "How to" Questions)
- 11 Where is the...? (Community Platform Locations)
- 9 I'd Like to See... (Community Platform Ideas & Suggestions)
- 11.2K Community Feedback (No Product Questions) Archive
- 64 Personal Document & Blog Archive
- 2 Community Programs
- 2 Get-Togethers
- 56 Certification Community
- 4.7K Certification Community Discussions
- 31 Oracle Certified Master Profiles
- 25 Oracle Database 12c Administrator Certified Master Profiles
- 28 Visual Builder Cloud Service
Export CLOB

How to write clob to file.
Best Answer
-
Anand Pandya Posts: 29
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 := 32000;
l_pos INTEGER := 1;
l_new_pos iNTEGER :=1;
l_count number :=1;
l_total_length number:=0;
BEGIN
l_total_length:= DBMS_LOB.getlength(p_clob);
l_file := UTL_FILE.fopen(p_dir, p_filename, 'w',32767);
LOOP
DBMS_LOB.read (p_clob, l_amount, l_new_pos, l_buffer);
l_pos:=INSTR(l_buffer,'
',-1);
if l_pos = 0 then
L_POS:=l_total_length;
end if;
UTL_FILE.put(l_file, SUBSTR(l_buffer,1,L_POS));
utl_file.fflush(l_file);
l_new_pos:=l_new_pos+L_POS;
l_count:=l_count+1;
IF l_pos = l_total_length THEN
EXIT;
END IF;
END LOOP;
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
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;
/
Answers
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 := 32000;
l_pos INTEGER := 1;
l_new_pos iNTEGER :=1;
l_count number :=1;
l_total_length number:=0;
BEGIN
l_total_length:= DBMS_LOB.getlength(p_clob);
l_file := UTL_FILE.fopen(p_dir, p_filename, 'w',32767);
LOOP
DBMS_LOB.read (p_clob, l_amount, l_new_pos, l_buffer);
l_pos:=INSTR(l_buffer,'
',-1);
if l_pos = 0 then
L_POS:=l_total_length;
end if;
UTL_FILE.put(l_file, SUBSTR(l_buffer,1,L_POS));
utl_file.fflush(l_file);
l_new_pos:=l_new_pos+L_POS;
l_count:=l_count+1;
IF l_pos = l_total_length THEN
EXIT;
END IF;
END LOOP;
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
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;
/