Thanks Tom.
This is another of a growing list of reasons for us to upgrade to 11.2.

That's a great addition to Ext Table.

Tom:

I have the simillar problem where the data files are in WinRar format (.rar extenstion) and the database server (11.2.0.1) is available in Windows XP.

C:\oracle\11.2.0\admin\ORA11GR2\dpdump>dir
 Volume in drive C has no label.
 Volume Serial Number is 74FA-88EC

 Directory of C:\oracle\11.2.0\admin\ORA11GR2\dpdump

07/06/2011  10:39 AM    <DIR>          .
07/06/2011  10:39 AM    <DIR>          ..
07/06/2011  10:36 AM               166 DEPT.rar
07/06/2011  10:06 AM               108 DEPT_EXT_2480_10648.bad
07/06/2011  10:44 AM               359 DEPT_EXT_2480_10648.log
04/13/2011  12:43 PM               119 dp.log
               5 File(s)            858 bytes
               2 Dir(s)  172,342,779,904 bytes free

C:\oracle\11.2.0\admin\ORA11GR2\dpdump>
C:\oracle\11.2.0\admin\ORA11GR2\dpdump>

rajesh@ORA11GR2>
rajesh@ORA11GR2> CREATE TABLE dept_ext
  2  (
  3    "DEPTNO" VARCHAR(4000),
  4    "DNAME" VARCHAR2(14),
  5    "LOC" VARCHAR2(13)
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY DATA_PUMP_DIR
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
 14      PREPROCESSOR DATA_PUMP_DIR:'rar'
 15      BADFILE DATA_PUMP_DIR:'ctl.bad'
 16      READSIZE 1048576
 17      FIELDS TERMINATED BY "," LDRTRIM
 18      MISSING FIELD VALUES ARE NULL
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        "DEPTNO" CHAR(255)
 22          TERMINATED BY ",",
 23        "DNAME" CHAR(255)
 24          TERMINATED BY ",",
 25        "LOC" CHAR(255)
 26          TERMINATED BY ","
 27      )
 28    )
 29    location
 30    (
 31      'DEPT.rar'
 32    )
 33  )REJECT LIMIT UNLIMITED
 34  /

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from dept_ext;
select * from dept_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file rar in C:\oracle\10.2.0\admin\ora11gr2\dpdump\ not found


Elapsed: 00:00:00.09
rajesh@ORA11GR2>

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/et_params.htm#SUTIL1385

Can you help me how to get the PREPROCESSOR file_spec for windows?

Tom:

I was reading this article published in Oracle Magazine
http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html
And, when I tried to re-run the example, I ended up with this error message. Please help me what i am doing wrong here.

I am using WinZip 12.1 so I have the data file as '.Zipx' extension rather than .zip extension.

create directory pp_dir as 'C:\TRASH';
GRANT EXECUTE ON directory pp_dir to public;
 
CREATE TABLE EXT_DEPT_PP
(
  DEPTNO         NUMBER(2),
  DNAME          VARCHAR2(14),
  LOC                VARCHAR2(13)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
            preprocessor pp_dir:'text_zip.bat'
    BADFILE DATA_PUMP_DIR:'ctl.bad'
    LOGFILE 'log.txt_xt'
    READSIZE 1048576
    SKIP 1
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )   
  )
  location
  (
    'data.zipx'
  )
)REJECT LIMIT UNLIMITED
 
 
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from ext_dept_pp;
 
no rows selected
 
Elapsed: 00:00:02.62
rajesh@ORA11GR2>
rajesh@ORA11GR2>

Log file Contents
LOG file opened at 07/27/11 00:21:36

Field Definitions for table EXT_DEPT_PP
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields

Fields in Data Source:

DEPTNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DNAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
LOC CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader


Bad File Contents
C:\oracle\11.2.0\admin\ORA11GR2\dpdump>dir ctl*
Volume in drive C has no label.
Volume Serial Number is 74FA-88EC

Directory of C:\oracle\11.2.0\admin\ORA11GR2\dpdump

07/27/2011 12:06 AM 0 ctl.bad
1 File(s) 0 bytes
0 Dir(s) 170,994,970,624 bytes free

C:\oracle\11.2.0\admin\ORA11GR2\dpdump>

Contents of TEXT_ZIP.BAT
@echo off
C:\oracle\product\11.2.0\db_1\bin\unzip.exe -q %1

