Re: utl_file question
Date: Mon, 25 Jun 2018 14:16:22 +0000
Message-ID: <YTOPR0101MB2233005E1C4A1EF9F5000B4AA64A0_at_YTOPR0101MB2233.CANPRD01.PROD.OUTLOOK.COM>
test using utl_file_dir='*' in your spfile you might not want to leave your filesystem this wide open though.
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> Sent: June 20, 2018 11:47 AM
To: RStorey_at_DCSO.nashville.org; 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_at_DCSO.nashville.org> 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_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-lReceived on Mon Jun 25 2018 - 16:16:22 CEST