Happy New Year to all! With the next long term release (23ai) probably due some time this year, let’s start the new year with a simple change you might need to make in naming once you decide to make the switch past 19c.
I discovered this when I was doing a simple demo with external tables and got an odd error. But I was sure that I’d done the same demo in the past and had no problems. Then I realised that I was mistakenly running this demo on a 21c instance not a 19c instance.
So lets start with working the 19c example first.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> create or replace
2 directory TEMP as 'x:\temp';
Directory created.
SQL>
SQL> SELECT txt
2 FROM
3 EXTERNAL
4 (
5 (
6 txt VARCHAR2(512)
7 )
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TEMP
10 ACCESS PARAMETERS (
11 RECORDS DELIMITED BY NEWLINE
12 NOBADFILE
13 NOLOGFILE
14 NODISCARDFILE
15 FIELDS LDRTRIM
16 MISSING FIELD VALUES ARE NULL (
17 txt char(512)
18 )
19 )
20 LOCATION ('sample.txt')
21 REJECT LIMIT UNLIMITED
22 ) inline_ext_tab;
TXT
-------------------------------------------------------------------------
It was the best of times,
It was the worst of times
But this is what happens when you run the same demo on 21c (or any later release).
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> create or replace
2 directory TEMP as 'x:\temp';
Directory created.
SQL>
SQL> SELECT txt
2 FROM
3 EXTERNAL
4 (
5 (
6 txt VARCHAR2(512)
7 )
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TEMP
10 ACCESS PARAMETERS (
11 RECORDS DELIMITED BY NEWLINE
12 NOBADFILE
13 NOLOGFILE
14 NODISCARDFILE
15 FIELDS LDRTRIM
16 MISSING FIELD VALUES ARE NULL (
17 txt char(512)
18 )
19 )
20 LOCATION ('sample.txt')
21 REJECT LIMIT UNLIMITED
22 ) inline_ext_tab;
DEFAULT DIRECTORY TEMP
*
ERROR at line 9:
ORA-00922: missing or invalid option
Unfortunately that is a fairly generic error message, and I use the word “TEMP” for a lots of simple demos and they all work fine:
SQL> create table TEMP ( x int );
Table created.
Thus my initial suspicion was that there had been some change in the external table syntax rules between releases, or perhaps I had a permissions problem on the OS for that directory. All of those investigations yielded little joy, and since “TEMP” seemed to be the problematic term, I tried a new name
SQL> create or replace
2 directory TEMPX as 'x:\temp';
Directory created.
SQL>
SQL> SELECT txt
2 FROM
3 EXTERNAL
4 (
5 (
6 txt VARCHAR2(512)
7 )
8 TYPE oracle_loader
9 DEFAULT DIRECTORY TEMPX
10 ACCESS PARAMETERS (
11 RECORDS DELIMITED BY NEWLINE
12 NOBADFILE
13 NOLOGFILE
14 NODISCARDFILE
15 FIELDS LDRTRIM
16 MISSING FIELD VALUES ARE NULL (
17 txt char(512)
18 )
19 )
20 LOCATION ('sample.txt')
21 REJECT LIMIT UNLIMITED
22 ) inline_ext_tab;
TXT
----------------------------------------
It was the best of times,
It was the worst of times
and lo and behold, all was good.
Ultimately the cause of the problem was a simple one. All we need to do is take a quick look at v$reserved_words to see the changes between 19c and 21c/23ai.
-- 19c
SQL> select keyword
2 from v$reserved_words
3 where upper(keyword) like '%TEMP%';
KEYWORD
-------------------------------------------
NO_TEMP_TABLE
TEMPFILE
TEMPLATE
TEMP_TABLE
TEMPORARY
CACHE_TEMP_TABLE
MAX_SHARED_TEMP_SIZE
FAILED_LOGIN_ATTEMPTS
-- 21c
SQL> select keyword
2 from v$reserved_words
3 where upper(keyword) like '%TEMP%';
KEYWORD
-------------------------------------------
NO_TEMP_TABLE
TEMPFILE
TEMP
TEMPLATE
TEMP_TABLE
TEMPORARY
CACHE_TEMP_TABLE
MAX_SHARED_TEMP_SIZE
FAILED_LOGIN_ATTEMPTS
So… no more TEMP for me 🙂
Learning is not a spectator sport 
Got some thoughts? Leave a comment