?
Solved

Get "Invalid path" with UTL_FILE.Fopen

Posted on 2006-06-07
2
Medium Priority
?
4,528 Views
Last Modified: 2008-01-09
Hi all,

I've spent about an hour chasing the various threads related to the UTL_FILE package with no success. Here's what I have so far:

I'm running Oracle 8.1 on an IBM RS/6000 box running AIX 5.2 (IBM's version of Unix)
the UTL_FILE_DIR value for the currently running instance is "/tmp/work_file"
the permissions on the directory /tmp/work_file are 777 (wide open access)
I have a subdirectory under /tmp/work_file called "image"
the permissions on "image" are 777
I have a file in image called "in_page_db.txt"
the permissions on this file are 666 (read/write, anybody)
the full path is "/tmp/work_file/image/in_page_db.txt"

I'm using the following PL/SQL:




CREATE OR REPLACE PACKAGE BODY AML_IMAGE AS
/*                                                                            */
/* post_pages: update IMG_AML with data in /tmp/workfile/image/in_page_db.txt */
/*                                                                            */
PROCEDURE post_pages
IS
  fp       UTL_FILE.FILE_TYPE;
BEGIN
/* open the file */
DBMS_OUTPUT.PUT_LINE('Debug 1');
  fp := UTL_FILE.Fopen('image', 'in_page_db.txt', 'r');
DBMS_OUTPUT.PUT_LINE('Debug 2');

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    Commit;
    UTL_FILE.Fclose(fp);
    DBMS_OUTPUT.PUT_LINE('Process complete');
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('Invalid path');
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('Invalid mode');
  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('Invalid operation');
END post_pages;


It compiles successfully and runs. It produces the following output:

Debug 1
Invalid path


I've checked everything I know to check. I believe I'm overlooking something REALLY trivial. Please look it over and let me know what you think I'm doing wrong.

Thanks,

Jim
0
Comment
Question by:jrollins138
2 Comments
 
LVL 11

Accepted Solution

by:
pennnn earned 2000 total points
ID: 16855890
You got the concept wrong. The UTL_FILE_DIR doesn't define the root directory for all your UTL_FILE package file operations. It just defines the one or more directories Oracle can access through UTL_FILE. In your case you have specified that Oracle can access "/tmp/work_file". You can't access its sub-directories.
And the way to access the specified directory in PL/SQL would be by specifying that same path there:
fp := UTL_FILE.Fopen('/tmp/work_file', 'in_page_db.txt', 'r');
If it was also defined in the init.ora file's UTL_FILE_DIR parameter it will work. If not - you'll get an Invalid Path arror.
Hope that  helps!
0
 

Author Comment

by:jrollins138
ID: 16855988
Perfect. I just knew it had to be something simple. Made the changes suggested by your comments and it worked.

Thanks. I appreciate the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

840 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