SELECT FROM EXTERNAL returns error with column name TIMESTAMP
updated on October 21, 2024By Christian Pfundtner
Oracle has known the concept of external tables for many years. Since Oracle 18c, you can use this function without explicitly creating an external table EXTERNAL().
As a test, the contents of t1.dat should be loaded:
"SYS","OBJ$","2024-10-11:15:19:56","VALID"
"SYS","I_OBJ3","2024-10-11:15:19:56","VALID"
In the example, the function is EXTERNAL()used to load test data into a table.
SELECT * FROM EXTERNAL (
(
OWNER VARCHAR2(22),
OBJECT_NAME VARCHAR2(121),
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(5)
)
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ARCH$_TMP_LS1M4DG
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 't1.bad'
LOGFILE 't1.log'
fields terminated by "," optionally enclosed by '"'
missing field values are null
(
owner CHAR(22),
object_name CHAR(121),
timestamp CHAR(19),
status CHAR(5)
)
)
LOCATION ('t1.dat') REJECT LIMIT UNLIMITED)
result
WHERE rownum <= 1
/
ERROR at line 94:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered iraq telegram data while parsing access parameters
KUP-01005: syntax error: found "timestamp": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 16 column 4
Oracle complains that "timestamp" caused a syntax error. If it were a real reserved word, it would have to be quoted using double quotation marks. Let's check this in V$RESERVED_WORDS.
select keyword, reserved
from v$reserved_words
where keyword like 'TIMESTAMP%'
KEYWORD RESERVED
------------------------------ ----------
TIMESTAMP N
TIMESTAMP_TO_NUMBER N
TIMESTAMPis actually listed, but with RESERVED = 'N' it is not treated as a real reserved keyword.
Let's look in the data dictionary to see how often there TIMESTAMPis a column with the name:
select owner, count(*)
from dba_tab_columns
where column_name='TIMESTAMP'
group by owner;
OWNER COUNT(*)
---------- ----------
SYS 166
SYSTEM 5
APPQOSSYS 4
OJVMSYS 1
DVSYS 6
OUTLN 1
Apparently the name is TIMESTAMPquite popular as a column. This was already the case many years ago, when TIMESTAMPOracle did not yet have the data type. With the introduction of the data type TIMESTAMP- in reality, Oracle knows many different versions of the data type internally! There is also TIMESTAMP(0), TIMESTAMP(1), ... Each of the 10 possible scalings is its own data type (times 3, because there are also combinations with WITH TIME ZONE and WITH LOCAL TIME ZONE). It is reasonable to assume that this is only so complicated (3 data types would have been enough: without TIME ZONE, WITH TIME ZONE and WITH LOCAL TIME ZONE) so that there are no problems with the existing column names Timestamp in the data dictionary. Oracle makes changes to the data dictionary - with the exception of additional columns - extremely rarely so that existing queries continue to work with the new Oracle database versions.
The solution / workaround