UTl_FILE invalid directory path error

You need to have your DBA specify the directory you want to write to in the UTL_FILE_DIR variable that is set by the DBA.

Or you can use this query to see the directories (if you have rights to the v$parameter view)

select value
from v$parameter
where name = 'utl_file_dir'
/


BTW: Especially with UTL_FILE, it's important to have an exception block. At least to close the File. If you omit this, the file will remain open (and locked) for the duration of your session.
Another point is the exception UTL_FILE can raise. These are in the "user-reserved" ranges (-20000 thru -20999) If you simply reraise you get a user-defined exception. (except for the one you encountered, it was added as a regular exception in 9i)

For utl_file to work, the directory has to be "approved" first.  With Oracle9i, you (or your DBA) can do this either with:
1. a directory object that is created via SQL*Plus
2. a UTL_FILE_DIR entry in your spfile (or init*.ora file).

Older versions of Oracle only supported the UTL_FILE_DIR approach, but this is less flexible, since it can only be changed by shutting down and restarting the instance.  With Oracle9i, it is much better to create directories via SQL*Plus.

Here is an example of a "create directory..." command from our system (on Linux):
CREATE OR REPLACE DIRECTORY LOG AS '/var/ora_logs';
grant read, write on log to public;

Note that when you use these directory names (like: LOG) with utl_file, they must be specified in upper case.

With oracle 9i no need to set  UTL_FILE_DIR.

The following executes successfully but no directory created.

SQL> exec admin_qa.test_dir('outputfile');
v_dyn_sql_grant grant create any directory to ADMIN_QA v_dyn_sql_dir create or
replace directory outputfile_admin_qa as '/tmp/outputfile_admin_qa' dir path  as
'/tmp/outputfile_admin_qa'

PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE test_dir (p_user_dir_nm IN VARCHAR2 := NULL)
IS
   v_dyn_sql_dir      VARCHAR2 (200);
   v_dyn_sql_grant    VARCHAR2 (200);
   v_dyn_sql_revoke   VARCHAR2 (200);
   v_user_dir         VARCHAR2 (200);
   v_user             VARCHAR2 (50);
   v_default_dir      VARCHAR2 (100)  := 'tmp';
   v_path             VARCHAR2 (200);
BEGIN
   SELECT USER
     INTO v_user
     FROM DUAL;

   IF (p_user_dir_nm IS NULL)
   THEN
      v_user_dir := 'outputfile_' || v_user;
   ELSE
      v_user_dir := p_user_dir_nm || '_' || v_user;
   END IF;

   v_user_dir := LOWER (v_user_dir);
   v_path := ' as ''/' || v_default_dir || '/' || v_user_dir || '''';
   v_dyn_sql_grant := 'grant create any directory to ' || v_user;
   v_dyn_sql_revoke := 'revoke create any directory from  ' || v_user;
   v_dyn_sql_dir := 'create or replace directory '||v_user_dir ||'' ||v_path;
   
DBMS_OUTPUT.put_line ('v_dyn_sql_grant '|| v_dyn_sql_grant || ' v_dyn_sql_dir ' || v_dyn_sql_dir|| ' dir path ' || v_path);

   EXECUTE IMMEDIATE (v_dyn_sql_grant);
   EXECUTE IMMEDIATE (v_dyn_sql_dir);
   EXECUTE IMMEDIATE (v_dyn_sql_revoke);
 
END;

So, is your problem solved, or do you still have a question?

When I do select * from all_directories I see
OWNER  Name                               Path
SYS       OUTPUTFILE_ADMIN_QA    /tmp/outputfile_admin_qa

But I can not see it when I logon to unix and browse to tmp directory, why is that?

How can I test it by creating a file in it and writing to it.

According to the documentation, you can't create a "physical" directory this way.

Docs:
"You must also create a corresponding operating system directory for file storage. Your system or database administrator must ensure that the operating system directory has the correct read and write permissions for Oracle processes."

Alex

I have tried this and get error
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "ADMIN_QA.WRITE_FILE", line 9
ORA-06512: at line 3


CREATE OR REPLACE PROCEDURE write_file (p_user_dir_nm IN VARCHAR2 :=NULL)
IS
   log_handle          UTL_FILE.FILE_TYPE;
   log_file            VARCHAR2 (200);
   log_line            VARCHAR2 (32767);

BEGIN
   log_file := 'test_' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || '.txt';
   log_handle := UTL_FILE.FOPEN ('OUTPUTFILE_ADMIN_QA', log_file, 'w');
   --DBMS_OUTPUT.put_line ('user ' || v_user || ' dir path ' || v_path );
   UTL_FILE.put_line (log_handle, 'this is a test');

   IF UTL_FILE.is_open (log_handle)
   THEN
      UTL_FILE.fclose (log_handle);
   END IF;
END;
/

Did you create this path: /tmp/outputfile_admin_qa  on the database server?

Alex

You have to use operating system tools to create the physical directory (or folder) first, then you can use the "create directory..." command in Oracle to make this physical directory available to PL\SQL.  Also, remember that the "Oracle" user needs operating system privileges on this folder.  That is the "oracle" user account in unix that runs the database, *not* the logon account in Oracle that will run the procedure.

I created directory  outputfile_admin_qa under tmp and did chmod 777 still get same error
Do I need to  login as oracle and then create the directory.

Problem was missing slash
VERY IMPORTANT make SURE SLASH is before tmp create or replace directory OUTPUTFILE_ADMIN_QA as '/tmp/outputfile_admin_qa'

File is created but 'this is a test' is not written to file?



CREATE OR REPLACE PROCEDURE write_file (p_user_dir_nm IN VARCHAR2 := NULL)
IS
/*
VERY IMPORTANT make SURE SLASH is before tmp create or replace directory OUTPUTFILE_ADMIN_QA as '/tmp/outputfile_admin_qa'
*/
   log_handle   UTL_FILE.file_type;
   log_file     VARCHAR2 (200);
   log_line     VARCHAR2 (32767);
BEGIN
   log_file := 'test_' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || '.txt';
   log_handle := UTL_FILE.fopen ('OUTPUTFILE_ADMIN_QA', log_file, 'w');
   UTL_FILE.put_line (log_handle, 'this is a test');

   IF UTL_FILE.is_open (log_handle)
   THEN
      UTL_FILE.fclose (log_handle);
   END IF;
END;
/

I'm stumped.... it looks fine, it should work...

Sometimes, it takes some time for the procedure to comlete it's task. Even when it says procedure sucessfully completed, it may still be "working" on the file.
How long did you wait to check the contents of the file?

Alex

can you simplify and run as simple PL/SQL script?

declare
   log_handle   UTL_FILE.file_type;
BEGIN
   log_handle := UTL_FILE.fopen ('OUTPUTFILE_ADMIN_QA', 'test.txt', 'w');
   UTL_FILE.put_line (log_handle, 'this is a test');
   IF UTL_FILE.is_open (log_handle) THEN
      UTL_FILE.fclose (log_handle);
   END IF;
END;

Any update?  500 points just sitting around....

might be corresponding directory object does not exist. If not then create a corresponding
           directory object with the CREATE DIRECTORY command