Skip to Main Content
  • Questions
  • Cannot read external table or read using utl_file.get_line on Windows 10 with Oracle 18c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Steve.

Asked: November 25, 2020 - 9:54 pm UTC

Last updated: December 17, 2020 - 4:38 am UTC

Version: 18c XE

Viewed 1000+ times

You Asked

I just upgraded to Oracle 18c XE from 11g. I have an external table that worked fine on 11g, but I keep getting the following errors on 18c.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11604: no directory object specified for log file

The directory does exist and I have the correct grants.

SELECT *
FROM   all_directories
WHERE  directory_name = 'MYDIR';


OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
SYS MYDIR C:\Users\sneuf\OneDrive\PLSQL\OracleDirectory 1

SELECT * 
FROM all_tab_privs 
WHERE table_name = 'MYDIR' 
AND grantee = 'C##_SNEUF';


GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED
SYS C##_SNEUF SYS MYDIR EXECUTE YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR READ YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR WRITE YES NO NO DIRECTORY NO


I'm pretty sure I'm missing a grant somewhere, but I can't figure out what.

Here is my table:

CREATE TABLE C##_SNEUF.CHECKING_TBL_EXT2 
   (  
  DB_KEY NUMBER, 
  CHECK_NUM VARCHAR2(10), 
  TRANS_DATE TIMESTAMP (6), 
  DESCRIPTION VARCHAR2(100), 
  DEPOSIT_WITHDRAWAL VARCHAR2(1), 
  AMOUNT VARCHAR2(12), 
  MEMO VARCHAR2(200)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY MYDIR
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE MYDIR: 'checking.bad'
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    LDRTRIM
    MISSING FIELD VALUES ARE NULL
(
  DB_key CHAR,
  check_num CHAR(10),
  trans_date CHAR(21) DATE_FORMAT DATE MASK 'MM/DD/YYYY HH24:MI:SS',
  description CHAR(100),
  deposit_withdrawal CHAR(1),
  amount CHAR(12),
  memo CHAR(200)
            )
                     )
      LOCATION
       ( MYDIR: 'checking.csv'
       )
    )
   REJECT LIMIT UNLIMITED ;


Thanks, Steve

and we said...

We need to write a log file based on the load of the data. You didn't specify one.

