- 3,726,961 Users
- 2,245,298 Discussions
- 7,852,511 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 5K All Categories
- 4 Introduce Yourself!
- 149 Community Feedback - NEW! (No Product Questions)
- 64 General Community Platform Concerns/Kudos/Feedback
- 52 Community Platform Bug Reports
- 65 How Do I Use the Community?
- 12 Where is the...? (Community Platform Locations)
- 9 Ideas and Suggestions for the Community Platform
- 65 Personal Document & Blog Archive
- 2 Community Programs
- 2 Get-Togethers
- 85 Certification Community
- 4.7K Certification Community Discussions
- 32 Oracle Certified Master Profiles
- 26 Oracle Database 12c Administrator Certified Master Profiles
- 32 Visual Builder Cloud Service
Export CLOB
How to write clob to file.
Best Answer
-
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;
/