KUP-01005: syntax error: found “hash” (How to fix on an External Table)

In this article, I’ll be discussing how to fix the “KUP-01005: syntax error: found "hash"” error when selecting from an external table. Here’s the error in its entirety:
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 "hash": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"

Replicating the Problem

First, let’s start with creating a basic external table:

create table drop_me_ext
(
     account# number(*,0)
   , account_name varchar2(35)
   , account_start_date date
)
organization external
(
  type oracle_loader
  default directory my_oracle_directory
  access parameters
  (
    records delimited by newline
    skip 1
    fields terminated by ',' rtrim
    missing field values are null
    (
        account#
      , account_name
      , account_start_date date 'MM/DD/YYY'
    )
  )
  location ('DropMe.csv')
)
;

If we attempt to create the external table, we don’t get any issues from Oracle.

Table DROP_ME_EXT created.

However, if we try to select from the table, we see that Oracle complains.

SQL> select *
  2  from drop_me_ext;

Error starting at line : 1 in command -
select *
from drop_me_ext
Error report -
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 "hash": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01007: at line 6 column 16

How to Fix KUP-01005: syntax error: found “hash”

The error is, frankly, one that pisses me off. Seems like Oracle got overzealous when trying to cover themselves syntactically. The problem is that we have a “#” sign in our columns. However, Oracle doesn’t have any issue with the # in the actual table definition above. But when we’re redefining the columns below, Oracle seems to now dislike it.

We can remedy the issue in the same way we can so many others (e.g. with reserved words, etc) by double quoting the identifier.

create table drop_me_ext
(
     account# number(*,0)
   , account_name varchar2(35)
   , account_start_date date
)
organization external
(
  type oracle_loader
  default directory my_oracle_directory
  access parameters
  (
    records delimited by newline
    skip 1
    fields terminated by ',' rtrim
    missing field values are null
    (
        "ACCOUNT#"
      , account_name
      , account_start_date date 'MM/DD/YYY'
    )
  )
  location ('DropMe.csv')
)
;

Of course, when when double quote the identifier, it becomes case sensitive. So, we want to capitalize it to preserve Oracle’s standard of “uppercasing all identifiers.”

Firstly, we drop the existing table…

SQL> drop table drop_me_ext purge;

Table DROP_ME_EXT dropped.

…and then, we re-create it again. (Note the change in line 18)

SQL> create table drop_me_ext
  2  (
  3       account# number(*,0)
  4     , account_name varchar2(35)
  5     , account_start_date date
  6  )
  7  organization external
  8  (
  9    type oracle_loader
 10    default directory my_oracle_directory
 11    access parameters
 12    (
 13      records delimited by newline
 14      skip 1
 15      fields terminated by ',' rtrim
 16      missing field values are null
 17      (
 18          "ACCOUNT#"
 19        , account_name
 20        , account_start_date date 'MM/DD/YYY'
 21      )
 22    )
 23    location ('DropMe.csv')
 24  )
 25  ;

Table DROP_ME_EXT created.

Finally, everything goes smoothly when we select from it. No more KUP-01005 error.

Note that we could have also fixed this by omitting the “#” from your column names altogether (for example, we could spell out ACCOUNT_NUMBER). But sometimes it’s nice to use shortcuts like this in column names. This is especially true if we are limited to 30 characters, and we have longer column names (e.g. ORDER#_LINE# instead of spelling out ORDER_NUMBER_LINE_NUMBER).

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets