hi,

I have checked both the queries. The directory is present and having write privileges also.

How can we check "the OS Oracle user has write access to it"?

please suggest.

thanks

yes,the user which the Oracle software runs under have write access still same invalid file operation issue is coming.

It's resolved, thanks.

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?

Yes I was able to get permissions and grants on the directory with the help of the DBA. The answer in SO also helped. Pasting the link to the SO answer to anyone who might need it in the future: https://stackoverflow.com/questions/56739653/cant-read-files-using-external-tables-or-write-files-using-utl-file-using-pl-sq