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
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