I have an issue wherein I can't Read a simple File using an External Table, nor can I write files using `UTL_FILE`. I think this has something to do with the permissions but I can't figure it out.
I confirmed that APPS and PUBLIC have the sufficient privileges:
select GRANTEE from all_tab_privs
where table_name = 'EXT_TAB_DATA'
and privilege = 'WRITE';
GRANTEE
--------
APPS
PUBLIC
And I also confirmed that the actual Directory is defined:
select * from all_directories
where directory_name = 'EXT_TAB_DATA';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
------ -------------- -------------------------- -----------------
SYS EXT_TAB_DATA /u01/app/oracle/DEV/SAMPLE 0
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 like below:
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 an 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@hamlfinappdev ~]$ cd /u01/app/oracle/DEV/SAMPLE
[appldev-run@hamlfinappdev ~]$ cat test.txt
1
1
1
1
[appldev-run@hamlfinappdev ~]$
What do I need to do to read and write to this directory?
Database Version is as Follows:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production