Skip to content
November 9, 2012 / doganay

CLOB TO FILE

First create a directory:

SQL> create directory MYDIR as '/mydir';

create or replace
procedure clob_to_file (p_file in varchar2)
as
 l_output utl_file.file_type;
 l_amt number default 32000;
 l_offset number;
 l_length number;
 p_clob clob;
 p_dir varchar2(1024);
BEGIN
 p_dir:='MYDIR';
 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
 for col in (select col1 from mytable)
 loop
  l_offset:=1;
  select col_clob into p_clob from mytable where col1=col.col1;
  l_length:=nvl(dbms_lob.getlength(p_clob),0);
  while (l_offset < l_length)
  loop
   utl_file.put(l_output, dbms_lob.substr(p_clob, l_amt, l_offset));
   utl_file.fflush(l_output);
   l_offset := l_offset + l_amt;
  end loop;
  utl_file.new_line(l_output);
 end loop;
utl_file.fclose(l_output);
end;
/

or just execute following:

DBMS_XSLPROCESSOR.clob2file(buf, 'DIR_TMP', script_file);

Thanks to Bülent AKPINAR.

http://www.link2sd.info/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: