title: Getting error 29285. 00000 - "file write error" *Cause: Failed to write to, flush, or close a file when trying to FFLUSH
body: <p>As part of a project, I'm working on data mapping of the peoplesoft records and fields in use at our company. There are more than 25K fields that I have to document but it gets tedious and will take a much more time than I have if I did it normal way. So, I wrote a stored procedure to reduce some of the work in documenting translate values.</p>
  <p>Here is the code of my stored procedure:</p>
  <pre><code>CREATE OR REPLACE PROCEDURE SP_DATAMAPPINGINFO(exportdir varchar2) AS 
  TYPE EmpCurTyp IS REF CURSOR;
  newrow_cursor    EmpCurTyp;
  txtable_cursor EmpCurTyp;
  txtabfieldname_cursor EmpCurTyp;
  
  file UTL_FILE.FILE_TYPE;
  queryfile UTL_FILE.FILE_TYPE;
  
  recname varchar2(40);
  recdescr varchar2(200);
  fieldnum number(3);
  fieldname varchar2(40);
  fieldescr varchar2(2000);
  keyflag varchar2(1);
  fieldtype varchar2(20);
  distinctcount number(10);
  boolfieldFirst number(1);
  boolfieldSecond number(1);
  boolfieldCLOB number(1);
  boolfieldBLOB number(1);
  boolTxTabName number(1);
  txtabfieldname varchar2(40);
  
  query1_str varchar(300);
  query2_str varchar(300);
  query3_str varchar(300);
  query4_str varchar(300);
  queryTxTabExist varchar(300);
  queryTxVwExist varchar(300);
  
  fieldvalue varchar2(500);
  hyphen varchar2(5);
  txvalue varchar2(500);
  fielduse CLOB;
  fielduseLength INTEGER;
  fieldinfo CLOB;
  fieldinfoLength INTEGER;
  pos INTEGER := 1;
  buffer VARCHAR2(32767);
  amount BINARY_INTEGER := 32760;
  
  tablename varchar2(40);
  intertxtabname varchar2(30);
  txtablename varchar2(40);
  txTabNameCheck number(1);
  txVwNameCheck number(1);
  
  dir varchar2(30);
  file_name varchar2(30);
  query_File varchar2(30);
  
  CURSOR get_fields is
          select A.RECNAME as &quot;Record&quot;, A.RECDESCR as &quot;Record Description&quot;
          , B.FIELDNUM as &quot;FieldNum&quot;, B.FIELDNAME as &quot;Field&quot;, C.DESCRLONG as &quot;Field Description&quot;, CASE WHEN
          EXISTS(select K.FIELDNAME FROM PSRECFLDDBKEYVW K WHERE K.RECNAME = A.RECNAME AND K.FIELDNAME=B.FIELDNAME)
          THEN 'Y' ELSE 'N' END as &quot;Key (Y/N)&quot;, DECODE (C.FIELDTYPE,
                   0, 'Character',
                   1, 'LongChar',
                   2, 'Number',
                   3, 'Signed Number',
                   4, 'Date',
                   5, 'Time',
                   6, 'DateTime',
                   8, 'ImageOrAttachment',
                   9, 'Image Reference',
                   'Unknown') as &quot;FieldType&quot;
          FROM PSRECDEFN A, PSRECFIELDDB B LEFT JOIN PSDBFIELD C ON (B.FIELDNAME = C.FIELDNAME)
          WHERE B.RECNAME = A.RECNAME
          AND A.RECNAME IN (select R.RECNAME from PSRECDEFN R, DBA_TABLES T
          WHERE ('PS_'||R.RECNAME=T.TABLE_NAME)
          AND T.NUM_ROWS &gt; 0
          AND R.RECTYPE=0)
          order by A.RECNAME, B.FIELDNUM;
  
  BEGIN
  
  dir := exportdir;
  file_name := 'peoplesoft_datamapping.csv';
  
  query_File := 'peoplesoftQuery.txt';
  
  file := UTL_FILE.FOPEN(dir, file_name, 'w', 32767);
  queryfile := UTL_FILE.FOPEN(dir, query_File, 'w', 32767);
  
  OPEN get_fields;
      LOOP
      FETCH get_fields INTO recname, recdescr, fieldnum, fieldname, fieldescr, keyflag, fieldtype;
  
  
      fielduse := '';
      fieldinfo := '';
      tablename := 'PS_' || recname;
      hyphen := ' - ';
      fieldvalue := '';
      txvalue := '';
      intertxtabname := '';
      txtablename := '';
      boolfieldFirst := 0;
      boolfieldSecond := 0;
      boolfieldCLOB := 0;
      boolfieldBLOB := 0;
      boolTxTabName := 0;
      txTabNameCheck := 0;
      txVwNameCheck := 0;
      pos := 1;
      
      
      IF (fieldtype = 'LongChar') THEN
          boolfieldCLOB := 1;
      ELSIF (fieldtype = 'ImageOrAttachment') THEN
          boolfieldBLOB := 1;
      END IF;
  
      IF (fieldname NOT LIKE '%EMPLID%' and fieldname NOT LIKE '%DESCR%' and fieldname NOT LIKE '%COMMENT%' and fieldtype &lt;&gt; 'Date') THEN
          query1_str := 'select NVL(TRIM(RI.EDITTABLE), ''isnull'') FROM PSRECDEFN RD, PSRECFIELDDB RI WHERE RD.RECNAME = RI.RECNAME 
          AND RD.RECNAME = ''' || recname || ''' AND RI.FIELDNAME = ''' || fieldname || '''';
  
          execute immediate query1_str into intertxtabname;
          
          queryTxTabExist := 'SELECT COUNT(TABLE_NAME) FROM ALL_TABLES where TABLE_NAME=''PS_' || intertxtabname || '''';
          execute immediate queryTxTabExist into txTabNameCheck;
          if (txTabNameCheck = 1) THEN
              boolTxTabName := 1;
          ELSIF (txTabNameCheck = 0) THEN
              queryTxVwExist := 'SELECT COUNT(VIEW_NAME) FROM ALL_VIEWS where VIEW_NAME=''PS_' || intertxtabname || '''';
              execute immediate queryTxVwExist into txVwNameCheck;
              if (txVwNameCheck = 1) THEN
                  boolTxTabName := 1;
              ELSIF (txVwNameCheck = 0) THEN
                  queryTxTabExist := 'SELECT COUNT(TABLE_NAME) FROM ALL_TABLES where TABLE_NAME=''' || intertxtabname || '''';
                  execute immediate queryTxTabExist into txTabNameCheck;
                  if (txTabNameCheck = 1) THEN
                      boolTxTabName := 5;
                  ELSIF (txTabNameCheck = 0) THEN
                      queryTxVwExist := 'SELECT COUNT(VIEW_NAME) FROM ALL_VIEWS where VIEW_NAME=''' || intertxtabname || '''';
                      execute immediate queryTxVwExist into txVwNameCheck;
                      if (txVwNameCheck = 1) THEN
                          boolTxTabName := 5;
                      ELSIF (txVwNameCheck = 0) THEN
                          boolTxTabName := 0;
                      ELSE
                          dbms_output.put_line ('Incorrect transaction table count');
                      END IF;
                  END IF;
              ELSE
                  dbms_output.put_line ('Incorrect transaction table count');
              END IF;
          ELSE
              dbms_output.put_line ('Incorrect transaction table count');
          END IF;
          
          query2_str := 'select count(distinct T.' || fieldname || ') FROM ' || tablename || ' T';
  
          IF (intertxtabname &lt;&gt; 'isnull' and boolTxTabName &lt;&gt; 0) THEN
              query4_str := 'select B.FIELDNAME FROM PSRECDEFN A, PSRECFIELDDB B WHERE B.RECNAME=A.RECNAME AND A.RECNAME=''' || intertxtabname
              || ''' ORDER BY B.FIELDNAME';
              OPEN txtabfieldname_cursor for query4_str;
                  LOOP
                  FETCH txtabfieldname_cursor into txtabfieldname;
                  if (txtabfieldname='DESCR') THEN
                      boolfieldFirst := 1;
                  ELSIF (txtabfieldname = fieldname) THEN
                      boolfieldSecond := 1;
                  END IF;
                  EXIT WHEN txtabfieldname_cursor%NOTFOUND;
                  END LOOP;
              CLOSE txtabfieldname_cursor;
              IF (boolfieldFirst = 1 and boolfieldSecond = 1 and boolfieldCLOB = 0 and boolfieldBLOB = 0) THEN
                  IF (boolTxTabName = 1) THEN
                      txtablename := 'PS_' || intertxtabname;
                  ELSIF (boolTxTabName = 5) THEN
                      txtablename := intertxtabname;
                  ELSE
                      dbms_output.put_line ('Incorrect case traversal. Check logic');
                  END IF;
                  query3_str := 'select distinct T.' || fieldname || ', TR.DESCR FROM ' || tablename || ' T left join ' || txtablename || ' TR ON T.' 
                  || fieldname || ' = TR.' || fieldname || ' order by T.' || fieldname;
              ELSIF (boolfieldFirst = 0) THEN
                  query3_str := 'noDescrField';
              ELSIF (boolfieldSecond = 0) THEN
                  query3_str := 'sameFieldNotPresentInTx';
              ELSIF (boolfieldCLOB = 1) THEN
                  query3_str := 'CLOB_Type_Skipping';
              ELSIF (boolfieldBLOB = 1) THEN
                  query3_str := 'BLOB_Type_Skipping';
              END IF;
          ELSE
              IF (boolfieldCLOB = 1) THEN
                  query3_str := 'CLOB_Type_Skipping';
              ELSIF (boolfieldBLOB = 1) THEN
                  query3_str := 'BLOB_Type_Skipping';
              ELSE
                  txtablename := '';
                  query3_str := 'select distinct DT.' || fieldname || ', DTR.XLATLONGNAME FROM ' || tablename || ' DT left join PSXLATITEM DTR on 
                  (DTR.FIELDNAME = ''' || fieldname || ''' and DT.' || fieldname || ' = DTR.FIELDVALUE) order by DT.' || fieldname;
              END IF;
          END IF;    
  
          UTL_FILE.PUT_LINE(queryfile, query3_str);
          
          IF (query3_str &lt;&gt; 'noDescrField' and query3_str &lt;&gt; 'sameFieldNotPresentInTx' and query3_str &lt;&gt; 'CLOB_Type_Skipping' and query3_str &lt;&gt; 'BLOB_Type_Skipping') THEN
              execute immediate query2_str into distinctcount;
          END IF;
  
          if(distinctcount &gt; 150) THEN
              fielduse := 'More than 150';
          ELSIF (query3_str = 'noDescrField') THEN
              fielduse := 'noDescrField';
          ELSIF (query3_str = 'sameFieldNotPresentInTx') THEN
              fielduse := 'sameFieldNotPresentInTx';
          ELSIF (query3_str = 'CLOB_Type_Skipping') THEN
              fielduse := 'CLOB_Type_Skipping';
          ELSIF (query3_str = 'BLOB_Type_Skipping') THEN
              fielduse := 'BLOB_Type_Skipping';
          ELSE
              OPEN newrow_cursor FOR query3_str;
                  LOOP
                  FETCH newrow_cursor INTO fieldvalue, txvalue;
                  fielduse := fielduse || fieldvalue || ' - ' || txvalue || '; ';
                  EXIT WHEN newrow_cursor%NOTFOUND;
                  END LOOP;
              CLOSE newrow_cursor;
          END IF;
  
      ELSE
          fielduse := 'SKIPPING';
      END IF;
      
      fieldinfo := recname || ',' || recdescr || ',' || fieldnum || ',' || fieldname || ',' || fieldescr || ',' || keyflag || ',' || fieldtype || ',' || fielduse;
      
      fieldinfoLength := length(fieldinfo);
      IF (fieldinfoLength &gt; 32766) THEN
          WHILE pos &lt; fieldinfoLength LOOP
              dbms_lob.read(fieldinfo, amount, pos, buffer);
              UTL_FILE.PUT(file, buffer);
              UTL_FILE.FFLUSH(file);
              pos := pos + amount;
          END LOOP;
          UTL_FILE.NEW_LINE(file);
      ELSE
          UTL_FILE.PUT_LINE(file, fieldinfo);
      END IF;
      
      fielduse := '';
      fieldinfo := '';
      pos := 1;
  
      END LOOP;
      
      UTL_FILE.FCLOSE(file);
  CLOSE get_fields;
  
  NULL;
  END SP_DATAMAPPINGINFO;
  </code></pre>
  <p>It seems that I have the logic for my data correct but I might not be using UTL_FILE.FFLUSH function properly as when the code hits that function, I get the following error (I have removed couple of comments from this code so the line number might not match up). It always happens at the same point as the output file always has the same size when the error happens. It happens the first time that code evaluates true for <strong><code>IF (fieldinfoLength &gt; 32766)</code></strong> if-else statement. It works fine up to that point doing UTL_FILE.PUT_LINE without any hitches.</p>
  <blockquote>
  <p>Error starting at line : 11 in command -</p>
  <p>BEGIN SP_DATAMAPPINGINFO ('DIR'); END;</p>
  <p>Error report -</p>
  <p>ORA-29285: file write error</p>
  <p>ORA-06512: at &quot;SYS.UTL_FILE&quot;, line 183</p>
  <p>ORA-06512: at &quot;SYS.UTL_FILE&quot;, line 1169</p>
  <p>ORA-06512: at &quot;SYSADM.SP_DATAMAPPINGINFO&quot;, line 240</p>
  <p>ORA-06512: at line 1</p>
  <ol start="29285">
  <li>00000 -  &quot;file write error&quot;</li>
  </ol>
  <p>*Cause:    Failed to write to, flush, or close a file.</p>
  <p>*Action:   Verify that the file exists, that it is accessible, and that it is open in write or append mode.</p>
  </blockquote>
  <p>I am struggling to find the cause behind this as I think I am using the CLOB, UTL_PUT and UTL_FFLUSH functions correctly. Any help is appreciated. If you spot any other glaring mistakes, please point those out.</p>