You can do:

ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY MYDIR
  ACCESS PARAMETERS (

which means everything comes from/goes to MYDIR, or you'd need to add:

BADFILE MYDIR: 'checking.bad'
LOGFILE MYDIR: 'checking.log'

Rating

  (8 ratings)

Comments

NOLOGFILE option

Rajeshwaran, Jeyabal, November 27, 2020 - 11:32 am UTC

or we can even tell "NOLOGFILE" option - something like this:

TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS(
 RECORDS DELIMITED BY NEWLINE
 NOLOGFILE
 BADFILE MYDIR: 'checking.bad'
 FIELDS TERMINATED BY ','


Connor McDonald
November 30, 2020 - 2:18 am UTC

True, but when things go wrong, everyone always says

"Why did you use NOLOGFILE?!?!?"

:-)

Already done

Steve Neufeld, November 27, 2020 - 12:53 pm UTC

I already have DEFAULT DIRECTORY MYDIR. See my table above.
Connor McDonald
November 30, 2020 - 2:43 am UTC

Apologies - I missed that.

Lets start simple - what happens with a standard user in the pluggable that has create directory privs (or do it as SYSTEM). This from my 18c XE (linux)

SQL> create directory mydir as '/tmp';

Directory created.

SQL> CREATE TABLE ext
  2     (
  3    col VARCHAR2(1000)
  4     )
  5     ORGANIZATION EXTERNAL
  6      ( TYPE ORACLE_LOADER
  7        DEFAULT DIRECTORY MYDIR
  8        ACCESS PARAMETERS
  9        ( RECORDS DELIMITED BY NEWLINE
 10      BADFILE MYDIR: 'checking.bad'
 11      FIELDS TERMINATED BY ","
 12      OPTIONALLY ENCLOSED BY '"'
 13      LDRTRIM
 14      MISSING FIELD VALUES ARE NULL
 15  (
 16    col CHAR(1000)
 17              )
 18                       )
 19        LOCATION
 20         ( MYDIR: 'dummy.txt'
 21         )
 22      )
 23     REJECT LIMIT UNLIMITED ;

Table created.

SQL>
SQL> select * from ext;

COL
-----------------------------------------------------------------
total 112
drwxr-xr-x.  2 oracle oinstall    98 Apr 10  2019 addnode
drwxr-xr-x.  9 oracle oinstall    86 Apr 10  2019 assistants
drwxr-xr-x.  2 oracle oinstall  8192 Apr 10  2019 bin
drwxrwx---.  3 oracle oinstall    16 Apr 10  2019 cfgtoollogs
drwxr-xr-x.  4 oracle oinstall    83 Apr 10  2019 clone
drwxr-xr-x.  6 oracle oinstall    51 Apr 10  2019 crs
drwxr-xr-x.  3 oracle oinstall    17 Apr 10  2019 css
drwxr-xr-x. 11 oracle oinstall  4096 Apr 10  2019 ctx
drwxr-xr-x.  7 oracle oinstall    66 Apr 10  2019 cv
drwxr-xr-x.  3 oracle oinstall    19 Apr 10  2019 data


Still not working

Steve, November 30, 2020 - 3:29 pm UTC

Mine still doesn't work. I'm on Windows 10, not Linux. I'm not seeing the problem. I even added a log file. It doesn't get created. It just says it can't find the .csv file. It IS there. I do have permissions to read/write to it. What am I missing? It worked just fine with Oracle 9g. Has something about grants changed with 18c or before?
Chris Saxon
November 30, 2020 - 5:47 pm UTC

I noticed that you're creating this as a common user (c## prefix) - i.e. one that's in the container database and all PDBs. But the privileges for this can be granted locally to each PDB. For more on this see:

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/overview-of-the-multitenant-architecture.html#GUID-16473474-7F47-4E40-A592-01836E7D911C

It's possible that you're running in a different container to the one this user was granted privileges in. Or that it just doesn't have write privs on the directory to create the logfile.

Please share your complete code - including create directory, create user & its privileges.

To Steve

J. Laurindo Chiappa, November 30, 2020 - 4:27 pm UTC

Hello, Steve : see, the main difference between Oracle 9i/10g and Oracle 18c under Windows is : 18c installer require you to create a new Windows user OR to use the built-in account - maybe the user created by your demand does not have the privs to the folder in question ?

Check the logon properties of your Window's service called OracleServiceNNN , where NNN is your SID...

Regards,

J. Laurindo Chiappa

An example...

J. Laurindo Chiappa, November 30, 2020 - 6:06 pm UTC

Hi - here is my example (with Oracle 19c under Windows 10 Home Edition, but the principles are the same - and don´t mind the system messages in Portuguese, these are due to my machine regional settings) :

=> the folder C:\TEMP exists AND it's available for admins (AND the Windows account in use by Oracle is a local admin), so everything works :

PS C:\temp> get-acl


Diretório: C:\


Path Owner Access
---- ----- ------
temp BUILTIN\Administradores BUILTIN\Administradores Allow FullControl...


PS C:\temp>

=> first, I will create a dummy text file in the required folder :

C:\Users\User 2am>dir C:\ORANT > C:\TEMP\dummy.txt

=> next, I run the given external table commands (again, ignore the Portuguese system messages) :

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> create directory mydir as 'C:\temp';

Diretório criado.

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> CREATE TABLE ext
2 (
3 col VARCHAR2(1000)
4 )
5 ORGANIZATION EXTERNAL
6 ( TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY MYDIR
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE
10 BADFILE MYDIR: 'checking.bad'
11 FIELDS TERMINATED BY ","
12 OPTIONALLY ENCLOSED BY '"'
13 LDRTRIM
14 MISSING FIELD VALUES ARE NULL
15 (
16 col CHAR(1000)
17 )
18 )
19 LOCATION
20 ( MYDIR: 'dummy.txt'
21 )
22 )
23 REJECT LIMIT UNLIMITED ;

Tabela criada.

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> select * from ext;

COL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
O volume na unidade C n¿tem nome.
O N¿mero de S¿e do Volume ¿686-7E64

Pasta de C:\ORANT

30/11/2019 19:11 <DIR> .
30/11/2019 19:11 <DIR> ..
30/11/2019 19:08 <DIR> BIN
30/11/2019 19:08 <DIR> DBS
30/11/2019 19:08 <DIR> MSHELP
30/11/2019 19:11 <DIR> ORAINST
30/11/2019 19:08 <DIR> SHARED_DLL
30/11/2019 19:11 <DIR> TOOLS
0 arquivo(s) 0 bytes
8 pasta(s) 38.082.998.272 bytes dispon¿is

15 linhas selecionadas.

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB>


=> That´s it : the Windows account in use by Oracle is not the owner of the C:\TEMP folder BUT this account have local sysadmin capacity AND the folder is allowed to admins, so everything works...

==> Now, I will create another folder and another text file inside it :

C:\Users\User 2am>mkdir C:\ANOTHER_DIR

C:\Users\User 2am>dir c:\orant > C:\ANOTHER_DIR\dummy.txt

C:\Users\User 2am>dir c:\ANOTHER_DIR
O volume na unidade C não tem nome.
O Número de Série do Volume é B686-7E64

Pasta de c:\ANOTHER_DIR

30/11/2020 14:40 <DIR> .
30/11/2020 14:40 <DIR> ..
30/11/2020 14:40 557 dummy.txt
1 arquivo(s) 557 bytes
2 pasta(s) 37.912.240.128 bytes disponíveis

C:\Users\User 2am>

PS C:\ANOTHER_DIR> get-acl


Diretório: C:\


Path Owner Access
---- ----- ------
ANOTHER_DIR BUILTIN\Administradores BUILTIN\Administradores Allow FullControl

Path Owner Access
---- ----- ------
test_dir BUILTIN\Administradores Allow FullControl...

=> I´m running cmd.exe with the 'Run as Administrador' option, so due to this the ownership is give to Administrator´s group... Now, using Windows Explorer, I will change the ownership of the folder to a non-admin account (GUEST built-in account) and deny the acess for Admin group members, the result is :

PS C:\ANOTHER_DIR> get-acl


Diretório: C:\


Path Owner Access
---- ----- ------
ANOTHER_DIR E550-2AM\Convidado BUILTIN\Administradores Deny FullControl...

==> Now I will try to access this folder via Oracle directory, see :

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> drop directory mydir;

Diretório eliminado.

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> create directory mydir as 'C:\ANOTHER_DIR';

Diretório criado.

SYSTEM@ORCL19CPDB::CNTNR=ORCL19CPDB> select * from ext;
select * from ext
*
ERRO na linha 1:
ORA-29913: erro ao executar chamada ODCIEXTTABLEOPEN
ORA-29400: erro no cartucho de dados
KUP-04040: arquivo dummy.txt em MYDIR não encontrado

The file EXISTS but I don´t have the needed privs so I´m receiving this message....

As I said, I don´t know if THIS is your problem, but could be, verify...

Regards,

Chiappa
Connor McDonald
December 02, 2020 - 12:37 am UTC

Nice input

My create directory, user, and privileges code

Steve, November 30, 2020 - 11:56 pm UTC

I have never worked with common users, container databases or PDBs. This used to be much easier...

CREATE OR REPLACE DIRECTORY MYDIR AS 'C:\Users\sneuf\OneDrive\PLSQL\OracleDirectory';
grant execute,read,write on directory MYDIR to c##_sneuf;

