0

I have a problem here while exporting data from oracle database to CSV file using a procedure. While exporting data, sometimes the CSV file is getting truncated and the error it shows is "ORA-29285 - File write error". The problem here is the file is getting truncated not all the times but randomly.

Edit : Below is the chunk of code I used in my procedure

conn := utl_file.fopen('sample_directory','output.csv','W',4096);
for i in (select * from per_data)
loop
utl_file.put_line(conn,i.name||','||i.sub||','||to_char(i.start_date,'dd-mon-yy')||','||to_char(i.expire_date,'dd-mon-yy')||','||i.loc||CHR(13));
end loop;
utl_file.fclose(conn);`

I am pulling my hair to trace out the reason. Can someone help me out ?

Improve this question
4
  • 2
    Showing your procedure code might help. What debugging have you done? What is the length of the data you're trying to write when it fails? Commented Jun 21, 2016 at 9:22
  • Please do not paste external links to code, and do not post images of your code - edit your question and paste the code as text instead. Commented Jun 21, 2016 at 10:41
  • @Alex Do the file size matter in this case ? I'm writing thousands of records to the output. Commented Jun 21, 2016 at 11:39
  • Yes, if it exceeds 32k, but you said it sometimes works and sometimes errors with the same script - did you mean exactly the same, or with different amounts of data? Commented Jun 21, 2016 at 11:42

2 Answers 2

Reset to default
2

One way to get this error is to open the file with a certain maximum line size - possibly the default 1024 - and then try to write a single line to that file which is longer than that.

In your case you don't seem to be doing that, as you open it with 4096 and your lines are all (apparently) shorter than that.

So you may be hitting the 32k limitation:

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.

You don't seem to be doing any flushing. You could change your put_line call to auto-flush:

utl_file.put_line(conn,
  i.name||','||i.sub||','||to_char(i.start_date,'dd-mon-yy')
    ||','||to_char(i.expire_date,'dd-mon-yy')||','||i.loc||CHR(13),
  true);

or keep a counter in your loop and manually flush every 100 lines (or whatever number works and is efficient for you).

As noted in the documentation:

If there is buffered data yet to be written when FCLOSE runs, you may receive WRITE_ERROR when closing a file.

You aren't flushing before you close, so adding an explicit flush - even if you have autoflush set to true - might also help avoid this, at least if the exception is being thrown by the fclose() call rather than by put_line():

...
end loop;
utl_file.fflush(conn);
utl_file.fclose(conn);
Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

utl_file.fopen(sample_directory,output.csv,'W',4096); I think I'm using line size which is greater than what I write to a file. My output is : Nick,F1333,19-JAN-13,19-JAN-99,IN. If I run the same script again, the output I get is perfectly fine.
Are you adding new line characters to the output - and carriage returns if your OS expects those? How many records are you writing out? It's odd if exactly the same output works intermittently. Please add the procedure code to the question so we don't have to guess what you are doing.
Yes ! I'm adding carriage return to the output. The output contain thousands of records (specifically 80k+). Does it depends on the number of records I'm writing to the output ? And it's really odd and I don't know why did I get the correct output when I run it again. This is a frequent problem.
Is the data in per_data changing between runs?
I used the same data without any change while running the script again.
|
0

"ORA-29285 - File write error"

this error occured when your HARD DRIVE is FULL where you are exporting data.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.