Re: utl_file question
Date: Wed, 20 Jun 2018 08:26:54 -0600
Message-ID: <269170c2-56ea-8b41-9972-c86fc63dc65f_at_gmail.com>
For a nice discussion/demo of DIRECTORY_OBJECTS, see http://morganslibrary.org/reference/directories.html - 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)
HTH
/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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Hans Forbrich
> *Sent:* Wednesday, June 20, 2018 8:57 AM
> *To:* oracle-l_at_freelists.org
> *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.
>
> /Hans
> 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
> d:\Level1\Level2\Level3.
>
> 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?
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 20 2018 - 16:26:54 CEST