CODEWITHSUNDEEP
linuxdatabaseoracle-databaseunixplsql
Migs
Migs

Reputation: 1480

Can't Read Files using External Tables or Write Files using UTL_FILE using PL/SQL

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

Upvotes: 3

Views: 10751

Answers (2)

David
David

Reputation: 1

For Oracle on Windows: (credit goes to Chris Saxon...)

  • check oracle installed directory (ORACLE_HOME)

    open properties (context menu)

    click the security tab

        group or user names:  ORA_***_SVCACCTS

  • Add the ORA_***_SVCACCTS to your oracle directory

    open properties (context menu)

    click the security tab

        group or user names:  add the ORA...SVCACCTS

  • Unit test with the utl_file (works!!!)

Upvotes: 0

Dmitry Demin
Dmitry Demin

Reputation: 2113

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

Upvotes: 1

Related Questions