Help - Search - Members - Calendar
Full Version: utl_file length issue
Oracle DBA Forums > Oracle > Oracle Forum
ashish1
Hi All,

I have an issue in writing flat file for more than 32767 bytes..

here is my source code.. please suggest ..Can it be modified that 32768th byte is written in next line and so on..

please help

CODE
declare
cursor c1 is  select XDATA from  t1 where
v2 CLOB;
v_file UTL_FILE.FILE_TYPE;
count1 number :=1;
begin
v_file:=utl_file.fopen('DIR','F1','w',32767);
for c2 in c1
loop
count1 :=count1 +1;
v2:=c2.xdata.getclobval();
utl_file.put_line (v_file,v2);
end loop;
utl_file.fclose(v_file);
count1:= count1 -1;
end;


thanks a lot
HAL9000
Try this approach:

http://forums.oracle.com/forums/thread.jsp...&msRange=15

CODE
procedure put_clob
( pi_file in utl_file.file_type
, pi_clob in clob
)
is
--
xbuf clob;
pos number := 0;
numinstr number;
begin

xbuf := pi_clob;
-- homage to Kevin Loney via PLVprs for this:
numinstr :=
(
LENGTH ( xbuf ) -
NVL ( LENGTH ( REPLACE ( xbuf , chr ( 10 ) ) ) , 0)
);
--
for i in 1..numinstr loop
pos := instr ( xbuf, chr(10));
-- need to test for and handle pos > 32k which
-- will cause a failure of UTL_FILE.
put_row ( pi_file
, substr ( xbuf , 1, pos-1 )
, true );
xbuf := substr ( xbuf , pos+1 , length ( xbuf ));
--
end loop;
--DBMS_OUTPUT.PUT_LINE ( xhold );
end;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.