Skip to Main Content
  • Questions

  • creating text files from the clob column in table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ashish.

Asked: September 12, 2000 - 7:33 am UTC

Last updated: August 28, 2012 - 12:59 pm UTC

Version: 1.8.5

Viewed 50K+ times! This question is

You Asked

HI TOM,

Thanks for the advice with the same. Yesterday I had told about my requirement of creating text files from the data stored in a clob column in one of the table. From the reference code in one of the questions i tried the following procedure.

The table structure is like:
Table test_tab
( col_id number(10) primary key,
col_text clob not null)

The code fragment is:
create or replace procedure dump_table_to_file(p_dir in varchar2
,p_file_extn in varchar2 default '.txt'
,p_col_id in number default null)
is
l_output utl_file.file_type;
l_status integer;

l1 number;
l2 clob;

txt varchar2(32767);
amount binary_integer := 32767;
position integer := 1;
offset integer := 1;

cursor c_1 is select col_id
,col_text
from test_tab
where col_id = nvl(p_col_id, col_id);
BEGIN
open c_1;
loop
fetch c_1 into l1, l2;
exit when c_1%notfound;
loop
l_output := utl_file.fopen(p_dir, l1||p_file_extn, 'w', 32767);
loop
dbms_lob.read(l2,amount,offset,txt);
offset := offset + amount;
utl_file.put(l_output, txt);
end loop;
utl_file.new_line(l_output);
utl_file.fclose(l_output);
end loop;
end loop;
close c_1;
EXCEPTION
when others then
if c_1%isopen
then
close c_1;
end if;
dbms_output.put_line(sqlerrm);
END;

The problem is it will create a file with the col_id name but the file doesn't have any contents. Only the file is created. Is anything going wrong. PLease advice.

Thanks a lot,

ashish


and Tom said...

The logic in your loop is wrong. dbms_lob.read throws NO_DATA_FOUND when you go beyond the end of the lob. That takes us out of the loop down to the exception handler after the very first lob is processed. It also skips right over the utl_file.fclose -- hence the file buffers never get flushed.

Since writing the clob to a file is a good "generic" sort of routine, I would write it as such -- no need to tie it to this table and this table only. Also, since I like cursor for loops over explicit cursors in every single case -- I'll do it that way as well (less code to write, easier to debug, faster as well)

Here is how I might approach this:

ops$tkyte@DEV816> create or replace
2 procedure clob_to_file( p_dir in varchar2,
3 p_file in varchar2,
4 p_clob in clob )
5 as
6 l_output utl_file.file_type;
7 l_amt number default 32000;
8 l_offset number default 1;
9 l_length number default
nvl(dbms_lob.getlength(p_clob),0);
10 BEGIN
11 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
12 while ( l_offset < l_length )
13 loop
14 utl_file.put(l_output,
15 dbms_lob.substr(p_clob,l_amt,l_offset) );
16 utl_file.fflush(l_output);
17 l_offset := l_offset + l_amt;
18 end loop;
19 utl_file.new_line(l_output);
20 utl_file.fclose(l_output);
21 end;
22 /

Procedure created.

ops$tkyte@DEV816> create table test_tab ( col_id number, col_text clob );

Table created.

ops$tkyte@DEV816> declare
2 l_col_text clob;
3 begin
4 for i in 1..5 loop
5 insert into test_tab values
6 ( i, empty_clob() )
7 returning col_text into l_col_text;
8 for i in 1 .. 10 loop
9 dbms_lob.writeappend( l_col_text, 30001,
10 rpad('*',30000,'*') || chr(10) );
11 end loop;
12 end loop;
13 end;
14 /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> create or replace
2 procedure dump_table_to_file
3 (p_dir in varchar2,
4 p_file_extn in varchar2 default '.txt',
5 p_col_id in number default null)
6 is
7 BEGIN
8 for x in ( select *
9 from test_tab
10 where col_id = nvl(p_col_id,col_id) )
11 loop
12 clob_to_file( p_dir,
13 x.col_id || p_file_extn,
14 x.col_text );
15 end loop;
16 END;
17 /

Procedure created.

