Re: UTL_FILE package problem - need help pls.

Jason Ladew <jal_at_esc188.expansion.com> wrote:

[utl_file problems...]

>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.

Hi Jason,

I found the utl_file package very useful, but only usable when you write your own layer of code around it to handle it's idiosynchrasies.

There are two problems:
1) All file access errors that can occur are handled like a user-defined exception, i.e. there is no "real" Oracle error message associated with them. If you have a look at the utl_file package spec, you will find:

  invalid_path       EXCEPTION;
  invalid_mode       EXCEPTION;
  invalid_filehandle EXCEPTION;
  invalid_operation  EXCEPTION;
  read_error         EXCEPTION;
  write_error        EXCEPTION;
  internal_error     EXCEPTION;

In the spec is also documentation which procedure/function can raise which execption, so that a meaningful exception handler in your own code could look like this, only to get a more or less meaningful error message:

  EXCEPTION
  WHEN UTL_FILE.invalid_path THEN
    raise_application_error(-20000, 'Invalid path');   WHEN UTL_FILE.invalid_mode THEN
   ...
   <include a section for each exception that your particular call     could raise >

2) UTL_FILE access is only possible for files/directories which are setup in the instance initialization file, and which have the proper permissions, i.e. they must be accessible by the Oracle background processes, because file access is done with the privileges of the account under which Oracle is running. E.g. you could not write to your /usr/users/jasonl directory because there are no permissions for oracle userid or dba group id to do so. If you chmod your directory to eg. world-writeable, it will work.

I'd like to add that it's not a good idea to use the 'UTL_FILE_DIR=*' configuration option, because it severly compromises database and database server security.

Hope this helps,
Peter

--
Peter Schneider
pschneider_at_knuut.de Received on Mon Nov 16 1998 - 15:54:13 CST