Hi, I have almost the same issue. I confirmed that APPS and PUBLIC have the sufficient privileges:
SQL> select GRANTEE from all_tab_privs
where table_name = 'EXT_TAB_DATA'
and privilege = 'WRITE'; 2 3
GRANTEE
--------
APPS
PUBLIC
SQL>
And I also confirmed that the actual Directory is defined:
SQL> select * from all_directories
where directory_name = 'EXT_TAB_DATA'; 2
OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
EXT_TAB_DATA
/u01/app/oracle/DEV/SAMPLE
0
SQL>
Below is the privileges of the directory /u01/app/oracle/DEV/SAMPLE
[appldev-run ~]$ ls -l /u01/app/oracle/DEV
total 24
-rw-r--r-- 1 appldev appldev 6473 Jun 19 15:10 EBSapps.env
drwxr-xr-x 5 appldev appldev 4096 Jun 4 10:13 fs1
drwxr-xr-x 5 appldev appldev 4096 Jun 7 16:26 fs2
drwxr-xr-x 4 appldev appldev 4096 May 22 12:32 fs_ne
-rw------- 1 root root 0 Sep 18 2018 nohup.out
drwxrwxrwx 2 oracle appldev 4096 Jun 25 02:31 SAMPLE
When I try to write a simple UTL_FILE command below:
declare
fHandle UTL_FILE.FILE_TYPE;
begin
fHandle := UTL_FILE.FOPEN('EXT_TAB_DATA', 'test_file', 'w');
UTL_FILE.PUT(fHandle, 'This is the first line');
UTL_FILE.PUT(fHandle, 'This is the second line');
UTL_FILE.PUT_LINE(fHandle, 'This is the third line');
UTL_FILE.FCLOSE(fHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
RAISE;
end;
/
it results into an error:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 14
29283. 00000 - "invalid file operation"
*Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
*Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.
And I even tried reading from an External Table:
CREATE TABLE sample_ext
( sample1 varchar(10) )
organization external (
default directory EXT_TAB_DATA
location ('test.txt')
);
/
select *
from sample_ext;
It results into the error below:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04001: error opening file /u01/app/oracle/DEV/SAMPLE/SAMPLE_EXT_62883.log
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
I confirmed that the file is there and has data:
[appldev-run@xxxx ~]$ cd /u01/app/oracle/DEV/SAMPLE
[appldev-run@hamlfinappdev ~]$ cat test.txt
1
1
1
1
[appldev-run@xxxx ~]$
What do I need to do to read and write to this directory?