5 Replies Latest reply on Oct 11, 2012 12:23 PM by BluShadow

    ORA-29285: file write error

    967646
      Hi,

      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:
      V_FILEHANDLE:=UTL_FILE.FOPEN(v_file_path,'ABC','W', max_linesize);

      Here max linesize = 32000, so as far as I think, limitation on length of records while writing the file is not an issue.
      Further code is written as:
      UTL_FILE.PUTF(V_FILEHANDLE, v_text1||'|'||v_text2||'|');
      UTL_FILE.NEW_LINE(V_FILEHANDLE);
      UTL_FILE.FFLUSH(V_FILEHANDLE); -- This is called after every 500 lines are written to the file.

      UTL_FILE.FCLOSE(V_FILEHANDLE);

      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?

      Oracle database 11g, version: 11.1.0.7.0


      Thanks,
      Sonam
        • 1. Re: ORA-29285: file write error
          BluShadow
          Not enough information.
          964643 wrote:
          Hi,

          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:
          V_FILEHANDLE:=UTL_FILE.FOPEN(v_file_path,'ABC','W', max_linesize);
          And what value has v_file_path got? Is this the name of an Oracle Directory Object as it should be?
          Here max linesize = 32000, so as far as I think, limitation on length of records while writing the file is not an issue.
          Further code is written as:
          UTL_FILE.PUTF(V_FILEHANDLE, v_text1||'|'||v_text2||'|');
          UTL_FILE.NEW_LINE(V_FILEHANDLE);
          UTL_FILE.FFLUSH(V_FILEHANDLE); -- This is called after every 500 lines are written to the file.

          UTL_FILE.FCLOSE(V_FILEHANDLE);

          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?
          Not without seeing complete code so that we can try and reproduce the problem or spot where you may be going wrong.
          • 2. Re: ORA-29285: file write error
            967646
            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:

            V_FILEHANDLE UTL_FILE.FILE_TYPE;
            max_linesize number := 32000;

            cursor c1 is
            select col1, col2 from temp_tbl;

            begin

            FOR c1_rec in c1 LOOP
            UTL_FILE.PUTF(V_FILEHANDLE, col1||'|'||col2||'|');
            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);
            COMMIT;
            END IF;
            end loop;

            UTL_FILE.FCLOSE(V_FILEHANDLE);
            End;


            Here value for v_commit_size = 500, but this error is coming after writing lakhs of records in file. Can you please suggets now.
            • 3. Re: ORA-29285: file write error
              BluShadow
              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.
              • 4. Re: ORA-29285: file write error
                967646
                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?

                Thanks,
                Sonam
                • 5. Re: ORA-29285: file write error
                  BluShadow
                  Well, I just ran this on my test database...
                  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;
                  and it executed successfully, creating a file that was 355K in size.

                  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.