Hello All,
I upgraded the version Oracle DB from Oracle Databes 12c Enterprise Edition Release 12.1.0.2.0 to Oracle Databes 19c Enterprise Edition Release 19.0.0.0.
I'am trying to load data into my database using an external table.
My external table definition:
create table
TAB_EXT_8094845(
A_S_TYPE INTEGER,
N_I_ADDR VARCHAR2(255),
N_P INTEGER,
A_S_ID VARCHAR2(255),
C_S_ID VARCHAR2(255),
CD_S_ID VARCHAR2(255),
A_T_CAUSE INTEGER,
A_S_TIME VARCHAR2(255),
S_TIME TIMESTAMP,
C_TIME TIMESTAMP,
D_TIME TIMESTAMP,
A_S_E_REALM VARCHAR2(255),
IGN VARCHAR2(255),
A_S_I_REALM VARCHAR2(255),
A_P_D_DELAY INTEGER,
A_P_A_ID VARCHAR2(255),
A_S_DIV VARCHAR2(255),
A_S_DISP INTEGER,
A_D_INIT INTEGER,
A_D_CA VARCHAR2(255),
A_S_STAT VARCHAR2(255),
A_E_F_R_NU VARCHAR2(255),
D_A VARCHAR2(255),
D_A_ORG VARCHAR2(255),
D_B VARCHAR2(255),
D_B_ORG VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY MY_EXTERNAL_TABLES
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOLOGFILE
FIELDS TERMINATED BY ',' optionally enclosed by '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(A_S_TYPE, N_I_ADDR, N_P, A_S_ID, C_S_ID, CD_S_ID, A_T_CAUSE, A_S_TIME, S_TIME char(40) date_format TIMESTAMP WITH TIMEZONE MASK '.hh24:mi:ss.ff3 TZD MON dd yyyy.', C_TIME char(40) date_format TIMESTAMP WITH TIMEZONE MASK '.hh24:mi:ss.ff3 TZD MON dd yyyy.', D_TIME char(40) date_format TIMESTAMP WITH TIMEZONE MASK '.hh24:mi:ss.ff3 TZD MON dd yyyy.', IGN, A_S_E_REALM, A_S_I_REALM, A_P_D_DELAY, A_P_A_ID, A_S_DIV, A_S_DISP, A_D_INIT, A_D_CA, A_S_STAT, A_E_F_R_NU, D_A, D_A_ORG, D_B, D_B_ORG))
LOCATION ('src_file')
)
PARALLEL
REJECT LIMIT 0
NOMONITORINGIt works on version 12c, but on version 19c it stopped working.
When I try to run query, I get this errors:
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
I think there may be something wrong with the dates (S_TIME, C_TIME, D_TIME). An example values from the file:
10:51:50.332 CEST Sep 06 2023
00:00:00.000 UTC Jan 01 1970
10:51:50.527 CEST Sep 06 2023
10:51:50.169 CEST Sep 06 2023
00:00:00.000 UTC Jan 01 1970
10:51:50.504 CEST Sep 06 2023
Do you have any clues what is wrong? Thanks in advance!
CEST is a time zone abbreviation. From MOS note 340512.1:
Using a Timezone Abbreviation (select TZABBREV from V$TIMEZONE_NAMES;) with TO_TIMESTAMP_TZ or FROM_TZ will also give a ORA-1882. You can only use Timezone Names (select TZNAME from V$TIMEZONE_NAMES;)
CEST is an abbreviation, thus converting it alone won't work. You need to include the region too. For example:
select to_timestamp_tz ( 'CEST', 'TZD' ) from dual;
--ORA-01857: not a valid time zone
select to_timestamp_tz ( 'Europe/Berlin CEST', 'TZR TZD' ) from dual;
/*
TO_TIMESTAMP_TZ('EUROPE/BERLINCEST','TZRTZD'
--------------------------------------------
01-OCT-2023 00.00.00.000000000 EUROPE/BERLIN
*/
There have been a few fixes & changes to time zone handing between 12.1 and 19c, you may have been impacted by these. Or perhaps you were lucky in the past and only received abbreviations that are names too (e.g. UTC, GMT)