Skip to Main Content
  • Questions
  • Oracle External Table ODCIEXTTABLEOPEN error while selecting count(*) of the table

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Partheeban.

Asked: June 24, 2016 - 8:28 pm UTC

Answered by: Connor McDonald - Last updated: March 09, 2019 - 2:32 am UTC

Category: Database - Version: Oracle 10g

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: The SQL Loader log file as data

You Asked

Hi

I am explaining from beginning,

My Server is : UNIX BOX (HPSA)

I have created the below folder in this UNIX server and folder permissions are

mm13pb:/ $ ls -ltr
drwxr-xr-x 7 root root 4096 Dec 15 2013 data

mm13pb:/data $ ls -ltr
drwxrwxrwx 13 root sys 4096 Oct 29 2015 download

mm13pb:/data/download $ ls -ltr
drwxrwxr-x 31 uid_load users 8192 Jun 24 16:29 uid_load

mm13pb:/data/download/uid_load $ ls -ltr
drwxrwxr-x 2 uid_load users 4096 Jun 24 20:18 ETL_DATA_DIRECTORY

mm13pb:/data/download/uid_load/ETL_DATA_DIRECTORY $ ls -ltr
-rwxrwxr-x 1 uid_load users 145473300 Jun 24 15:29 RXD_ACCOUNT_MAPPING_AUTO.CSV

--Create UNIX Folder directory
--data\download\uid_load\ETL_DATA_DIRECTORY

--Create Oracle Directory
CREATE OR REPLACE DIRECTORY ETL_DATA_DIRECTORY AS '/data/download/uid_load/ETL_DATA_DIRECTORY';

--Grant READ and WRITE permission to this ETL_DATA_DIRECTORY in Oracle.
GRANT READ, WRITE ON DIRECTORY ETL_DATA_DIRECTORY TO UID_LOAD;

--CREATE EXTERNAL TABLE for RXD_ACCOUNT_MAPPING_AUTO.CSV
CREATE TABLE UID_OWNER_ETL.RXDACCTMAP_EXT_TAB
(
RBSG_SORT_CODE NUMBER,
RBSG_ACCOUNT_NUM NUMBER,
BRAND VARCHAR2(10),
RBSG_TARGET_SORT_CODE NUMBER,
WG_TARGET_SORT_CODE NUMBER,
WG_TARGET_ACCOUNT_NUM NUMBER,
SWITCH_RAINBOW_INDICATOR NUMBER,
SEPARATION_STATUS NUMBER,
ACCOUNT_PLATFORM VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ETL_DATA_DIRECTORY
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(
RBSG_SORT_CODE,RBSG_ACCOUNT_NUM,BRAND,RBSG_TARGET_SORT_CODE,WG_TARGET_SORT_CODE,WG_TARGET_ACCOUNT_NUM,SWITCH_RAINBOW_INDICATOR,SEPARATION_STATUS,ACCOUNT_PLATFORM
)
)
LOCATION('RXD_ACCOUNT_MAPPING_AUTO.CSV')
)
parallel 5
reject limit unlimited;

GRANT SELECT ON UID_OWNER_ETL.RXDACCTMAP_EXT_TAB TO UID_LOAD;

---
---

mm13pb:/data/download/uid_load/ETL_DATA_DIRECTORY $ sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 24 20:54:52 2016

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> SELECT COUNT(*) FROM UID_OWNER_ETL.RXDACCTMAP_EXT_TAB;
SELECT COUNT(*) FROM UID_OWNER_ETL.RXDACCTMAP_EXT_TAB
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file RXDACCTMAP_EXT_TAB_6533.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19


This issue is from production server I am unable to get the count of oracle external table, can you please help me

thanks
Partheeban.J


and we said...

Being able to *read* the file is insufficient in this case, because as you query an external table, we also want to *write* a log file. Since you are using the 'DEFAULT DIRECTORY' clause, we will write the log file to the same location as your file.

Either that location needs to be read/write, or you can specify an explicit (different) directory for the log file.

Hope this helps.

and you rated our response

  (2 ratings)

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

Reviews

Oracle External Table ODCIEXTTABLEOPEN error while selecting count(*) of the table

July 01, 2016 - 1:55 pm UTC

Reviewer: Partheeban J

Thank Connor,

Sorry for Delay, The issue in UNIX production server in my company. so after a week I got a permission to recheck the issue in server.

It is fully based on UNIX ACCESS PERMISSION issue. Oracle user group is not able to write the access in the ETL_DATA_DIRECTORY folder. I changed the permission to ORACLE user instead of UID_LOAD USER.

The issue is resolved. Now I can get the count(*) and data

Thanks
Partheeban.J
Connor McDonald

Followup  

July 02, 2016 - 1:44 am UTC

Thanks for taking to time to get back to us.

March 08, 2019 - 12:16 pm UTC

Reviewer: A reader

ORACLE USER, Meaning your trying to provide access to system, I am not getting the last part.
Connor McDonald

Followup  

March 09, 2019 - 2:32 am UTC

Files are written by the account the the database is running under. That OS account needs write access to the directory you want write files to. And *reading* an external table might want to *write* log files.