MacLochlainns Weblog

I was playing around with some external table filtering examples, and I stumbled on a restriction that I’d previously missed. You can’t filter external data with SQL functions, like REGEXP_LIKE. Unfortunately, the limitation applies to equality and non-equality validation in combination with the AND and OR operators.

CREATE TABLE item_load
( item_title    VARCHAR2(60)
, item_subtitle VARCHAR2(60)
, release_date  DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload_source
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD_LOG':'item_load.bad'
      DISCARDFILE 'UPLOAD_LOG':'item_load.dis'
      LOGFILE     'UPLOAD_LOG':'item_load.log'
      LOAD WHEN (REGEXP_LIKE(item_title,'^Harry.'))
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
      LOCATION ('item_load.csv'))
REJECT LIMIT UNLIMITED;

It threw the following exception, which includes a new error message (at least for me), the KUP-01005. I suppose that basically means you can’t use function calls inside external table access parameters but I couldn’t find it in the documentation. Chapter 13 (the lucky number) in the Oracle Database Utilities 11g manual only provides examples of equality and non-equality.

Here’s the raise exception for those using external tables:

SQL> SELECT * FROM item_load;
SELECT * FROM item_load
              *
ERROR at line 1:
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 "(": expecting one OF: "equal, notequal"
KUP-01007: at line 5 COLUMN 29

I couldn’t leave it alone, I tried the LIKE operator and a jackpot – another new error message:

KUP-01008: the bad identifier was: LIKE

It makes sense, it’s the 13th of July and two days before the new Harry Potter flick, therefore it must be Chapter 13 was calling to me. Hope this helps a few folks.