Re: utl_file question

  • From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
  • To: "Storey, Robert (DCSO)" <RStorey@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 20 Jun 2018 08:26:54 -0600

For a nice discussion/demo of DIRECTORY_OBJECTS, see - which also has a link tio discussion of UTL_FILE.

A directory object is owned by SYS.  For any user running utl_file, the directory object must be granted (eg: r/w) to that user.  In Dan's demos, that user is 'UWCLASS', the dir obj is 'ORALOAD', and it is opened in various modes (including append-only)

/Hans - same disclaimer

On 2018-06-20 8:06 AM, Storey, Robert (DCSO) wrote:

I had not created a Directory_object.

As a test (before your email) I created a directory object called temp_dir pointing to the windows folder location.  Replaced my ‘file path’ with the ‘temp_dir’ in the .fopen and I still get the invalid path  errpr

*From:*oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Hans Forbrich
*Sent:* Wednesday, June 20, 2018 8:57 AM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: utl_file question

Sounds like the DIRECTORY_OBJECT (the object stored in the DB data dictionary that replaces the PATH in old versions of the DB) may not be correct, or permissions to the DIRECTORY_OBJECT may not have been GRANTED to the user running utl_file.

Note: I work for Oracle.  Opinions are my own, not my employers.

On 2018-06-20 7:31 AM, Storey, Robert (DCSO) wrote:

    Working on moving some stored procedures from a 9i to an 11g
    system and I hit a snag with regards to utl_file.

    In my stored proc, I pull a path variable, ie, d:\level1\level2
    for a root level folder. Then, as the procedure goes through the
    motions, it tacks on another level to this path based on some
    criteria.  It then writes out a flat text file to location

    When I tested this proc in my 11g, I kept getting an
    utl_file.invalid_path error.   I first took this as permissions
    based, but, when I gave everyone full control of the destination
    folder, it still gave same error.

    Created a simple proc to just open and close a file.  Get the same
    error regardless of where I point it.  This code works perfect on
    the 9i, but not 11g.

    A check of documents and online searching seems to point to the
    fact that you can’t pass UTL_FILE.FOPEN a qualified path name. 
    You now have to create a “Directory” object and give permssions to
    it, and use it as the Path.

    Anyone confirm or deny?

Other related posts: