This content has been marked as final. Show 5 replies
Not enough information.
964643 wrote:And what value has v_file_path got? Is this the name of an Oracle Directory Object as it should be?
We are getting this error ORA-29285: file write error while writing to a text file using utl_file.putf package.
Using fopen funtion in code to open file as below:
Here max linesize = 32000, so as far as I think, limitation on length of records while writing the file is not an issue.Not without seeing complete code so that we can try and reproduce the problem or spot where you may be going wrong.
Further code is written as:
UTL_FILE.FFLUSH(V_FILEHANDLE); -- This is called after every 500 lines are written to the file.
We are encountering this error every now and then, and strangely this gets resolved when we re-run the program and file gets written successfully.
Can someone please help on this please?
Yes v_file_path is the name of an oracle directory object. No problem on that front since files are getting created in this directory, but sometimes we get this file-write error. Here is complete code:
max_linesize number := 32000;
cursor c1 is
select col1, col2 from temp_tbl;
FOR c1_rec in c1 LOOP
IF CEIL((c1%ROWCOUNT)/V_COMMIT_SIZE) = FLOOR((c1%ROWCOUNT)/V_COMMIT_SIZE) THEN
Here value for v_commit_size = 500, but this error is coming after writing lakhs of records in file. Can you please suggets now.
Well for starters, get rid of the intermittent commits in your loop. That is just wrong. You should never commit inside a cursor loop. Commit should only be done at the end of a logical transaction.
Alright I will see to that. Thanks for enlightening. But how should I resolve this error? I have been doing analysis on this and coming to a thought of replacing 'putf' by 'put_line'. But not sure since root cause is unknown as of now. Can this issue be some evnironment or OS level?
Well, I just ran this on my test database...
and it executed successfully, creating a file that was 355K in size.
DECLARE V_FILEHANDLE UTL_FILE.FILE_TYPE; max_linesize number := 32000; cursor c1 is select empno, ename from emp; begin v_FILEHANDLE := UTL_FILE.FOPEN('TEST_DIR','test.txt','W',max_linesize); FOR i in 1..2000 -- output the data enough times to exceed 32K LOOP FOR c in c1 LOOP UTL_FILE.PUTF(V_FILEHANDLE, c.empno||'|'||c.ename||'|'); UTL_FILE.NEW_LINE(V_FILEHANDLE); -- IF CEIL((c1%ROWCOUNT)/V_COMMIT_SIZE) = FLOOR((c1%ROWCOUNT)/V_COMMIT_SIZE) THEN -- UTL_FILE.FFLUSH(V_FILEHANDLE); -- END IF; END LOOP; end loop; UTL_FILE.FCLOSE(V_FILEHANDLE); End;
Typically the error you are getting would indicate a problem in the writing of the file from the o/s side of things e.g. out of disk space, or write permissions, or the file being 'locked' against writing because it's open in some application etc.