ops$tkyte@DEV816> exec dump_table_to_file( '/tmp' );

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> host ls -l /tmp/?.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/1.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/2.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/3.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/4.txt
-rw-r--r-- 1 ora816 300011 Sep 12 08:00 /tmp/5.txt

ops$tkyte@DEV816> select dbms_lob.getlength(col_text) from test_tab;

DBMS_LOB.GETLENGTH(COL_TEXT)
----------------------------
300010
300010
300010
300010
300010

ops$tkyte@DEV816>

Which is exactly what we expected. You'll get an exception on the fclose if your text does not have a newline every 32k.

Rating

  (25 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Creating Text File From Review

Angelica Campbell, July 02, 2002 - 5:26 pm UTC

The response was useful in the fact that it proved to me that a file could be generated from a clob. However I did not understand the last statement that you made re: You'll get an exception on the fclose if your text does not have a newline every 32k.

In my case I am combining several clobs of variable length which may or may not be greater than 32k. The final clob will be used to create a document which will be used as a file attachment. Would I have to parse the new clob and add a newline every 32k and if so, how?

Tom Kyte
July 02, 2002 - 7:47 pm UTC 

if the ASCII newline character (chr(10)) does not appear at least every 32k bytes -- utl_file will fail.  That means if you try to write 32k+1 byte of text that doesn't have a newline in it you'll get an exceptoin.  For example, consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_output                utl_file.file_type;
  3          l_data          varchar2(32000) default rpad( '*', 32000, '*' );
  4  begin
  5          l_output := utl_file.fopen( '/tmp', 'foo.txt', 'w', 32765 );
  6          utl_file.put( l_output, l_data );
  7          utl_file.fclose( l_output );
  8  end;
  9  /

PL/SQL procedure successfully completed.
<b>that works, less then 32k</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_output                utl_file.file_type;
  3          l_data          varchar2(32000) default rpad( '*', 32000, '*' );
  4  begin
  5          l_output := utl_file.fopen( '/tmp', 'foo.txt', 'w', 32765 );
  6          utl_file.put( l_output, l_data );
  7          utl_file.put( l_output, chr(10) );
  8          utl_file.put( l_output, l_data );
  9          utl_file.fclose( l_output );
 10  end;
 11  /

PL/SQL procedure successfully completed.
<b>that works, 32,000 bytes -- newline -- 32,000 bytes</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_output                utl_file.file_type;
  3          l_data          varchar2(32000) default rpad( '*', 32000, '*' );
  4  begin
  5          l_output := utl_file.fopen( '/tmp', 'foo.txt', 'w', 32765 );
  6          utl_file.put( l_output, l_data );
  7          utl_file.put( l_output, l_data );
  8          utl_file.fclose( l_output );
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 103
ORA-06512: at "SYS.UTL_FILE", line 222
ORA-06512: at line 8

<b>that does NOT work 32,000 bytes -- NO newline -- 32,000 bytes</b>


If your clob might contain a stream of characters over 32k in length that do not contain a newline, you will get that error.  That is what I meant.

Yes, you would have to make sure a newline appears AT LEAST every 32k. How?  That is up to you (knowledge of the file you are producing, the data going into it and what is allowable in the output file necessary in order to answer that part of the question)

Extracting line by line

Abdel-Rahman Agha, February 03, 2003 - 1:09 pm UTC

Hi Tom,
I have a problem when I design a text item of type long based on a clob column, that I need to extract the text filled in this item line by line. If the line contains a linefeed or cariage return, no problem I could extract this line before the linefeed supposed that the width not exceed specific number say 80. But if the line wrap to the next one, I will not find any linefeed or cariage return. How can I take this line just written in one line as it appears?

Tom Kyte
February 03, 2003 - 1:23 pm UTC

? don't understand.

this sounds like a "client" issue doesn't it? I mean, the database just stores whatever you put in there

How about read file

A reader, January 08, 2004 - 9:27 pm UTC

Hi, Tom,

Does this "32k in length that do not contain a newline, you will get that error" suitable for reading a file?
Another words, what about the file need to be read contains a line of 32K length without new line, is it a problem?

THanks

Tom Kyte
January 09, 2004 - 8:17 am UTC

right now, utl_file has a 32k linesize limit, yes. reading and writing a "line" of text >32k will not work. You'll have to use a BFILE to read that file in chunks instead.

reader, March 24, 2004 - 3:38 am UTC

dear sir ,
i want to create that clob to txt file at the web client machine
not at client server machine.
how can i do that ?

Tom Kyte
March 24, 2004 - 9:01 am UTC

write a program that runs on the client, since only a program on the client can write to the client machine.

reader, March 24, 2004 - 12:29 pm UTC

Sir ,
is that mean no way to make the web client able to get that text file ?!
it's hard soluation to make a program and install it on each web client , iam talking about 100s clients



Tom Kyte
March 24, 2004 - 1:25 pm UTC

do you have "a browser"

that is client enough -- </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:232814159006#6891533553691 <code>

Whats wrong?

VA, March 16, 2005 - 10:16 pm UTC

I gleaned the following code from your site to parse a CSV file in a BLOB

It goes into an infinite loop when it encounters a empty line in the file.

How can I skip empty lines? Thanks

PROCEDURE select_file(p_file_id IN NUMBER)
IS
l_blob blob;
l_last pls_integer;
l_current pls_integer;
l_record varchar2(32767);
l_array wwv_flow_global.vc_arr2;
l_dummy NUMBER;
l_job_id binary_integer;
l_rec_no pls_integer := 0;
l_record_separator VARCHAR2(2) := chr(13)||chr(10);
BEGIN
BEGIN
select blob_content into l_blob from wwv_flow_files
where file_id=p_file_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20000,'File not found, id='||p_file_id);
END;

