Get "Invalid path" with UTL_FILE.Fopen

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
jrollins138Asked:
Who is Participating?
 
pennnnCommented:
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
 
jrollins138Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.