Home » SQL & PL/SQL » SQL & PL/SQL » Error in Writing FIle (Oracle 10g)
Error in Writing FIle [message #357152] Tue, 04 November 2008 03:04 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
DECLARE
 vInHandle utl_file.file_type;
BEGIN
  vInHandle := utl_file.fopen('SADAT', 'test.sql', 'W');
  FOR x IN (SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE seg_owner IN ('SADAT')) LOOP
  utl_file.put(vInHandle, x.sql_redo);
  END LOOP;
  utl_file.fclose(vInHandle);
END;
/


and I am getting the following error,
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 140
ORA-06512: at "SYS.UTL_FILE", line 379
ORA-06512: at "SYS.UTL_FILE", line 600
ORA-06512: at line 8


The string is a big one. so what could be the problem or how to solve it?

Re: Error in Writing FIle [message #357154 is a reply to message #357152] Tue, 04 November 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-29285: file write error
 *Cause:  Failed to write to, flush, or close a file.
 *Action: Verify that the file exists, that it is accessible, and that
          it is open in write or append mode.

Regards
Michel
Re: Error in Writing FIle [message #357162 is a reply to message #357152] Tue, 04 November 2008 03:16 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
The code is ok if I insert small string instead of x.sql_redo like

utl_file.put(vInHandle, 'sadat');
Re: Error in Writing FIle [message #357166 is a reply to message #357162] Tue, 04 November 2008 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Default max line size for utl_file is 1024 (in 10.2), sql_redo is varchar2(4000).
Have a look at open function in utl_file for your version.

Regards
Michel
Re: Error in Writing FIle [message #357170 is a reply to message #357152] Tue, 04 November 2008 03:34 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
Thank you for the helpful information. But is there any way to solve this without using substring function?
Re: Error in Writing FIle [message #357179 is a reply to message #357170] Tue, 04 November 2008 04:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Increase the linesize to the number of characters you think the linesize will be. Maximum allowed is 32767. And this is the last parameter in the fopen function.

Check this link for more information.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003526

Regards

Raj
Re: Error in Writing FIle [message #357181 is a reply to message #357152] Tue, 04 November 2008 04:06 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
wow!
thats a great solution.

Thanks to all....
Re: Error in Writing FIle [message #357189 is a reply to message #357181] Tue, 04 November 2008 04:37 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tanmoy1048 wrote on Tue, 04 November 2008 11:06
wow!
thats a great solution.

Thanks to all....

when someone says:
Quote:
Have a look at open function in utl_file for your version.

Maybe you should do it before reposting immediatly.

Regards
Michel

Previous Topic: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this
Next Topic: How to write output of a function returning sys_refcursor to a file
Goto Forum:
  


Current Time: Mon Dec 05 11:18:47 CST 2016

Total time taken to generate the page: 0.10042 seconds