RE: utl_file question

From: Storey, Robert (DCSO) <"Storey,>
Date: Wed, 20 Jun 2018 14:28:09 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB01D2CE5BA2_at_DCSOSVMS02.dcso.org>



Thank you, I will review that. In my case, I granted read/write on the directory_object to public

From: Hans Forbrich [mailto:fuzzy.graybeard_at_gmail.com] Sent: Wednesday, June 20, 2018 9:27 AM
To: Storey, Robert (DCSO); oracle-l_at_freelists.org Subject: Re: utl_file question

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> [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<mailto: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-l
Received on Wed Jun 20 2018 - 16:28:09 CEST

Original text of this message