-- Sigh, stupid DOS/Unix newline stuff. If HTMLDB has generated the file,
-- it will be a Unix text file. If user has manually created the file, it
-- will have DOS newlines.
-- If the file has a DOS newline (cr+lf), use that
-- If the file does not have a DOS newline, use a Unix newline (lf)
IF (NVL(dbms_lob.instr(l_blob,utl_raw.cast_to_raw(l_record_separator),1,1),0)=0)
THEN
l_record_separator := chr(10);
END IF;

l_last := 1;
LOOP
l_current := dbms_lob.instr( l_blob, utl_raw.cast_to_raw(l_record_separator), l_last, 1 );
exit when (nvl(l_current,0) = 0);
l_record := utl_raw.cast_to_varchar2(dbms_lob.substr(l_blob,l_current-l_last,l_last));
csv_to_array(l_record,l_array);
l_rec_no := l_rec_no+1;

-- Skip the first record with column headings
IF (l_rec_no!=1)
THEN
<do whatever you want with the columns in l_array>
END IF;
l_last := l_current+length(l_record_separator);
END LOOP;


EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20001,'File format not recognized');
END select_file;



Tom Kyte
March 17, 2005 - 8:29 am UTC

and you cannot debug this?

Debug

A reader, March 17, 2005 - 9:01 am UTC

No. I have stared at this for a long time but nothing is jumping out! I cant figure out why it just keeps spinning wheels when it comes across \r\n\r\n!

Thanks

Tom Kyte
March 17, 2005 - 9:34 am UTC

so, litter the code with dbms_output.put_lines and run it.

*debug* it. don't compile it in your head, debug it.

A reader, March 17, 2005 - 9:40 am UTC

Um, not to put too fine a point on it, but dbms_output.put_line doesnt give me anything until the procedure finishes executing. This one doesnt! It goes in a infinite loop. If do a Ctrl-C on it, I dont get my dbms_outputs. If I do dbms_application_info.set_client_info(), I get only my last output!

Got it

A reader, March 17, 2005 - 10:00 am UTC

I put a autonomous transaction in my proc to log to a table and saw it.

l_current was the same as l_last for the blank line so dbms_lob.substr() was getting the 'amount' parameter as 0 so I guess the results are undefined.

I will fix this. Thanks!

File to CLOB to Screen

Kim, December 20, 2006 - 11:06 pm UTC

10gR2