you have unzip.exe in your $ORACLE_HOME\bin directory??

C:\oracle\product\11.2.0\db_1\bin>dir unzip*
 Volume in drive C has no label.
 Volume Serial Number is 74FA-88EC

 Directory of C:\oracle\product\11.2.0\db_1\bin

06/17/2005  12:59 AM           167,936 unzip.exe
               1 File(s)        167,936 bytes
               0 Dir(s)  170,743,242,752 bytes free

C:\oracle\product\11.2.0\db_1\bin>

search your file system on your server and see if you don't have more than one text_zip.bat file

C:\TRASH>dir text*
 Volume in drive C has no label.
 Volume Serial Number is 74FA-88EC

 Directory of C:\TRASH

07/26/2011  11:57 PM                61 text_zip.bat
               1 File(s)             61 bytes
               0 Dir(s)  170,743,181,312 bytes free

C:\TRASH>

it looks like your script you are running is not the one you claim. The script you are running seems to be executing a dir command.

How do you say this, its executing a dir command?

that isn't how to search a disk drive, that dir command shows the contents of one directory

PP_DIR directory is pointed to C:\TRASH

create directory pp_dir as 'C:\TRASH';
GRANT EXECUTE ON directory pp_dir to public;

So, why do you want to search a disk drive (for existance of text_zip.bat) and not one directory (C:\TRASH) ?

The example is really good one. But I have concern here.

I think bash script here is taking content of all the files in current directory (so even if there are more than 2 files mentioned will also be considered) hence defeating the purpose of files mentioned in Location clause of create table statement.
Please confirm?

Also I tried applying the same example to my scenario but it fails. Below is what I did.

Shell Script:

[oracle@IE1FUX004 Test_Dir]$ cat filename.sh
#!/bin/sh
for FILE in `ls *.vrt`;
do
awk '{OFS= "|"}{$0 = FILENAME OFS $0; print}' $FILE
done

Create Table SQL:

CREATE TABLE et_and_crh_vrt_records_pol
   (FILE_NAME   VARCHAR2(255),
   WHOLE_RECORD  VARCHAR2(560 BYTE))
 ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY TEST_DIR
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
      preprocessor TEST_DIR:'filename.sh'
      FIELDS terminated by "|" LDRTRIM
      )
    LOCATION ('pol_l.vrt','eur_l.vrt')
   )REJECT LIMIT UNLIMITED

Though script filename.sh works fine when run as

 [oracle@IE1FUX004 Test_Dir]$ ./filename.sh 

prints the content of all the desired files with their name appended But same throws an error when a select statement is executed. I think script is expecting filename while it is called from sql select statement?

Could you please suggest on what could be wrong here?

[oracle@IE1FUX004 Test_Dir]$ cat ET_AND_CRH_VRT_RECORDS_POL_29132.log


 LOG file opened at 11/23/15 17:42:00

Field Definitions for table ET_AND_CRH_VRT_RECORDS_POL
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    FILE_NAME                       CHAR (255)
      Terminated by "|"
      Trim whitespace same as SQL Loader
    WHOLE_RECORD                    CHAR (560)
      Terminated by "|"
      Trim whitespace same as SQL Loader
KUP-04095: preprocessor command /home/oracle/Test_Dir/filename.sh encountered error "/home/oracle/Test_Dir/filename.sh: line 5: ls: No such file or directory
"

thanks for replying.

Found the reason for error in my previous review.

I guess there are few limitations on the commands for the shell scripts that are called though external tables.

In my script usage of ls command was wrong instead I have replaced it with $* ($* represents the files mentioned in Location clause) which resolved the problem.

Using multiple files for creating External table, but SKIP 1 is only skipping the 1st record from file1 and but not not the file2.

When selecting the records from external table, can see the header of file in query result.

Please assist! Thanks!

preprocessor.sh
===============
for i in $*
do
  awk '{print NR,$0}' $i
done

Thanks for quick reply. However this code for Preprocessor file is giving an error.

I need to load 200 CSV files in a external table by skipping 1st row from each CSV file. Names of these files will be like:
1. klm_points-100-120
2. klm_points-130-120
3. klm_points-20-40
4. klm_points-50-100
5. klm_points-40-50 ....

Can you please help with code for .sh file which can solve my expectations.

Hi Tom,

