Skip to Main Content
  • Questions
  • Permission issues when loading text file to external table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Stratum.

Asked: February 12, 2019 - 4:05 pm UTC

Last updated: March 07, 2019 - 3:55 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hey Tom,
First of all, I'm sorry if this has been asked already, I simply couldn't find an answer after days of googling.
Here it goes:
I've created a SP to load a text file into an external table on 10g as per instructed here and many more places with the following code:

create or replace PROCEDURE "DEFINE_FSDIR" (fs_dir in varchar2)
AUTHID CURRENT_USER is
q1 VARCHAR2(200) :='create or replace directory dump_dir as '''|| fs_dir ||'''';
begin
   execute immediate q1;
   commit;
end define_fsdir;

create or replace PROCEDURE "TABLE_FSDUMP" (fs_file in varchar2)
AUTHID CURRENT_USER is
q2 varchar2(300) :='create table fs_dump (txt_line varchar2(512)) ' ||
'organization external (type ORACLE_LOADER default directory dump_dir access parameters ' ||
'(records delimited by newline characterset we8pc850 fields (txt_line char(512))) location ('''|| fs_file ||'''))'; 
begin
   execute immediate(q2);
end table_fsdump;


(as an aside, the charset we8pc850 is necessary, as the files are DOS filesystem dumps with western european characters)

passing into the SP the corresponding data (directory and file) and having granted previously all corresponding privileges to the user (read write on dump_dir)

This worked swimmingly until I've upgraded to 12c.
According to oracle docs, 11g onwards works permissions through ACL and herein lies my problem.

On debugging table_fsdump, I get the following error:
Ejecutando PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '51876' )
ORA-24247: acceso de red denegado por la lista de control de acceso (ACL)
ORA-06512: en "SYS.DBMS_DEBUG_JDWP", línea 68
ORA-06512: en línea 1
El proceso ha terminado.

Just for completeness, the translation is that network acces is denied by ACL.

I can compile all SP, but on execution I get this error and on trying to do a select on fs_dump i get the following message:

ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEOPEN
ORA-29400: error de cartucho de datos
KUP-04001: error al abrir el archivo <dump_dir>\FS_DUMP_5384_2868.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.

Again translated:
Error executing call ODCIEXTTABLEOPEN
Error in Data Cartridge (i s'pose).

Apparently, the error can be corrected by adding permissions to the ACL, which I tried:

NETWORK_ACL_296C00CF7F2744BAB526D4C4E85FE189 0000000080002724 <myuser> connect true false   SYS
/sys/acls/oracle-sysman-ocm-Resolve-Access.xml 0000000080002760 <myuser> connect true false   SYS
NETWORK_ACL_59663FB0168E4C49B0530DC77E0DE03F 0000000080002788 <myuser> connect true false   SYS
NETWORK_ACL_59663FB0168E4C49B0530DC77E0DE03F 0000000080002788 <myuser> JDWP true false   SYS
NETWORK_ACL_0EF18F79FFF04C03A2BAFFB71D27E229 000000008000279A <myuser> connect true false   SYS
NETWORK_ACL_0EF18F79FFF04C03A2BAFFB71D27E229 000000008000279A <myuser> JDWP true false   SYS
NETWORK_ACL_2E5E977F9CF74578AC0804EBDD0E24BF 000000008000278E <myuser> connect true false   SYS
NETWORK_ACL_2E5E977F9CF74578AC0804EBDD0E24BF 000000008000278E <myuser> JDWP true false   SYS
NETWORK_ACL_E95E17A7B9D04177800FF724E544A8BE 00000000800027A0 <myuser> connect true false   SYS
NETWORK_ACL_E95E17A7B9D04177800FF724E544A8BE 00000000800027A0 <myuser> JDWP true false   SYS


Didn't work.
I know I messed up somewhere, I'm stumped as to how to get my SP to work again, and again, I am so sorry to bother you with this (which I'm sure) menial problem.

I want to resolve the ACL problem to get my SP to load the files again, which are the starting point for a much larger set of SP, and I can't find my way around it.

Please, and with the utmost respect, help me out!
Also, I think I followed the posting guidlines, but if not, please let me know and I'll correct it asap, and excuse my poor english, I tried to be as correct as possible given the fact that this is not my first language.

Thank you very much for your time in advance, and sorry if the post is too long.
Stratum

and Chris said...

The ACL problem is a red herring. You're getting this because you're debugging your procedure. It's nothing to do with the external table itself.

The real problem is here:

KUP-04001: error al abrir el archivo <dump_dir>\FS_DUMP_5384_2868.log


Which I believe translates as:

KUP-04001: error when opening the file


So the problem is the database can't write the log file for some reason.

Double check all your permissions. Check there's free disk space in that location. See if you can write a file to this location using utl_file. And so on.

If you're still stuck, come back with your findings after these checks.

As a side note - creating a new external table on-the-fly just because the file as a different name is kinda a bad idea. You can alter the table to change the location:

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test1.txt', 'w');
  utl_file.put_line(f, 'the first file');
  utl_file.fclose(f);
  
  f := utl_file.fopen ('TMP', 'test2.txt', 'w');
  utl_file.put_line(f, 'the second file');
  utl_file.fclose(f);
end;
/

create table t (
  c1 varchar2(20)
) organization external (
  default directory tmp
  location ( 'test1.txt' )
);

select * from t;

C1               
the first file   

alter table t location ( 'test2.txt' );

select * from t;

C1                
the second file


Or - if you're on 12.2 - you can use the external modify clause in a query to change the location within for that statement:

select * from t
  external modify ( location ( 'test1.txt' ) );

C1               
the first file  
  
select * from t
  external modify ( location ( 'test2.txt' ) );

C1                
the second file  

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up on your advice

Stratum von Stein, March 06, 2019 - 11:57 pm UTC

Hey Tom,
I tried your advice:

Double check all your permissions.
- I gave full control on the windows side to basically all ORA users and groups to the folder and the files.

Check there's free disk space in that location.
- About 100 GB

See if you can write a file to this location using utl_file.
- Following your code, I was able to write testfile1 and testfile2 successfully.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('DUMP_DIR', 'testfile1.txt', 'w');
  utl_file.put_line(f, 'the first file');
  utl_file.fclose(f);
  
  f := utl_file.fopen ('DUMP_DIR', 'testfile2.txt', 'w');
  utl_file.put_line(f, 'the second file');
  utl_file.fclose(f);
end;
/

Both files created with the corresponding text.

On trying to query t with
create table t (
  c1 varchar2(20)
) organization external (
  default directory dump_dir
  location ( 'testfile1.txt' )
);


I still get
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEOPEN
ORA-29400: error de cartucho de datos (Data Cartridge Error)
KUP-04001: error al abrir el archivo <dump_dir>\T_3128_9848.log Error opening file ....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.


So, back to square one.

I went as far es doing a clean remove and install of 12c, to no avail.

I'm tempted to get rid of 12c and get a 10whatever version, but I'm certain there has to be a logical solution to this.

Please, help!

Thank you a million times
Stratum
Chris Saxon
March 07, 2019 - 10:36 am UTC

I'm not sure what's going on here. The following works fine for me on 12.2:

create or replace directory dump_dir as '/tmp';

grant create session, create table 
  to u identified by u;
    
grant read, write on directory dump_dir to u;
  
conn u/u

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('DUMP_DIR', 'testfile1.txt', 'w');
  utl_file.put_line(f, 'the first file');
  utl_file.fclose(f);
  
  f := utl_file.fopen ('DUMP_DIR', 'testfile2.txt', 'w');
  utl_file.put_line(f, 'the second file');
  utl_file.fclose(f);
end;
/

create table t (
  c1 varchar2(20)
) organization external (
  default directory dump_dir
  location ( 'testfile1.txt' )
);

select * from t;

C1               
the first file 


What happens when you run this example?

Also, the line:

KUP-04001: error al abrir el archivo <dump_dir>\T_3128_9848.log Error opening file ....log 29913. 00000 -  "error in executing %s callout"


relates to the logfile. Not the file you're trying to read.

What happens when you create the ext table without logfiles?

create table t (
  c1 varchar2(20)
) organization external (
  default directory dump_dir
  access parameters (
    nologfile
  )
  location ( 'testfile1.txt' )
);

select * from t;

C1               
the first file  

Follow up on your advice - Part 2

Stratum von Stein, March 07, 2019 - 12:04 pm UTC

First of all, thanks a lot for being so incredibly helpful.

Here's what I did:
Log Messages translated for your convenience

create or replace directory dump_dir as '/tmp';

DUMP_DIR directory created.

--
grant create session, create table 
  to u identified by u;


Error starts in line: 3 of statement:
grant create session, create table 
  to u identified by u
Error report -
ORA-65049: The creation of a local user or role is not allowed in this container.
65049. 00000 -  "creation of local user or role is not allowed in CDB$ROOT"
*Cause:    An attempt was made to create a local user or role in CDB$ROOT.
*Action:   If trying to create a common user or role, specify CONTAINER=ALL.

--
- Modified statement accordingly:
grant create session, create table to c##u identified by c##u container=all;


Grant correct.

--
grant read, write on directory dump_dir to c##u;


Grant correct.

--
conn c##u/c##u


Connected.
The connection created by the script command CONNECT has been disconnected.

-- So, I'm using sql developer, which is why I get this message. I established a new connection with c##u to bridge this step.
--
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('DUMP_DIR', 'testfile1.txt', 'w');
  utl_file.put_line(f, 'the first file');
  utl_file.fclose(f);

  f := utl_file.fopen ('DUMP_DIR', 'testfile2.txt', 'w');
  utl_file.put_line(f, 'the second file');
  utl_file.fclose(f);
end;


Error report -
ORA-29283: invalid file operation
ORA-06512: in "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: in "SYS.UTL_FILE", line 41
ORA-06512: in "SYS.UTL_FILE", line 478
ORA-06512: in line 4
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.


-- at this point I realize I'd have to set all permissions for c##u? I stopped here, because I'm not sure if the intention is to see if this is the problem. I could go into permission settings, but I preferred to see what you'd advice.

to point 2:

create table fs_dump (txt_line varchar2(512))
organization external (type ORACLE_LOADER default directory dump_dir access parameters
(records delimited by newline characterset we8pc850 nologfile fields (txt_line char(512))) location ('testFile1.txt'));


Table FS_DUMP created.


select * from fs_dump;


ORA-29913: Error on executing call to ODCIEXTTABLEFETCH 
ORA-29400: Error in Data Cartridge
KUP-04001: Error opening file <dump_dir>\testFile1.txt
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.


So, that's where I'm at. I had tried nologfile with this result and logfile to a specific file with the ODCIEXTTABLEOPEN error.

What should I do now?

Thanks again
Stratum
Chris Saxon
March 07, 2019 - 3:54 pm UTC

A couple of things:

- Is your database running on a Windows server?

If so, you should change

/tmp

in the directory definition to

c:\path\you\want\to\write\to

e.g.

c:\temp

Ensure the Oracle Windows account has permissions to read/write to this location

- You're running in the container database!

12c moved to the multitenant architecture. All your code should live in the pluggable databases, not the container.

You can find what these are like so:

select name, pdb 
from   v$services;

NAME             PDB        
orcl             ORCL       
cdb1XDB          CDB$ROOT   
cdb1             CDB$ROOT   
SYS$BACKGROUND   CDB$ROOT   
SYS$USERS        CDB$ROOT   


Then change your connection to one of the servers where PDB <> CDB$ROOT. e.g. orcl in the list above:

alter session set container = orcl;


Find out more at https://oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1

Downgraded to 11g

Stratum von Stein, March 07, 2019 - 3:05 pm UTC

Hey Tom,
thanks for all the help and good advice. I finally gave up and installed 11g, which worked perfectly. It's a shame, and most likely my ignorance that didn't allow for a succesfull solution, and I appreciate the time you've taken.
Nevertheless, once 11g was up and running and with the exact same db, procs, functions and views I had no trouble whatsoever, everything went just smoothly.

Thanks again
Stratum
Chris Saxon
March 07, 2019 - 3:55 pm UTC

Sorry to hear that...

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.