Connected to Oracle database through service name, then get errors by running the below sample code. There is no problem to run the sample code while connecting to database through IPC.
$sqlplus testuser/****@DBTEST SQL>DECLARE l_output utl_file.file_type; BEGIN l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' ); utl_file.fclose( l_output ); END; / * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at line 4
$sqlplus / as sysdba SQL> DECLARE l_output utl_file.file_type; BEGIN l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' ); utl_file.fclose( l_output ); END; / PL/SQL procedure successfully completed.
CAUSES
New group “apex” is just added into oracle user for being able to read/write MY_DIR directory.
$ id oracle
uid=100(oracle)gid=200(oinstall)groups=200(oinstall),300(dba),400(apex)
RESOLUTION
Bounce the listener to make the changes effective.
$ ls -ltr|grep apex
drwxrwx---. 6 apexuser apex 4096 May 3 16:08 apex
$lsnrctl stop
$lsnrctl start
$sqlplus testuser/****@DBTEST
SQL>DECLARE
l_output utl_file.file_type;
BEGIN
l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
utl_file.fclose( l_output );
END;
/
PL/SQL procedure successfully completed.