title: Can't Read Files using External Tables or Write Files using UTL_FILE using PL/SQL
body: <p>I have an issue wherein I can't Read a simple File using an External Table, nor can I write files using <code>UTL_FILE</code>. I think this has something to do with the permissions but I can't figure it out.</p>
  
  <p>I confirmed that <code>APPS</code> and <code>PUBLIC</code> have the sufficient privileges:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And I also confirmed that the actual Directory is defined:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Below is the privileges of the directory <code>/u01/app/oracle/DEV/SAMPLE</code></p>
  
  <pre><code>[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
  </code></pre>
  
  <p>When I try to write a simple <code>UTL_FILE</code> command below:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>it results into an error like below:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>And I even tried reading from an External Table:</p>
  
  <pre><code>CREATE TABLE sample_ext
  ( sample1 varchar(10) )
  organization external (
    default directory EXT_TAB_DATA
    location ('test.txt')
    );
  /
  
  select  *
  from    sample_ext;  
  /
  </code></pre>
  
  <p>It results into an error below:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>I confirmed that the file is there and has data:</p>
  
  <pre><code>[appldev-run@hamlfinappdev ~]$ cd /u01/app/oracle/DEV/SAMPLE
  [appldev-run@hamlfinappdev ~]$ cat test.txt
  1
  1
  1
  1
  [appldev-run@hamlfinappdev ~]$
  </code></pre>
  
  <p>What do I need to do to read and write to this directory?</p>
  
  <p>Database Version is as Follows:</p>
  
  <pre><code>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
  </code></pre>