Have an issue with an external table definition after a recent upgrade to database version 11.2.0.3.0, this may simply be down to the fact that the rules have been tightened up, but thought I would ask here after unsuccessful google queries.
Unfortunately we have a large number of table definitions with a column name called ZONE, which I know is a reserved word... can't be helped as its a 10 year old appplication with a lot of historical issues like this... it probably needs redesigning from the ground up to take advantage of new techniques but we're not likely to be given the time to do this anytime soon.
I've had to change column names in the example below due to security issues, but can confirm the error still occurs. This is our table definition :-
CREATE TABLE tmp_table
(
ID VARCHAR2(10 BYTE),
STATUS_CODE VARCHAR2(255 BYTE),
COD_ID VARCHAR2(255 BYTE),
GROUP_ID VARCHAR2(15 BYTE),
TITLE VARCHAR2(200 BYTE),
COL1 DATE,
COL2 DATE,
COL3 DATE,
COL4 NUMBER(4,1),
COL5 NUMBER(5,0),
COL6 VARCHAR2(48 BYTE),
ZONE VARCHAR2(10 BYTE),
COL7 VARCHAR2(10 BYTE),
COL8 VARCHAR2(200 BYTE),
COL9 VARCHAR2(200 BYTE),
COL10 VARCHAR2(200 BYTE),
COL11 DATE,
COL12 DATE,
COL13 VARCHAR2(10 BYTE),
COL14 NUMBER(5,0),
COL15 VARCHAR2(1 BYTE),
COL16 VARCHAR2(32 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY EXTERNAL_TABLE_DIR ACCESS PARAMETERS (
records delimited BY 0x'0A'
fields terminated BY ',' optionally enclosed BY '"'
( id CHAR,
status_code CHAR,
cod_id CHAR,
group_id CHAR,
title CHAR,
col1 DATE "DD/MM/YYYY HH24:MI:SS",
col2 DATE "DD/MM/YYYY HH24:MI:SS",
col3 DATE "DD/MM/YYYY HH24:MI:SS",
col4 CHAR,
col5 INTEGER external(30),
col6 CHAR,
zone CHAR,
col7 CHAR,
col8 CHAR,
col9 CHAR,
col10 CHAR,
col11 DATE "DD/MM/YYYY HH24:MI:SS",
col12 DATE "DD/MM/YYYY HH24:MI:SS",
col13 CHAR,
col14 CHAR,
col15 CHAR,
col16 CHAR) )
LOCATION ('test.csv' )
)
REJECT LIMIT 1000 ;
The following is a couple records to stick in a test.csv file
010000,Not Started,SOMETHING,1234,COD1,01/01/2013 08:00:00,01/01/2013 08:00:00,01/01/2013 08:00:00,0,0,SOMETHING,WB,,Some Guy,Support,COD,,,,,,
010005,Not Started,SOMETHING,1234,COD2,01/01/2013 08:00:00,01/01/2013 08:00:00,01/01/2013 08:00:00,0,0,SOMETHING,WB,,Some Guy,Support,COD,,,,,,
The table creates OK, yet when I come to select data from it I get the following error displayed :-
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "zone": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 14 column 12
The error disappears if I change the field called ZONE to something else, for instance calling it ZONE1 fixes the error.
Is this a bug? I know its a reserved word but we don't see the same issue under 10.2.0.5.0 the version we are upgrading from.