CODEWITHSUNDEEP
oracle-databasecsvstored-procedures
learner1
learner1

Reputation: 83

ORA-29285 error While exporting data from oracle database to CSV file using procedure

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 ?

Upvotes: 0

Views: 8234

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

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);

Upvotes: 2

Related Questions