Oracle Directory Objects and Solution of ORA-29280 and ORA-32001

Today i ws debugging a PL/SQL program. The program was using a log file that is created with utl_file without Oracle's Directory object by PL/SQL on server side. During debugging i got some errors and fixed them. Below i described how to solve them:

SQL> DECLARE
  2    f utl_file.file_type;
  3  BEGIN
  4    f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  5    utl_file.fclose(f);
  6  END;
  7  /
DECLARE
  f utl_file.file_type;
BEGIN
  f := utl_file.FOPEN('/data01/WORK/TMP', 'drop.txt', 'w');
  utl_file.fclose(f);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data02/TMP
SQL>
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> create spfile from pfile;                                             
File created.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> ALTER system SET utl_file_dir = '/data01/WORK7/TMP' scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  293601280 bytes
Fixed Size                  1302824 bytes
Variable Size              94117592 bytes
Database Buffers          197132288 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /data01/app/oracle/product/10.
                                                 1/dbs/spfiletest.ora
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /data01/WORK7/TMP
SQL>

Oracle has Directory objects that are stored as if they were tables in database. They do not have owners. So they can not possible to query such as select * from user_directories. They can be granted to users on read and write privilleges:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> create directory test_directory as 'C:\TMP';
create directory test_directory as 'C:\TMP'
ORA-01031: insufficient privileges
SQL> connect sys/sys@XE as sysdba
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> grant create any directory to hr;
Grant succeeded
SQL> connect hr/hr@XE
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL> create directory test_directory as 'C:\TMP';
Directory created
SQL> select * from user_directories;
select * from user_directories
ORA-00942: table or view does not exist
SQL> select * from all_directories;
SQL> grant read on directory test_directory to u1;
Grant succeeded

SQL> SELECT t.grantor, t.grantee, t.privilege
  2    FROM all_tab_privs t
  3   WHERE t.table_name = 'TEST_DIRECTORY';
GRANTOR                        GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
HR                             U1                             READ
SYS                            HR                             READ
SYS                            HR                             WRITE
SQL>