TEMP is a four letter word

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 🙂

One comment

  1. 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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.