SQL & PL/SQL

Using 10g Realease 2 on Solaris. We are not using init.ora parameter 'utl_file_dir', but are trying to use a directory.

create or replace directory debug_dir as '/db03/inteface/debug' ;

jimmyb@oradev> select * from dba_directories
  2  where directory_name = 'DEBUG_DIR';

OWNER                DIRECTORY_NAME                 DIRECTORY_PATH
-------------------- ------------------------------ ------------------------
SYS                  DEBUG_DIR                      /db03/inteface/debug

grant read,write on directory debug_dir to dba ;

jimmyb@oradev> select table_name,grantee,privilege
  2  from dba_tab_privs
  3  where table_name = 'DEBUG_DIR';

TABLE_NAME                     GRANTEE         PRIVILEGE
------------------------------ --------------- ---------------
DEBUG_DIR                      DBA             READ
DEBUG_DIR                      DBA             WRITE

jimmyb@oradev> select grantee,granted_role
  2  from dba_role_privs
  3  where granted_role = 'DBA'
  4  and   grantee = 'JIMMYB' ;
  
  GRANTEE         GRANTED_ROLE
  --------------- ------------------------------
  JIMMYB          DBA
  

jimmyb@oradev> select name,value from v$parameter
  2  where name = 'utl_file_dir';

NAME                 VALUE
-------------------- --------------------
utl_file_dir


--UNIX permissions:

$ pwd
/db03/interface
$ ls -l
total 4
drwxrwx---   2 jbrock   dba          512 Jun 19 06:44 debug

Here is my test procedure:

CREATE OR REPLACE PROCEDURE test_utl_file AS 

 dir_loc    VARCHAR2(50) := 'DEBUG_DIR';
 l_handle utl_file.file_type ;

BEGIN
 dbms_output.put_line('FOPEN');

 l_handle := utl_file.fopen(
                   location => dir_loc,
                   filename => 'testfile.log',
                   open_mode => 'a',
                   max_linesize => 32767 ) ;

 dbms_output.put_line('WRITING...');
    utl_file.put_line(l_handle,'-------') ;
    utl_file.put_line(l_handle,'WRITING') ;
    utl_file.put_line(l_handle,'-------') ;
    
 dbms_output.put_line('FCLOSE');
    utl_file.fclose( l_handle ) ;


EXCEPTION
  WHEN utl_file.access_denied THEN
    dbms_output.put_line('access_denied') ;
  WHEN utl_file.invalid_mode THEN
    dbms_output.put_line('invalid_mode') ;
  WHEN utl_file.invalid_path THEN
   dbms_output.put_line('invalid_path') ;
  WHEN utl_file.file_open THEN
      dbms_output.put_line('File Already Opened');
  WHEN utl_file.invalid_filename THEN
      dbms_output.put_line('Invalid File Name');
  WHEN utl_file.invalid_maxlinesize THEN
    dbms_output.put_line('Line Size Exceeds 32K');
  WHEN utl_file.invalid_filehandle THEN
    dbms_output.put_line('invalid_filehandle') ;
  WHEN utl_file.invalid_operation THEN
    dbms_output.put_line('invalid_operation') ;
  WHEN utl_file.read_error THEN
    dbms_output.put_line('read_error') ;
  WHEN utl_file.write_error THEN
    dbms_output.put_line('write_error') ;
  WHEN utl_file.internal_error THEN
    dbms_output.put_line('internal_error') ;
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm) ;
END test_utl_file;
/
show errors

Here is the error:

jimmyb@oradev> exec test_utl_file
FOPEN
invalid_operation

When I comment out the utl_file exceptions here is what I get:

jimmyb@oradev> exec test_utl_file
FOPEN
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 460
ORA-29283: invalid file operation

Any suggestions?