4 Replies Latest reply: Aug 30, 2013 12:18 AM by swesley_perth RSS

    Trouble Using utl_file from APEX

    DaveR

      In 11.2.0.3 I have a procedure that works fine from APEX, for a while....on a linux (OUL) VM writing to an nfs mount

      then it stops working until I bounce the database (very annoying).

       

      While APEX can't run the procedure, I can run the following from sqlplus successfully:

      DECLARE

        V1 VARCHAR2(32767) default 'Test from remote connection';

        F1 UTL_FILE.FILE_TYPE;

        D1 varchar2(32727) default 'FIN_DATA_OUT_DIR';

        F2 varchar2(32727) default 'SR_test_file';

      BEGIN

        F1 := UTL_FILE.FOPEN(D1,F2,'W',2000);

        UTL_FILE.PUT_LINE(F1,V1);

        UTL_FILE.FCLOSE(F1);

      END;

      /

       

      I can also run the procedure successfully in sqlplus:

      create procedure sr_test as

        V1 VARCHAR2(32767) default 'Test from remote connection package';

        F1 UTL_FILE.FILE_TYPE;

        D1 varchar2(32727) default 'FIN_DATA_OUT_DIR';

        F2 varchar2(32727) default 'SR_test_file';

      BEGIN

        F1 := UTL_FILE.FOPEN(D1,F2,'W',2000);

        UTL_FILE.PUT_LINE(F1,V1);

        UTL_FILE.FCLOSE(F1);

      END;

      /

      execute sr_test;

       

      but when I run the same procedure, in the same database, from APEX I'll start getting:

      ORA-29283: invalid file operation

      unmounting and remounting the nfs directory makes no difference, bouncing the http listener makes no difference, but bouncing the database always works

      Any ideas to keep this working without at database bounce would be appreciated.

        • 1. Re: Trouble Using utl_file from APEX
          swesley_perth

          My guess is a permissions issue on the file from the linux user running the oracle database.

          • 2. Re: Trouble Using utl_file from APEX
            DaveR

            Thanks for replying.  three items
            1.  The directory permissions are already 777 (forgot to mention that before).

            2.  If the directory permissions were to blame it wouldn't work from sql*plus.

            3.  I'm not sure how bouncing the database would consistently fix a file permissions issue.

             

            Perhaps there is an explination somewhere about the user/privilege cascade from APEX through the database to the filesystem or perhaps a different internals issue with APEX seeing database directories pointing to nfs mounted filesystems?  I assume this is something esoteric but there seem to be several "unaswered" posts about this issue so I know I'm not completely alone in needing a solution.

            • 3. Re: Trouble Using utl_file from APEX
              Joe Upshaw

              Dave,

               

              With regard to item 2, this is not correct. Unless you are actually connecting as the user APEX_PUBLIC_USER and then doing a SET SCHEMA to the parsing schema of the application, then you are not testing Apples-to-Apples with respect to underlying permissions when you connect via SQL*Plus. That said, if the permissions are wide open (777), then, agreed, this is likely not your problem.


              My guess, and one that would be explain being fixed by a DB bounce, is a file locking issue.


              However, you can get more information about exactly what it is that UTL_FILE doesn't like by adding a more verbose EXCEPTION handler. Here is an example:

               

              EXCEPTION

                  WHEN UTL_FILE.INVALID_PATH THEN

                      RAISE_APPLICATION_ERROR( -20002, 'Invalid Path Specified' ); RAISE;

                  WHEN UTL_FILE.INVALID_MODE THEN

                      RAISE_APPLICATION_ERROR( -20003, 'Invalid Mode Specified' ); RAISE;

                  WHEN UTL_FILE.INVALID_FILEHANDLE THEN

                      RAISE_APPLICATION_ERROR( -20004, 'Invalid File Handle' ); RAISE;

                  WHEN UTL_FILE.INVALID_OPERATION THEN

                      RAISE_APPLICATION_ERROR( -20005, 'Invalid Operation Specified' ); RAISE;

                  WHEN UTL_FILE.READ_ERROR THEN

                      RAISE_APPLICATION_ERROR( -20006, 'Unable to Read Specified File' ); RAISE;

                  WHEN UTL_FILE.WRITE_ERROR THEN

                      RAISE_APPLICATION_ERROR( -20007, 'Unable to Write to Specified File' ); RAISE;

                  WHEN UTL_FILE.INTERNAL_ERROR THEN

                      RAISE_APPLICATION_ERROR( -20008, 'Unspecified Internal Error Encountered' ); RAISE;

                  WHEN NO_DATA_FOUND THEN

                      RAISE_APPLICATION_ERROR( -20009, 'Operation Read Past End of File' ); RAISE;

                  WHEN VALUE_ERROR THEN

                      RAISE_APPLICATION_ERROR( -20010, 'Invalid Value Encountered. Perhaps ' ||

                                              'an argument to a GET or PUT operation ' ||

                                              'was > 1022 Bytes' ); RAISE;

               

              -Joe

              • 4. Re: Trouble Using utl_file from APEX
                swesley_perth

                I was going to make similar comment about #2 - OS user will be different.