Home » SQL & PL/SQL » SQL & PL/SQL » utl_file write error
utl_file write error [message #216711] Tue, 30 January 2007 02:07 Go to next message
gautamsutar
Messages: 14
Registered: January 2007
Location: india
Junior Member
Hi

I created one pl/sql wherein i fetch the data using cursor from emp1 table (table contains only one field having width 100 chars) and I used one clob variable to append the records one by one and finally write the clob variable into utl_file.

The purpose is utl_file should give me only one record even though there are number of records in emp1 table.

my sql
------

declare
l_file UTL_FILE.FILE_TYPE;
cursor cur_emp is select field1 from emp1;
rec_emp emp%rowtype;
l_string clob := empty_clob();
begin
l_file := UTL_FILE.fopen('c:\temp','gfile.txt','w');
DBMS_LOB.CREATETEMPORARY(l_string,TRUE,DBMS_LOB.CALL);
for i in cur_emp
loop
DBMS_LOB.APPEND(l_string,i.field1);
end loop;
utl_file.put_line(l_file , l_string);
UTL_FILE.fclose(l_file);
end;

ERROR
------

declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 69
ORA-06512: at "SYS.UTL_FILE", line 604
ORA-06512: at line 16

Gautam



Re: utl_file write error [message #216713 is a reply to message #216711] Tue, 30 January 2007 02:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Make sure you have a C:\temp directory on the database server and that you have the necessary rights on that directory.

MHE
Re: utl_file write error [message #216714 is a reply to message #216713] Tue, 30 January 2007 02:18 Go to previous messageGo to next message
gautamsutar
Messages: 14
Registered: January 2007
Location: india
Junior Member
Hi

c:\temp is exist and i have got necessary previleges.

When I run this sql for 5 records in emp1 table then it will write file but for more records it is giving file write error


Gautam
Re: utl_file write error [message #216719 is a reply to message #216714] Tue, 30 January 2007 02:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Is the field you're trying to append NULL?

MHE
Re: utl_file write error [message #216731 is a reply to message #216719] Tue, 30 January 2007 03:33 Go to previous messageGo to next message
gautamsutar
Messages: 14
Registered: January 2007
Location: india
Junior Member
Hi Maaher

Thanks for showing interest

There is no null field in the table

Gautam
Re: utl_file write error [message #216733 is a reply to message #216731] Tue, 30 January 2007 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you have enough diskspace to write more than n lobs?
Re: utl_file write error [message #216741 is a reply to message #216733] Tue, 30 January 2007 04:00 Go to previous messageGo to next message
gautamsutar
Messages: 14
Registered: January 2007
Location: india
Junior Member
Hi Frank

I have enough disk space to write.

Gautam
Re: utl_file write error [message #216742 is a reply to message #216741] Tue, 30 January 2007 04:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This is a directory on the database server, right?

MHE

[Updated on: Tue, 30 January 2007 04:03]

Report message to a moderator

Re: utl_file write error [message #216744 is a reply to message #216742] Tue, 30 January 2007 04:11 Go to previous messageGo to next message
gautamsutar
Messages: 14
Registered: January 2007
Location: india
Junior Member
Hi

Ya , this is the directory on database server

gautam
Re: utl_file write error [message #216802 is a reply to message #216744] Tue, 30 January 2007 07:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
UTL_FILE :

The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. The GET_LINE len parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies the default value of max_linesize. If max_linesize and len are defined to be different values, then the lesser value takes precedence.

I will make it even more clear.

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)

If there is no max_linesize mentioned when you open a file it gets defaulted to 1024 (the maximum value is 32767), (i.e) in the line utl_file.put_line(l_file , l_string), the length of l_String cannot exceed max_linesize. If it exceeds max_linesize you will hit this error (error writing file). The maximum which oracle can support is 32767 as per the definition in the utl_file docs.


HTH



[Updated on: Wed, 31 January 2007 05:55]

Report message to a moderator

Re: utl_file write error [message #216820 is a reply to message #216711] Tue, 30 January 2007 10:13 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi,

Can it be that that "c:\temp" is not in the utl_file_dir parameter.
I believe that every directory you want to write to has to be in this init. parameter.

On the other hand.... I'm still a novice in the oracle filed Smile

Best Regards
Martijn
Re: utl_file write error [message #216941 is a reply to message #216820] Wed, 31 January 2007 00:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The OP says he is able to output 5 records, so there is nothing wrong with the location of the file or the UTL_FILE_DIR.
In my opinion, it has to be size-related.
Re: utl_file write error [message #216966 is a reply to message #216941] Wed, 31 January 2007 01:40 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
mmmmmm, I missed that the first time i read the post (and it's replies). And now I'm missing it for the second time.

It must be a lack of coffee.

Anyway... if the OP indicates that he actually can output records to the file, my suggestion is/was pointless.

Best Regards,
Martijn
Previous Topic: Materialzied View on Fast Refresh
Next Topic: truncate
Goto Forum:
  


Current Time: Mon Dec 05 10:48:46 CST 2016

Total time taken to generate the page: 0.19372 seconds