| ORA-29285 WRITE_ERROR [message #424107] |
Wed, 30 September 2009 04:45  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
I'm writing following block,
Begin
str := '...'
....
f1 := utl_file.Fopen('EXT_REP_DIR',b,'W',32767);
utl_file.Put(f1,str);
Loop
....
utl_file.Put(f1,str);
End Loop;
utl_file.Fclose(f1);
End
There would be around 100000 records and I want everything in 1 line(i.e no line breaks).
I'm getting error ORA-29285, WRITE_ERROR
By using PUT_LINE, the size of the file is for 10000 records is 3035542. But, I can not use PUT_LINE as this will put NEW_LINE at end of line.
Is there any other way ?
Brayan.
|
|
|
|
| Re: ORA-29285 WRITE_ERROR [message #424112 is a reply to message #424107] |
Wed, 30 September 2009 04:50  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
Try using utl_file.put_raw instead of utl_file.put - that can be set to automatically flush data to the file with every write.
|
|
|
|
| Re: ORA-29285 WRITE_ERROR [message #424136 is a reply to message #424112] |
Wed, 30 September 2009 05:42 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Using put_raw I'm getting same error. Put_line works fine.
str_raw := Utl_Raw.Cast_To_Raw (str) ;
Utl_File.Put_Raw (
file => f1,
buffer => str_raw,
autoflush => true );
Brayan.
|
|
|
|
| Re: ORA-29285 WRITE_ERROR [message #424172 is a reply to message #424136] |
Wed, 30 September 2009 07:19 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref11892
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
So You can't "PUT" more than 1024 bytes without buffer flush or change the buffer size. But the maximum is still 32767 Bytes.
Excuse me - if You open the file in mode 'wb', the AUTOFLUSH in PUT_RAW will work (Tested in ORACLE 10.2.0.1 WINDOWS):
filehandler := UTL_FILE.fopen(dir_name, 'test.txt', 'wb');
...
FOR i IN 0 .. 33000 LOOP
utl_file.Put_raw(filehandler,Utl_Raw.Cast_To_Raw (chr(mod(i,48)+48)),TRUE);
END LOOP;
[corrected mistake!]
[Updated on: Wed, 30 September 2009 07:45] Report message to a moderator |
|
|
|
| Re: ORA-29285 WRITE_ERROR [message #470413 is a reply to message #424107] |
Mon, 09 August 2010 16:02 |
beanburrito
Messages: 1
Registered: August 2010
|
Junior Member |
|
|
|
I tried this procedure and it works fine when running from TOAD on Windows. However, it still gives the ORA-29285 when running from SQL Plus on the unix system. I'm using all the parameter suggested autoflush=true and 32767 as the max line size. I'm writing a csv so I'm using utl_file.put_line. Any ideas?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
