Logo Background

ORA-29283 Invalid File Operation sys.utl_file

  • By on February 9, 2010 | No Comments

    ORA-29283 Invalid File Operation sys.utl_file error can be quite hard to troubleshoot. Today we have problem when the developer execute the PL/SQL stored procedure which will write the files into the entry in all_directories table.

    If you are working on Oracle E-Business Suite, it is quite common that the Oracle Database will write files into the mount point that resides in Oracle Applications via PL/SQL stored procedure.

    Before your PL/SQL stored procedure can write from Oracle database, you need to create the directory entry in the database instance with two alternative steps.

    First step is by adding the directory full path entry utl_file_dir in the spfile and rebounce the Oracle database.

    But rebounce Oracle database will need downtime, thus we prefer the second step by adding the directory full path entry in all_directories.

    SQL> CREATE OR REPLACE directory XML_DIR AS '/oracle/apps/nfs/interface';
    SQL> GRANT READ,WRITE ON directory XML_DIR TO apps;
     
    SQL> SET SERVEROUTPUT ON
    SQL> DECLARE
    fHandler UTL_FILE.FILE_TYPE;
    BEGIN
    fHandler := UTL_FILE.FOPEN('FUSION_OUT_DIR', 'utl_file.txt', 'w');
    UTL_FILE.PUTF(fHandler, 'Test write from database using all_directories path');
    UTL_FILE.FCLOSE(fHandler);
    EXCEPTION
    WHEN utl_file.invalid_path THEN
    raise_application_error(-20000, 'ERROR: Create directory or set UTL_FILE_DIR.');
    END;
    /
     
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 475
    ORA-29283: invalid file operation
    ORA-06512: at line 4

    As you see, there’s error in the end of the PL/SQL stored procedure. What should we troubleshoot on the error?

    1. Check whether the NFS mount point /oracle/apps/nfs/interface was available in the Oracle database machine. You can use Unix bdf command to check it.

    2. Check if there’s any directory permission problem by creating a file manually from the shell of Oracle database machine. You can use the SQL command select hostcommand(‘/usr/bin/touch /oracle/apps/nfs/interface/testing.txt’) from dual; to create testing file from SQL*Plus or TOAD. Grant necessary chmod access for the database user oracle if required.

    We found out the problem was one of the RAC database nodes having issue in the NFS mount point in the end. So don’t be surprise to find out that this issue is intermittent in this case.

    Previous
    Next
    » Sqlplus EOF In Unix Shell Script
Leave a Comment