-- Create the user
create user C##_SNEUF
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke object privileges
grant execute, read, write on directory MYDIR to C##_SNEUF;
-- Grant/Revoke role privileges
grant execute_catalog_role to C##_SNEUF;
grant resource to C##_SNEUF;
grant scheduler_admin to C##_SNEUF;
grant select_catalog_role to C##_SNEUF;
-- Grant/Revoke system privileges
grant alter any index to C##_SNEUF;
grant alter any materialized view to C##_SNEUF;
grant alter any procedure to C##_SNEUF;
grant alter any role to C##_SNEUF;
grant alter any sequence to C##_SNEUF;
grant alter any table to C##_SNEUF;
grant alter any trigger to C##_SNEUF;
grant alter any type to C##_SNEUF;
grant alter session to C##_SNEUF;
grant alter user to C##_SNEUF;
grant create any directory to C##_SNEUF;
grant create any index to C##_SNEUF;
grant create any indextype to C##_SNEUF;
grant create any job to C##_SNEUF;
grant create any materialized view to C##_SNEUF;
grant create any procedure to C##_SNEUF;
grant create any sequence to C##_SNEUF;
grant create any table to C##_SNEUF;
grant create any trigger to C##_SNEUF;
grant create any type to C##_SNEUF;
grant create any view to C##_SNEUF;
grant create database link to C##_SNEUF;
grant create job to C##_SNEUF;
grant create materialized view to C##_SNEUF;
grant create procedure to C##_SNEUF;
grant create public database link to C##_SNEUF;
grant create public synonym to C##_SNEUF;
grant create sequence to C##_SNEUF;
grant create synonym to C##_SNEUF;
grant create table to C##_SNEUF;
grant create trigger to C##_SNEUF;
grant create type to C##_SNEUF;
grant create user to C##_SNEUF;
grant create view to C##_SNEUF;
grant debug any procedure to C##_SNEUF;
grant debug connect session to C##_SNEUF;
grant delete any table to C##_SNEUF;
grant drop any directory to C##_SNEUF;
grant drop any index to C##_SNEUF;
grant drop any indextype to C##_SNEUF;
grant drop any materialized view to C##_SNEUF;
grant drop any procedure to C##_SNEUF;
grant drop any sequence to C##_SNEUF;
grant drop any synonym to C##_SNEUF;
grant drop any table to C##_SNEUF;
grant drop any trigger to C##_SNEUF;
grant drop any type to C##_SNEUF;
grant drop any view to C##_SNEUF;
grant drop public database link to C##_SNEUF;
grant drop public synonym to C##_SNEUF;
grant drop user to C##_SNEUF;
grant execute any indextype to C##_SNEUF;
grant execute any procedure to C##_SNEUF;
grant execute any program to C##_SNEUF;
grant execute any type to C##_SNEUF;
grant flashback any table to C##_SNEUF;
grant grant any object privilege to C##_SNEUF;
grant grant any privilege to C##_SNEUF;
grant insert any table to C##_SNEUF;
grant select any sequence to C##_SNEUF;
grant select any table to C##_SNEUF;
grant unlimited tablespace to C##_SNEUF;
grant update any table to C##_SNEUF;
Connor McDonald
December 02, 2020 - 12:36 am UTC

And are you then running your definitions/queries from the container or from the pluggable ?

container or pluggable?

Steve, December 13, 2020 - 6:57 pm UTC

I don't know how to tell that. Sorry.
Connor McDonald
December 14, 2020 - 6:09 am UTC

Run

select con_id, name from v$pdbs;

Script results

Steve, December 15, 2020 - 3:16 pm UTC

CON_ID NAME
1 2 PDB$SEED
2 3 PDB1

Connor McDonald
December 17, 2020 - 4:38 am UTC

OK, this means you are running your scripts in the root container.

Now would be a good time to gain an understanding of the pluggable database architecture, because its the only game in town going forward.

A good starting point is here

https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234

and there's a nice video in there as well, but I've linked it in direcltly below



For 18c XE, you'll be wanting to connect to the XEPDB1 pluggable database, and you don't need all the "C##" prefix stuff in that instance - you use the database just like you would a normal database.




More to Explore

Design

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