Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29285 WRITE_ERROR (oracle 9.2.0.7)
ORA-29285 WRITE_ERROR [message #424107] Wed, 30 September 2009 04:45 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
_jum
Messages: 508
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 Go to previous message
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?
Previous Topic: How to chg a password in PL/SQL w/o using ALTER statement?
Next Topic: oracle 11g
Goto Forum:
  


Current Time: Tue Sep 27 23:01:28 CDT 2016

Total time taken to generate the page: 0.05393 seconds