External Table – Compressed Files

https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN12898
Below the example with External tables using PREPROCESSOR option,
this is usefull where you have compressed files. The example has been
done with Pluggable Database.

Files:
C1.txt
C2.txt
ENG,England,English
SCO,Scotland,English
IRE,Ireland,English
WAL,Wales,Welsh
FRA,France,French
GER,Germany,German
USA,Unites States of America,English
 gzip c1.txt c2.txt

-rw-r--r--. 1 oracle oinstall       77 Dec 12 16:00 c1.txt.gz
-rw-r--r--. 1 oracle oinstall       92 Dec 12 16:00 c2.txt.gz

sqlplus / as sysdba
SQL> alter session set container=PDBPROD2;
Session altered.
  • Create a directory where the DATA is placed:
    SQL> create or replace directory dir_dba as '/home/oracle/dba';
    
    Directory created.
    
    SQL> grant read , write on directory dir_dba to public;
    
    Grant succeeded.
  • Create a directory pointing to the Utility that will be used to
    UNCOPRESS the file:
    
    [oracle@vm1 dba]$ which gunzip
    /usr/bin/gunzip
    
    [oracle@vm1 dba]$ which zcat
    
    /usr/bin/zcat
    
    SQL> create or replace directory dir_bin as '/usr/bin';
    
    Directory created.
    
    SQL> grant read, write on directory dir_bin to public;
    
    Grant succeeded.
    
    Drop table cmagno.countries_ext;
    
    CREATE TABLE cmagno.countries_ext (
      country_code      VARCHAR2(5),
      country_name      VARCHAR2(50),
      country_language  VARCHAR2(50)
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY dir_dba
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        PREPROCESSOR dir_bin:'zcat'
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
          country_code      CHAR(5),
          country_name      CHAR(50),
          country_language  CHAR(50)
        )
      )
      LOCATION ('c1.txt.gz','c1.txt.gz')
    )
    PARALLEL 1
    REJECT LIMIT UNLIMITED;
    
    SQL> select * from cmagno.countries_ext;
    
    COUNT COUNTRY_NAME                                   COUNTRY_LANGUAGE
    ----- -------------------------------------------------- -----------------
    ENG   England                                        English
    SCO   Scotland                                       English
    IRE   Ireland                                        English
    WAL   Wales                                          Welsh
    ENG   England                                        English
    SCO   Scotland                                       English
    IRE   Ireland                                        English
    WAL   Wales                                          Welsh
    
    After you can check inside of the data directory to see the logs:
    
    -rw-r--r--. 1 oracle oinstall       77 Dec 12 16:00 c1.txt.gz
    -rw-r--r--. 1 oracle oinstall       92 Dec 12 16:00 c2.txt.gz
    -rw-r--r--. 1 oracle oinstall      676 Dec 12 16:59 COUNTRIES_EXT_15232.log
    -rw-r--r--. 1 oracle oinstall      676 Dec 12 16:59 COUNTRIES_EXT_15230.log

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s