PreProcessor in Oracle External Tables

Question

I seem to be having an issue whenever I add the preprocessor clause into my external tables statement. Without the preprocessor line it all works fine but as soon as I add it I get assorted errors depending on where I put it in the access parameters block, do they have to be in a particular order? I can't find any similar issues online.

Edit: Also as a side note, I know you can use preprocessor to feed in a zipped file to the external table. If I want to read a zipped file AND append the filename is it as easy as just putting it all in the one shell file?

CREATE TABLE (
    column1 VARCHAR2(40),
    column2 VARCHAR2(40)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DMPDIR
    ACCESS PARAMETERS(
        RECORDS DELIMITED BY NEWLINE
        LOGFILE DMPDIR:'test.log'
        BADFILE DMPDIR:'test.bad'
        fields terminated by '^'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL FIELDS
        preprocessor DMPDIR: 'append_filename.sh'
    )
 LOCATION (DMPDIR: 'testfile.dat')
);

append_filename.sh

#!/bin/bash
sed -e 's!$!,'"${1##*/}"'!' $1

Answer 1

Here is a small example to create an external table using PREPROCESSOR. I did this on 12c:

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> DROP TABLE validation_header;

Table dropped.

SQL>
SQL> CREATE TABLE validation_header (
  2  header1 VARCHAR2(5),
  3  header2 number,
  4  header3 VARCHAR2(8),
  5  header4 number
  6  )
  7  ORGANIZATION EXTERNAL (
  8  TYPE oracle_loader
  9  DEFAULT DIRECTORY data_pump_dir
 10  ACCESS PARAMETERS (
 11  RECORDS DELIMITED BY NEWLINE
 12  PREPROCESSOR import_ppm:'script.ksh'
 13  LOGFILE import_ppm:'script.log'
 14  FIELDS TERMINATED BY ','
 15  (header1,header2,header3, header4
 16  ))
 17  LOCATION ('script_file.txt')
 18  );

Table created.

SQL>

From the documentation,

KUP-04094: preprocessing cannot be performed if Database Vault is installed

• Cause: The Database Vault option did not permit the execution a program from an Oracle directory object.

• Action: If preprocessing must be performed, then the Database Vault option must not be installed.