APEX

I hava funny behaviour on one of the servers. I have a procedure which is supposed to check if a directory is valid. It will take a path as input and do the following:

1. create a directory

2. create a simple text file

3. if it runs in an exception it will show the error message and drop the created directory

4. if it is successfull it will drop the file and drop the directory

In my development on Windows 64 bit it works without problems. In the test environment on UNIX it will throw an error if I run it from the application or from SQL Workshop (owner schema). If running it from SQL Plus (owner schema), it will throw no errors. Does anyone knows where the problem is?

Here is the code and the error message I get:

DECLARE
   p_pathname                  VARCHAR2 (4000)
                                   := '/pkg/GHBF/GHBFoa/home/oracle/data/icu';
   p_fsp_language_preference   VARCHAR2 (2)       := 'en';
   PRAGMA AUTONOMOUS_TRANSACTION;
   v_sql                       VARCHAR2 (4000);
   v_message                   VARCHAR2 (4000);
   v_error                     VARCHAR2 (4000);
   v_filename                  VARCHAR2 (4000);
   v_directory                 VARCHAR2 (100);
   out_file                    UTL_FILE.file_type;
BEGIN
   v_directory := 'CCP_LOAD_TEST_DIR_XYZ';
   v_sql :=
         'CREATE OR REPLACE DIRECTORY '
      || v_directory
      || ' AS '
      || ''''
      || p_pathname
      || '''';

   EXECUTE IMMEDIATE v_sql;

   DBMS_OUTPUT.put_line ('Directory created.');

   BEGIN
      v_filename := 'ccp_load_test_file.txt';
      out_file := UTL_FILE.fopen (v_directory, v_filename, 'w');
      DBMS_OUTPUT.put_line ('File created.');
   EXCEPTION
      WHEN OTHERS
      THEN
         v_message := v_message || ' ERROR : ' || SQLERRM;
         DBMS_OUTPUT.put_line ('Error creating file. ' || SQLERRM);
   END;

   BEGIN
      UTL_FILE.fclose (out_file);
      UTL_FILE.fremove (v_directory, v_filename);
      v_sql := 'DROP DIRECTORY ' || v_directory;

      EXECUTE IMMEDIATE v_sql;

      DBMS_OUTPUT.put_line ('Directory dropped.');
   EXCEPTION
      WHEN OTHERS
      THEN
         v_message := v_message || ' ERROR : ' || SQLERRM;
         DBMS_OUTPUT.put_line ('Error dropping directory. ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         v_sql := 'DROP DIRECTORY ' || v_directory;

         EXECUTE IMMEDIATE v_sql;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      DBMS_OUTPUT.put_line ('Invalid path. ' || SQLERRM);
END;

Error while running it from the SQL Workshop:

Directory created.
Error creating file. ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Error dropping directory. ORA-29283: invalid file operation

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.apress.com/9781430235125
http://apex.oracle.com/pls/otn/f?p=31517:1
http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
-------------------------------------------------------------------