say I have a nicely formatted XML file that I want to CLOB it
How can I display (put_line) it "line-by-line" exactly as if I vi that XML file ?
thanks

DECLARE
l_xmlclob CLOB := EMPTY_CLOB;
l_bfile bfile;
l_length number ;
l_amt number default 80;
l_offset number default 1;

BEGIN
dbms_lob.createtemporary(l_xmlclob, TRUE);
l_bfile := bfilename( 'YOUR_DIR', 'yourfile.xml');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_xmlclob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
l_length := nvl(dbms_lob.getlength(l_xmlclob),0);
while ( l_offset < l_length )
loop
dbms_output.put_line(dbms_lob.substr(l_xmlclob,l_amt,l_offset) );
l_offset := l_offset + l_amt;
end loop;
END;

Tom Kyte
December 22, 2006 - 5:56 am UTC 

just dbms_output.put_line it - assuming it has newlines.

ops$tkyte%ORA10GR2> create table t ( x clob );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values
  2  ( 'hello
  3  there
  4  this
  5  has
  6  newlines' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for c in (select * from t)
  3          loop
  4                  dbms_output.put_line( c.x );
  5          end loop;
  6  end;
  7  /
hello
there
this
has
newlines

PL/SQL procedure successfully completed.

Why I must flush after every put?

Zilvinas, April 24, 2009 - 8:13 am UTC

loop for saving file:
LOOP
Dbms_Lob.Read(SELF.File_Body, l_Length, l_Offset, l_Buffer);
Utl_File.Put(l_File, l_Buffer);
Utl_File.fflush(l_File);
l_Offset := l_Offset + l_Length;
END LOOP;

When File_Body is BLOB and l_Buffer is RAW everything works fine without Utl_File.Fflush(l_File);
But when File_Body is CLOB and l_Buffer is VARCHAR2 only 32k is writen to file. Is it a bug or is it a feature?
Tom Kyte
April 27, 2009 - 12:19 pm UTC

the maximum (documented) linesize of a utl_file text file is 32k. If you do not hit an end of line marker before that - you will get an error.

Nihal, May 07, 2009 - 11:34 pm UTC

Whats the way around it? i have a requirement to create a ascii file with data more than 32K and cant hv a new line feed as the recieving system does not accept it and can't be changed, and why does utl file always put a end of file, is there a way to "not" have a end of file character as well ?

I am using version 10.2
Tom Kyte
May 11, 2009 - 3:03 pm UTC

you will not be using PLSQL then - a java stored procedure or an external client will do it.

the UTL_FILE limit for text files is 32k, hard and fast.

Write CLOB to file

Marcus, May 12, 2009 - 3:50 am UTC

To write a clob to file without searching for line breaks you can use
utl_file.put_raw(v_fileHandle, utl_raw.cast_to_raw(v_clob));

Regards
Marcus
Tom Kyte
May 13, 2009 - 10:29 am UTC

do not recommend, explicitly did not suggest that. Plenty of character set issues in your future should you do that.

Also, did you actually try it with more than 32k? The cast_to_raw routine accepts a varchar2 (max length 32 in plsql). There is an implicit conversion of the clob to varchar2.

ops$tkyte%ORA10GR2> declare
  2          l_clob clob;
  3  begin
  4          l_clob := rpad('*',32000,'*') || rpad('!',32000,'!' );
  5          dbms_output.put_line( length( l_clob ) );
  6  end;
  7  /
64000

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_clob clob;
  3  begin
  4          l_clob := rpad('*',32000,'*') || rpad('!',32000,'!' );
  5          dbms_output.put_line( length( utl_raw.cast_to_raw(l_clob) ) );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5


you'd need to chunk it all up.

I'll stick with the original answer - java stored procedure or external client program.

cast_to_raw

Marcus, May 13, 2009 - 11:14 am UTC

Hello Tom,

to be honest this is not my idea but suggested by Metalink, see Doc ID 358422.1, 358641.1, 358781.1.

The doc for utl_raw says
The input value is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type RAW.

I expect the file to be written 1:1, independent from the charset (and until now I had no problems). Where do you expect problems?

Regards
Marcus

Tom Kyte
May 13, 2009 - 3:14 pm UTC

if you write a file in unicode, what are the first couple of bytes of it?


$ od -c test.txt
0000000 377 376   h  \0   e  \0   l  \0   l  \0   o  \0      \0   w  \0
0000020   o  \0   r  \0   l  \0   d  \0
0000030


or utf

$ od -c test.txt
0000000 357 273 277   h   e   l   l   o       w   o   r   l   d
0000016



would they be there *if* you did raw?


I'll stick with my original answer, still.

My test case

Marcus, May 15, 2009 - 3:47 pm UTC

Hello Tom,

it took some time to answer because I wanted to test a bit with different character encodings. Mostly because I have a book where the author (a Mr. Kyte) says not to trust an authority without proof. Therefore I set up a database with AL32UTF8 and loaded a textfile with some exotic characters in three different formats.

Clear text
Testdatei äöü? ¿¿¿¿¿¿¿¿
(Maybe the Ask-Tom does not support UTF-8 as the preview shows only questionmarks)

UTF-8 (HEX)
FF EE 54 00 65 00 73 00 74 00 64 00 61 00 74 00
65 00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 00
52 01 A3 03 CE 03 97 04 39 06 DE 06 DC FB FC FE


UTF-16 Big Endian without BOM (HEX)
00 54 00 65 00 73 00 74 00 64 00 61 00 74 00 65
00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 01 52
03 A3 03 CE 04 97 06 39 06 DE FB DC FE FC


Unicode ASCII Escapes
Testdatei äöü? \u0152\u03A3\u03CE\u0497\u0639\u06DE\uFBDC\uFEFC



CREATE TABLE t_clob(
     file_name  VARCHAR2(200)
    ,file_data  CLOB
    );

SQL> SELECT  file_name
  2         ,DUMP(TO_CHAR(file_data)) dump
  3  FROM    t_clob;

FILE_NAME
--------------------------------------------------------------------------------
DUMP
--------------------------------------------------------------------------------
test_UTF-8.txt
Typ=1 Len=36: 84,101,115,116,100,97,116,101,105,32,195,164,195,182,195,188,63,32
,197,146,206,163,207,142,210,151,216,185,219,158,239,175,156,239,187,188

test_UTF-16_BigEndian_ohneBOM.txt
Typ=1 Len=57: 0,84,0,101,0,115,0,116,0,100,0,97,0,116,0,101,0,105,0,32,0,239,191
,189,0,239,191,189,0,63,0,32,1,82,3,239,191,189,3,239,191,189,239,191,189,6,57,6
,239,191,189,239,191,189,239,191,189

test_Unicode_ASCII-Escaped.txt
Typ=1 Len=63: 84,101,115,116,100,97,116,101,105,32,239,191,189,63,32,92,117,48,4
9,53,50,92,117,48,51,65,51,92,117,48,51,67,69,92,117,48,52,57,55,92,117,48,54,51
,57,92,117,48,54,68,69,92,117,70,66,68,67,92,117,70,69,70,67

DECLARE
    v_clob                  CLOB;   
    v_filename              t_clob.file_name%TYPE;
    v_fileHandle            utl_file.FILE_TYPE;
BEGIN
    FOR r IN (
        SELECT  file_name
               ,file_data
        FROM    t_clob
        )
    LOOP    
  dbms_output.put_line(r.file_name);
        v_fileHandle := utl_file.fopen ('DOC', 'new_'||r.file_name, 'wb', 32767);
        utl_file.put_raw(v_fileHandle, utl_raw.cast_to_raw(v_clob));    
    END LOOP;
END;
/

The resulting files are byte by byte identically to the original files.

Not a proof for every situation but I think it shows, that put_raw does not change the content of the file.

Regards
Marcus


Tom Kyte
May 23, 2009 - 10:37 am UTC

did you notice that you did not create the files you wanted to create?????


do you or do you not have the byte headers required in the files of the type you are attempting to create?

Or, do you have in fact a binary file that is not useful?

Just asking....


put_raw DOES NOT change the contents of the file, therein lies the problem.

Marcus, May 31, 2009 - 9:13 pm UTC

Hello Tom,

first I have to say, that my previous test is not correct concerning the files encoded "UTF-16 Big Endian without BOM" and "Unicode ASCII Escaped", because the content is corrupted after reading the file. I used the default CSID of a UTF8 database to load ASCII files. Now I use 178 (WE8MSWIN1252).


do you or do you not have the byte headers required in the files of the type you are attempting to create?

The headers - yes, but when I write the files using utl_file.put I get a difference compared to the original files

UTF-8 (HEX) 
FF EE 54 00 65 00 73 00 74 00 64 00 61 00 74 00
65 00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 00
52 01 A3 03 CE 03 97 04 39 06 DE 06 DC FB FC FE
0D 00 0A 00


So there is a difference as put adds some bytes (the last four in my examples) and therefore changes the original files.
In this case it depends on what I want: put_raw to get the original file back or put and get an added EOF.

It gets even more complicated when you have ASCII files (see my failed first attempt). Though I found a way to load them correctly I don't know how to write them back, as put and put_raw only write a UTF8 encode file when used in a UTF8 database.
When searching for a solution I found another procedure and added it to my test: dbms_xslprocessor.clob2file. To use it I put a new column in my test table that contains the encoding of the original file as CSID.

Result
utl_file.put adds an EOF and you need line breaks if you want to write more than 32K.

ASCII files will be converted into the database encoding

UTF-16 Big Endian without BOM (HEX) 
00 54 00 65 00 73 00 74 00 64 00 61 00 74 00 65
00 69 00 20 00 E4 00 F6 00 FC 00 3F 00 20 01 52
03 A3 03 CE 04 97 06 39 06 DE FB DC FE FC

becomes a completely different text
00 54 00 65 00 73 00 74 00 64 00 61 00 74 00 65
00 69 00 20 00 C3 A4 00 C3 B6 00 C3 BC 00 3F 00
20 01 52 03 C2 A3 03 C3 8E 04 E2 80 94 06 39 06
C3 9E C3 BB C3 9C C3 BE C3 BC 0D 0A


So don't use it if you have a UTF8 database or anything else but plain ASCII.

utl_file.put_raw writes the file as is. So the output file will be encoded in UTF8 if the database is UTF8 yet it changed the "UTF-16 Big Endian without BOM" file in the same way as utl_file.put

dbms_xslprocessor.clob2file writes CLOBS loaded from files without adding EOF and with any target encoding. This will be my choice in the future: most flexible, least code to write..

Code used
DROP TABLE t_clob
/
CREATE TABLE t_clob(
     file_name  VARCHAR2(200)
    ,file_data  CLOB
    ,file_csid  INTEGER
    );


CREATE OR REPLACE PROCEDURE loadCLOB 
    (
     p_file_name    IN VARCHAR2 
    ,p_csid         IN INTEGER := 0
    ) 
AS

    v_clob          CLOB;
    v_src_file      BFILE;
    v_error_out     INTEGER;
    dest_offset     INTEGER := 1; 
    src_offset      INTEGER := 1;
    lang_context    INTEGER := dbms_lob.default_lang_ctx;

BEGIN
    INSERT INTO t_clob(
         file_name  
        ,file_data  
        ,file_csid    
        )
     VALUES (
         p_file_name
        ,NULL
        ,p_csid
        );

    v_src_file := bfilename( 'DOC', p_file_name );
    dbms_lob.fileOpen( v_src_file, dbms_lob.file_readonly );
    dbms_lob.loadClobFromfile(
         v_clob
        ,v_src_file
        ,dbms_lob.lobMaxSize
        ,dest_offset
        ,src_offset
        ,p_csid
        ,lang_context
        ,v_error_out 
        );
    dbms_lob.fileCloseAll() ;

    UPDATE  t_clob 
    SET     file_data = v_clob 
    WHERE   file_name = p_file_name;

END loadCLOB;
/
SHOW ERRORS

DECLARE
    v_tmp_clob  CLOB;
    v_file_name VARCHAR2(200) := 'test_UTF-8.txt';
BEGIN
    loadCLOB( 'test_UTF-8.txt',0);
    loadCLOB( 'test_UTF-16_BigEndian_ohneBOM.txt',178);
    loadCLOB( 'test_Unicode_ASCII-Escaped.txt',178);

END;
/

INSERT INTO t_clob(
     file_name  
    ,file_data  
    ,file_csid 
    )
 VALUES (
     'test_Unistr.txt'
    ,NULL
    ,0
    );

DECLARE
    v_tmp_clob  CLOB;
    v_file_name VARCHAR2(200) := 'test_Unistr.txt';
BEGIN
    dbms_lob.createTemporary( v_tmp_clob,TRUE,dbms_lob.session );
    v_tmp_clob := UNISTR('Testdatei \00E4\00F6\00FC? \0152\03A3\03CE\0497\0639\06DE\FBDC\FEFC');
    UPDATE  t_clob 
    SET     file_data = v_tmp_clob 
    WHERE   file_name = v_file_name;
    dbms_lob.freeTemporary( v_tmp_clob );
END;
/

COMMIT;

DECLARE
    v_clob                  CLOB;   
    v_filename              t_clob.file_name%TYPE;
    v_fileHandle            utl_file.FILE_TYPE;
BEGIN
    FOR r IN (
        SELECT  file_name
               ,file_data
               ,file_csid
        FROM    t_clob
        )
    LOOP    
        v_fileHandle := utl_file.fopen ('DOC', 'raw_'||r.file_name, 'wb', 32767);
        utl_file.put_raw(v_fileHandle, utl_raw.cast_to_raw(r.file_data));  
        utl_file.fclose(v_fileHandle);

        v_fileHandle := utl_file.fopen ('DOC', 'put_'||r.file_name, 'w', 32767);
        utl_file.put(v_fileHandle,r.file_data);    
        utl_file.fclose(v_fileHandle);

        dbms_xslprocessor.clob2file(r.file_data,'DOC','c2f_'||r.file_name,r.file_csid);
    END LOOP;
END;
/

SELECT  file_name
       ,file_data
       ,DUMP(TO_CHAR(file_data)) 
FROM    t_clob;

Tom Kyte
June 01, 2009 - 8:01 pm UTC

basically - it is what I said - you cannot do it

... So don't use it if you have a UTF8 database or anything else but plain ASCII. ....

pretty much sums it all up

Files

Marcus, June 01, 2009 - 4:45 am UTC

The files I used and generated can be found at http://matzberger.de/oracle/tkyte/clob_file.zip

It's not that easy

Marcus, June 05, 2009 - 5:14 am UTC

Hello Tom,

I don't think that don't use it is the solution. Because even when, as you suggested, another language is used to write the files the problem is still the same: What's the db encoding and whats the target encoding. If both are identical then utl_file.put_raw or dbms_xslprocessor.clob2file will do, no necessity for another language.

It's only difficult if they are not identical e.g. DB is not UTF8 and you want to write a NCLOB to target UTF8. Then you can either use another language or add the BOM (FF EE) to the output of utl_raw.cast_to_raw and then use utl_file.put_raw.

Oracle can do it, so why not use it?

Regards
Marcus

Tom Kyte
June 08, 2009 - 11:50 am UTC

I will never recommend doing this in plsql - plain and simple. A client program would be required to do this properly. A client program that knows how to generate the right kind of file and does not have the 32k limit per line.

it is actually quite that easy.

however

Marcus, June 09, 2009 - 2:56 am UTC

Hello Tom,

A client program that knows how to generate the right kind of file and does not have the 32k limit per line.

I don't think we will reach a consensus on this, but to me these are important points
1. more than once I had only the database to write files
2. the client programm won't do the encoding by magic
3. 32k is no issue for put_raw and clob2file

Regards
Marcus


Tom Kyte
June 09, 2009 - 10:23 am UTC

You have java in the database - it would be the more correct approach here- better overall file handling capabilities without the 32k issue.

utl_file for utf16

deba, November 12, 2009 - 8:25 am UTC

Hi Tom,

Is it true that utl_file can only be used to create UTF8 file ? I think I can also create UTF16 file also with proper BOM from nvarchar/nchar/nclob column where national character set is AL16UTF16 ( irrespective of my single byte database character set which is WE8ISO8859P1 ) ?

Thanks
Deba

utl_file for utf16

deba, November 17, 2009 - 6:05 am UTC

According to 10g manual, fopen_nchar : "This function opens a file in Unicode for input or output, with the maximum line size specified. You can have a maximum of 50 files open simultaneously. With this function, you can read or write a text file in Unicode instead of in the database charset"

But in 11G , bleow lines are added as extra.

Again according to 11g manual, fopen_nchar : "Even though the contents of an NVARCHAR2 buffer may be AL16UTF16 or UTF8 (depending on the national character set of the database), the contents of the file are always read and written in UTF8. UTL_FILE converts between UTF8 and AL16UTF16 as necessary"

I dont understand what is the meaning of "utl_file always read and write in UTF8 and converts as necessary".

It means that
1) can't I create utf16 file using utl_file in 11G ?
2) Don't we have to use convert function any more to convert data from utf16 to utf8 when we have UTF16 as national character set and want to write this data into utf8 encoding file ?

