Posted by Happy New Year to all! With the next long term release (23c) 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/23c.
-- 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 
Great to see this – I’m a new follower having returned to Oracle from SQL DBA work and loving what Brent Ozar does for the SQL community. Hoping that your ‘post every day’ new years resolution is one you can stick to