We are building a new system that needs to ingest a small variety of files in different formats:
- per-record csv: a record id followed by data (so if your 'employee' entity has 2 records in the upstream system, you get 2 rows in the file)
- per-entity csv: entity id followed by data (so if your 'employee' entity has 2 records in the upstream system, you get 1 row in the file with individual attributes concatenated)
- XML: a few different sources having their own file/data structures, but pretty vanilla otherwise
- plaintext file in some format defined by the upstream system that's colon-delimetered and has a new line for every data element (i.e., REC:123 <newline> DOB: 1970 <newline>, etc. with REC starting each record)

What is the most efficient way of loading all these files into the system that could span all of these formats? Would that be Data Pump? External Tables? SQL Loader? (presumably not the latter, but...)

DB is currently on 11gR1 with the plan to migrate to 12cR2, but it's not clear whether this file ingestion needs to go live before or after. Ideally would be good to get your recomm for 12cR2 (unless it's something that won't work on 11gR1)...

Many thanks as always!

Team,

please see below, able to run the shell script from the current file location, but failed when execution from External table.
could you please help us to understand what i am missing here?
(Environment is Oracle Exadata X7, with DB as 12.2)

create directory DIR1 as '/home/oracle';
grant read on directory DIR1 to c##rajesh;
grant write on directory DIR1 to c##rajesh;
grant execute on directory DIR1 to c##rajesh;
create directory EXEC_DIR as '/home/oracle/TEMP';
grant execute on directory EXEC_DIR to c##rajesh;

 
drop table emp_ext  purge;
CREATE TABLE emp_ext
(
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR EXEC_DIR:'read_zip.sh'
    NOBADFILE  
    NOLOGFILE  
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS    
  )
  location
  (
    'emp.zip'
  )
)REJECT LIMIT UNLIMITED;

c##rajesh@PDB1> select * from emp_ext ;
select * from emp_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file read_zip.sh in /home/oracle/TEMP not found


c##rajesh@PDB1> ! ls -ltr /home/oracle/TEMP
total 4
-rwxrwxrwx 1 oracle oinstall 109 Jul 11 00:19 read_zip.sh

c##rajesh@PDB1> ! cat /home/oracle/TEMP/read_zip.sh
#!/bin/bash

x="$(/usr/bin/unzip -Z1 $1 | /bin/egrep -i 'emp[0-9]+.csv')"
# echo $x
/usr/bin/unzip -p $1 $x


c##rajesh@PDB1> ! ./home/oracle/TEMP/read_zip.sh /home/oracle/emp.zip
/bin/bash: ./home/oracle/TEMP/read_zip.sh: No such file or directory

c##rajesh@PDB1> host
[oracle@en701c202vm02 ~]$ ./home/oracle/TEMP/read_zip.sh /home/oracle/emp.zip
bash: ./home/oracle/TEMP/read_zip.sh: No such file or directory

[oracle@en701c202vm02 ~]$ cd /home/oracle/TEMP
[oracle@en701c202vm02 TEMP]$ ls -ltr
total 4
-rwxrwxrwx 1 oracle oinstall 109 Jul 11 00:19 read_zip.sh

[oracle@en701c202vm02 TEMP]$ ./read_zip.sh /home/oracle/emp.zip
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-1980 12:00:00 AM,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-1981 12:00:00 AM,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-1981 12:00:00 AM,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-1981 12:00:00 AM,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-1981 12:00:00 AM,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-1981 12:00:00 AM,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-1981 12:00:00 AM,2450,,10
7788,"SCOTT","ANALYST",7566,09-DEC-1982 12:00:00 AM,3000,,20
7839,"KING","PRESIDENT",,17-NOV-1981 12:00:00 AM,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-1981 12:00:00 AM,1500,0,30
7876,"ADAMS","CLERK",7788,12-JAN-1983 12:00:00 AM,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-1981 12:00:00 AM,950,,30
7902,"FORD","ANALYST",7566,03-DEC-1981 12:00:00 AM,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-1982 12:00:00 AM,1300,,10
[oracle@en701c202vm02 TEMP]$

In case somebody finds themselves here and is on Windows, here's a .bat version of Chris Saxon's shell/awk script that worked for me:
(gnu awk required)
@echo off
"C:/Program Files (x86)/GnuWin32/bin/awk.exe" "BEGIN{OFS= \""|\""}{n=split(FILENAME,array,\""\\\\\"");$0 = array[n] OFS $0; print}" %1

The split() and array[n] is to get the basename, just use FILENAME like the original if the whole path is what you want.