Re: utl_file question
- From: Michael McMullen <ganstadba@xxxxxxxxxxx>
- To: "RStorey@xxxxxxxxxxxxxxxxxx" <RStorey@xxxxxxxxxxxxxxxxxx>, oracle-l-freelist <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Jun 2018 14:16:22 +0000
test using utl_file_dir='*' in your spfile
you might not want to leave your filesystem this wide open though.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
Sent: June 20, 2018 11:47 AM
To: RStorey@xxxxxxxxxxxxxxxxxx; oracle-l-freelist; Hans Forbrich
Subject: RE: utl_file question
We are running Oracle EBS on Windows and it still uses UTL_FILE init.ora
entries rather than directory objects. We have no problem writing to the
UTL_FILE entries in Oracle 11.2.0.4 on Windows using EBS or custom programs.
Are the entries in your init.ora?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
"Storey, Robert (DCSO)" <RStorey@xxxxxxxxxxxxxxxxxx> 6/20/18 10:28 AM >>>
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@xxxxxxxxx]
Sent: Wednesday, June 20, 2018 9:27 AM
To: Storey, Robert (DCSO); oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx<
mailto: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<
mailto: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.
/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?
Other related posts:
