[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle UTL_FILE INVALID_PATH Error

Posted on 2009-04-23
15
Medium Priority
?
1,149 Views
Last Modified: 2012-05-06
What is the correct syntax for supplying directory path/file name for utl_file.fopen?
This gives me INVALID_PATH...

File_Handle := UTL_FILE.FOPEN('/test/data/log',
                                 'err.log', 'a');



   
0
Comment
Question by:iBinc
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24213824
your syntax is correct, but the path must really exist, it must be one the database has access to and it must be listed in utl_file_dir init parameter
0
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 200 total points
ID: 24213848
Did you add  '/test/data/log' to the utl_file_dir initialisation parameter of our database ?
'/test/data/log' should exist on the host the database is running.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 500 total points
ID: 24214283
You have to declare Oracle directory where the files reside.
Before this create a physical directory and ensure that the
Oracle OS user has access to this directory:

connect sys/some_passw@alias as sysdba
create directory pump as '/test/data/log';
grant read,write on directory pump to oracle;


File_Handle := UTL_FILE.FOPEN('pump', 'err.log', 'a');
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24214340
note, oracle directories work in 9i and above.

it is still legal to specify the path directly in all version 7 and higher,
but with the caveats above.

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 24215397
If you have Oracle9 or higher, I recommend that you use a "logical directory" instead of the actual physcial path to a directory as you tried.  Using an actual physical path will only work if you have previously included this path in the utl_file_dir parameter of your init*.ora file or spfile.   If it is not in utl_file_dir now, a database shutdown and restart is required to add it.

 A "logical directory" is an object that you can create dynamically without requiring a database shutdown and restart, and it does not have to be in the init*.ora or dpfile either.  The syntax to create a logical directory is like this:
CREATE OR REPLACE DIRECTORY HOME AS '/home/oracle';

Then, you can allow other database users to use it like this:
grant read, write on directory HOME to [username];

Then, in your PL\SQL procedure you use the logical directory name (HOME, in my example and it must be in upper-case) instead of the actual physical path that you had tried.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24215918
I agree, definitely use directory objects instead of literal paths if you have the option.

Only reason I didn't mention it before was I was trying to solve the question "as presented", but definitely, if you can, use them as others have suggested above.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24216528
Logical directory works and it is dynamical parameter.
The uppercase in the directory name mentioned by Mr. Markgeer is an important remark.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 24216694
yes, unless you create your directory in double quotes to be case sensitive, like other db objects (I don't recommend doing this though) you will need to use upper case in your utl_file calls

so, slight correction to above example from schwertner...

create directory pump as '/test/data/log';
grant read,write on directory pump to oracle;

File_Handle := UTL_FILE.FOPEN('PUMP', 'err.log', 'a');   --- note PUMP instead of pump

also, I recommend NOT using sys unless you are doing an install, upgrade, recovery, startup or shutdown.

0
 

Author Comment

by:iBinc
ID: 24216709
This is great info from all of you...thanks, I'm learning.

The database I'm teaching myself on is a 10g/Express version. I saw some posts on security issues with XE but after granting permissions, still have the issue.

The app I am looking at in the (9.2) production environment (not my little XE test version)  is using the path like this...
  fhandle:= UTL_FILE.FOPEN('//'|| TRIM(gEnvironment) ||'//apps//data',
                                 'err.log', 'a');

Which from what you  describe is hard coded and every occurrence of gEnvironment must have been previously added to the utl_file_dir. What is the physical root directory, is it the oracle directory or the drive (c:)?  

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24216744
yes,  all of your gEnvironments would need to be in utl_file_dir  (which is another reason to use directory objects instead of literal paths)


the root would be the root directory of the database server itself, not your local drives and directories.  Given the path as shown above, it appears your path is on unix.  Not sure why the double slashes are being used.  that's not necessary, or even helpful
0
 

Author Comment

by:iBinc
ID: 24216804
Is it possible to do a query to determine what directories have been added to the utl_file_dir?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24216814
select value from v$parameter where name = 'utl_file_dir';
0
 

Author Comment

by:iBinc
ID: 24216911
Great Thanks to all of you...one last ?

One last question sdstuber... you recommended not using "sys"...do you mean do not create the directories from "sys"? Why is that?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24217001
it wasn't creating directories specifically,  it would work and there would be nothing bad about the directory afterward.

it's just bad practice to be SYS if you don't need to be.
SYS "cheats"  it's too powerful, it can do things that are necessarily legal for other users, by default it's not auditted (it can be, but usually isn't) and because SYS can do almost anything it wants, anything you develop while using it just "works" and then when you go to install your objects into a reasonable schema suddenly your application breaks because your application didn't need privileges before because it ran as SYS.

And lastly, it's good security practice in general (not just oracle, not just databases) to operate with an environment of least privilege. If you don't need to be able to create/alter/drop any object, then don't log in with that privilege.

That's all.
0
 

Author Comment

by:iBinc
ID: 24217107
Great thanks again!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question