Please clear my doubt as I got stucked.

Thanks
Deba

Cut the coat to fit the cloth

Sam Allen, January 25, 2010 - 4:58 pm UTC

If one is converting a clob, which does not require a newline every 32k to a utl_file, which does require this; then; if one predicably placed a newline exactly every 32k, then one could then logically expect to find it in exactly that spot again when reading the file back into plsql and it can be easily discarded before piecewise processing is performed.

If you are going to take over with ..yet..another lower level package..then take over and stop using utl_file. I don't see that the choice of languages is at all relevant here.
Tom Kyte
January 29, 2010 - 8:39 am UTC

Sam - in general, when you create a file, you want the file to be a file, a file that anything can process.

Your suggestion would make a file that is processable only by code written specifically for that file. PLSQL would be an inappropriate language in this particular case.

(and then also ask yourself, is a newline 1 or 2 bytes? and what if you wrote 1 byte but then someone moved it to windows and it is now two? and ....)

AMC, August 29, 2010 - 11:57 am UTC

Hi Tom,
Its useful for my idea of implementation.
I have a query, created a cursor and inserted the result into CLOB and next line character as chr(10).Returned that CLOB to my DAO(in java). Read each line from CLOB and inserted into file (using java io).When I opened and checked, found data in a single line, rather than in different lines. Can u help me how to set new line to be read by java.

