UTL_FILE Package problem

Hello,

Problem #1
In the $ORACLE_HOME dbs directory the init.ora file has the UTL_FILE_DIR set to * and my procedure reads a configuration type ASCII file that has the directory where to place the generated ASCII file. When the procedure runs it runs fine when I generate the ASCII file to the $ORACLE_HOME directory or a user directory, it has been setup (at the UNIX level) with privileges for anyone to read/write to it.

If I then change the init.ora file specifying the UTL_FILE_DIR to the user diectory (UTL_FILE_DIR = /usr/users/jasonl) shutdown and startup the O server and then run the procedure I get the error:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 113
ORA-06512: at "LASERJ0928.AUDIT_ITEM", line 195
ORA-06512: at line 1
ORA-06510: PL/SQL: unhandled user-defined exception

There is two problems with the above error:

#1 - it is caused when I try to write to a directory where in the
init.ora file the UTL_FILE_DIR specified a specific directory other than the $ORACLE_HOME dbs directory.

#2 - There is no exception handle to catch the above error. This must be
caused by permissions to write to a directory as I get it when I try to write to /usr/users/jasonl and not the $ORACLE_HOME dbs directory and the init.ora specifies the user directory.

I have also used every Exception handler for the UTL_FILE package, including the WHEN OTHERS and it doesn't catch the above.

I have been running this on a IBM AS400 AIX and also on a DEC Alpha with the same crappy results.

Summary
By specifiying the directory in INIT.ORA I should be able to write my file to the directory. It only writes when I specify * and then it writes to either the dbs directory or the user directory; if I specify a directory it will only work for the dbs directory and not the user directory.

Please help!!!

Thanks a Bunch Received on Mon Nov 16 1998 - 13:25:08 CST