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, privilege from all_tab_privs
where table_name = 'EXT_TAB_DATA';
GRANTEE PRIVILEGE
------- --------
APPS WRITE
APPS READ
APPS EXECUTE
PUBLIC WRITE
PUBLIC READ
PUBLIC EXECUTE
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
You need to give all the rights to the directory chmod 755 DEV or change the owner of the directory chown oracle:appdev DEV.
I repeated the situation and got the same error.
I created a test environment.
user root
esmd:/opt/oracle # ls -l |grep DEV
drwxrwx--- 3 appdev appdev 4096 2019-06-25 07:17 DEV
esmd:/opt/oracle/DEV # ls -l
total 4
drwxrwxrwx 2 oracle dba 4096 2019-06-25 07:22 SAMPLE
esmd:/opt/oracle # cd DEV/SAMPLE/
esmd:/opt/oracle/DEV/SAMPLE # ls -l
total 4
-rw-r--r-- 1 oracle dba 8 2019-06-25 07:14 test.txt
user oracle
oracle@esmd:~> more /opt/oracle/DEV/SAMPLE/test.txt
/opt/oracle/DEV/SAMPLE/test.txt: Permission denied
user appdev
oracle@esmd:~> su appdev
Password:
appdev@esmd:/opt/oracle> ls -l /opt/oracle/DEV/SAMPLE/test.txt
-rw-r--r-- 1 oracle dba 8 2019-06-25 07:14 /opt/oracle/DEV/SAMPLE/test.txt
DB user system
CREATE OR REPLACE DIRECTORY EXT_TAB_DATA AS '/opt/oracle/DEV/SAMPLE';
CREATE TABLE sample_ext
( sample1 varchar(10) )
organization external (
default directory EXT_TAB_DATA
location ('test.txt')
);
select *
from sample_ext;
07:34:14 line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
07:34:14 ORA-29400: data cartridge error
07:34:14 error opening file /opt/oracle/DEV/SAMPLE/SAMPLE_EXT_4977.log
I changed dir DEV chmod 757 DEV and All works!
appdev@esmd:/opt/oracle> ls -l /opt/oracle |grep DEV
drwxrwx--- 3 appdev appdev 4096 2019-06-25 07:17 DEV
appdev@esmd:/opt/oracle> chmod 775 DEV
appdev@esmd:/opt/oracle> ls -l /opt/oracle |grep DEV
drwxrwxr-x 3 appdev appdev 4096 2019-06-25 07:17 DEV
select *
from sample_ext;
SAMPLE1
----------------
1
1
1
1
I changed dir DEV chmod 775 DEV or chown oracle:appdev DEV and All works!
oracle@esmd:~> su
Password:
esmd:/opt/oracle # ls -l |grep DEV
drwxrwxr-x 3 appdev appdev 4096 2019-06-25 07:17 DEV
esmd:/opt/oracle # chown oracle:appdev DEV
esmd:/opt/oracle # ls -l |grep DEV
drwxrwxr-x 3 oracle appdev 4096 2019-06-25 07:17 DEV
esmd:/opt/oracle # chmod 770 DEV
esmd:/opt/oracle # ls -l |grep DEV
drwxrwx--- 3 oracle appdev 4096 2019-06-25 07:17 DEV
esmd:/opt/oracle #
select *
from sample_ext;
SAMPLE1
----------------
1
1
1
1