Getting error 29285. 00000 - "file write error" *Cause: Failed to write to, flush, or close a file when trying to FFLUSH

Question

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.

Here is the code of my stored procedure:

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 "Record", A.RECDESCR as "Record Description"
        , B.FIELDNUM as "FieldNum", B.FIELDNAME as "Field", C.DESCRLONG as "Field Description", 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 "Key (Y/N)", 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 "FieldType"
        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 > 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 <> '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 <> 'isnull' and boolTxTabName <> 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 <> 'noDescrField' and query3_str <> 'sameFieldNotPresentInTx' and query3_str <> 'CLOB_Type_Skipping' and query3_str <> 'BLOB_Type_Skipping') THEN
            execute immediate query2_str into distinctcount;
        END IF;

        if(distinctcount > 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 > 32766) THEN
        WHILE pos < 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;

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 IF (fieldinfoLength > 32766) if-else statement. It works fine up to that point doing UTL_FILE.PUT_LINE without any hitches.

Error starting at line : 11 in command -

BEGIN SP_DATAMAPPINGINFO ('DIR'); END;

Error report -

ORA-29285: file write error

ORA-06512: at "SYS.UTL_FILE", line 183

ORA-06512: at "SYS.UTL_FILE", line 1169

ORA-06512: at "SYSADM.SP_DATAMAPPINGINFO", line 240

ORA-06512: at line 1

29285. 00000 - "file write error"

*Cause: Failed to write to, flush, or close a file.

*Action: Verify that the file exists, that it is accessible, and that it is open in write or append mode.

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.

Answer 1

Seems like you are feeding the function 'DIR' instead of an actual directory.