0

My colleague is having some issues when trying to export data from an Oracle database to CSV. Oddly enough, if he replaces the spaces in the field oc_cr_comments with NULL then the export works.

TRIM (REPLACE(oc_cr_comments, CHR(32), NULL))

What could be causing the export issue that is fixed by replacing spaces with NULL?

The error output is:

ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 140
ORA-06512: at "SYS.UTL_FILE", line 785
ORA-06512: at "EFS.P_CRIMINAL_DISPOSITION", line 151
ORA-06512: at line 1
Improve this question

1 Answer 1

Reset to default
1

In the call to utl_file.fopen, what was the max_linesize that was specified? How long is the line before you remove the spaces? How long is the line after you remove the spaces?

My guess is that when you opened the file, you specified a maximum line size that is smaller than the actual line size before you remove the spaces. If removing the spaces causes the line size to now be smaller than the maximum line size, that would explain why it works after you do that replacement.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That was it! The Oracle doc states that max_linesize defaults to 1024 if not specified, which it wasn't, and one of the records was about 1400 characters long. What value should he choose for max_linesize? Are there any disadvantages to specifying a higher value?
@mattblang - Any value up to 32k works. The only downside to choosing a larger line length would be if some downstream system would have problems with the file and/or if you are using the line length as a sort of constraint to ensure that the data is valid.

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.