sailesh kumar, January 05, 2011 - 4:28 am UTC

Hi Tom,

I am working on email server of Informatica.
Any attachment in the email is stored as a clob object in the oracle database; with other column
like from,to,cc,bcc,subjectline,content.
We need to detach this attachment from the clob and make it into a file in unix.
Please advise me if it is possible

thanks



Tom Kyte
January 05, 2011 - 7:39 am UTC

please ask things once - go to the other place you posted this exact same question and see what I said there.

ORA-06512: at "SYS.UTL_FILE", line 183

Paul, August 20, 2012 - 10:28 am UTC

I am running a piece of code to extract data into a file. The developer that created the code, pretty much used your exact procedure.

The code generates over 23,000 files, but then errors out on the "UTL_FILE.fflush (l_output);" line.

ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 183
ORA-06512: at "SYS.UTL_FILE", line 1169
ORA-06512: at "TREAS_ADHOC_DATA_GAO.P_CLOB_TO_FILE", line 13
ORA-06512: at "TREAS_ADHOC_DATA_GAO.P_DUMP_TABLE_TO_FILE", line 11
ORA-06512: at "TREAS_ADHOC_DATA_GAO.P_GAO_GEN_FILE", line 10
ORA-06512: at line 2

Here is the whole procedure:
CREATE OR REPLACE PROCEDURE p_clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB)
AS
l_output UTL_FILE.file_type;
l_amt NUMBER DEFAULT 1000;
l_offset NUMBER DEFAULT 1;
l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);

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);
UTL_FILE.fclose (l_output);
END;
/

Tom Kyte
August 28, 2012 - 12:59 pm UTC

any chance you filled your file system?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here