jcon.no: Oracle blog

In the Oracle Magazine I read this article written by Arup Nanda. In 11g release 2 we have now got the opportunity to preprocess a file before it´s read into an external table. For instance – if the file is compressed, we can decompress it by preprocessing the file. Below you´ll find a smal example from Nanda´s article.

Create an UTILITY directory in the OS, and create an Oracle direcory as SYS:

oracle:~$ cd /u01/app/oradata/ORCL
oracle:/u01/app/oradata/ORCL$ mkdir tools
oracle:/u01/app/oradata/ORCL$ cd tools
oracle:/u01/app/oradata/ORCL/tools$ sqlplus "sys@orclvm.edb.com as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Sun Dec 18 13:08:35 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password: **********

Koblet til: 
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  ORCL         laptop                    65    25       11.2.0.2.0 20111218 5579            26    5577            00000001B83DF420 00000001B52B72A8

SQL> create directory execdir as '/u01/app/oradata/ORCL/tools';
Catalog created.

SQL> grant execute, read on directory execdir to lj;
GRANT-command succeded.

SQL> create or replace public synonym execdir for sys.execdir;
Synonym created.

To make it easy I´ll use the same directory for storing the external datafile:

oracle:/u01/app/oradata/ORCL/tools$ echo "1,John Smith,1000
> 2,Jack Smith,2000" > mydata.txt
oracle:/u01/app/oradata/ORCL/tools$ cat mydata.txt 
1,John Smith,1000
2,Jack Smith,2000
oracle:/u01/app/oradata/ORCL/tools$ gzip mydata.txt 
oracle:/u01/app/oradata/ORCL/tools$ ls -ltr
total 4
-rw-r--r-- 1 oracle oinstall 61 2011-12-18 13:19 mydata.txt.gz
oracle:/u01/app/oradata/ORCL/tools$

Så lager vi pre-prosesserings skriptet (“preload.sh”):

oracle:/u01/app/oradata/ORCL/tools$ cat preload.sh 
#!/bin/bash
/bin/gunzip -c $1
oracle:/u01/app/oradata/ORCL/tools$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 61 2011-12-18 13:19 mydata.txt.gz
-rwxrwxrwx 1 oracle oinstall 25 2011-12-18 13:29 preload.sh
oracle:/u01/app/oradata/ORCL/tools$ ./preload.sh mydata.txt.gz 
1,John Smith,1000
2,Jack Smith,2000
oracle:/u01/app/oradata/ORCL/tools$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 36 2011-12-18 13:19 minedata.txt.gz
-rwxrwxrwx 1 oracle oinstall 25 2011-12-18 13:29 preload.sh
oracle:/u01/app/oradata/ORCL/tools$ 

Then we can create a new external table against the compressed file:

SQL> create table inndata
(
  cust_id      number,
  cust_name    varchar2(20),
  credit_limit number(10)
)
organization external
(
  type oracle_loader
  default directory execdir
  access parameters
  (
    records delimited by newline
    preprocessor execdir:'preload.sh'
    fields terminated by ","
  )
location ('mydata.txt.gz')
)
/

Table created.

Then I can read from the file which is compressed:

SQL> select * from inndata;

   CUST_ID CUST_NAME            CREDIT_LIMIT
---------- -------------------- ------------
         1 John Smith                   1000
         2 Jack Smith                   2000