Using Decode Function in SQL Loader

In this short blog post, we will explain how to use Decode Function in the control file of SQL Loader to Load data. Let us setup a simple test case with some data which we will insert into a table through SQL Loader.

 

We have a test data as shown below for explaining Decode Usage:

 

Samsung I900 White H/Set 16GB,Samsung,""

IPhone 3GS 16GB Black,Apple,""

IPhone 3G S 16GB White,Apple,""

Nokia X6 16GB Black Handset,Nokia,""

Nokia X6 16GB White Handset,Nokia,""

IPhone 4 16GB Black,Apple,""

IPhone 4 16GB White,Apple,""

Sam I9000 Galaxy 16GB Black,Samsung,""

 

This data needs to be loaded into a table MOBILEPHONE using SQL Loader, the structure of which is shown below

 

CREATE TABLE MOBILEPHONE
(
PHONE_DESCRIPTION VARCHAR2(100),
PHONE_MAKE VARCHAR2(20),
PHONE_ABB VARCHAR2(10)
)

 

Now, the column PHONE_ABB takes a value of "S" if the MAKE is Samsung and E for all other cases. This means, we have to use DECODE logic while loading data for the third column. The control file example for this scenario is provided below

 

Load data

infile *

into table MOBILEPHONE

fields terminated by ',' optionally enclosed by '"'

(PHONE_DESCRIPTION,

PHONE_MAKE,

PHONE_ABB  "decode(:PHONE_MAKE, 'Samsung', 'S', 'E')") => Decode logic used in SQL Loader Control File to load data based on a condition

begindata

Samsung I900 White H/Set 16GB,Samsung,""

IPhone 3GS 16GB Black,Apple,""

IPhone 3G S 16GB White,Apple,""

Nokia X6 16GB Black Handset,Nokia,""

Nokia X6 16GB White Handset,Nokia,""

IPhone 4 16GB Black,Apple,""

IPhone 4 16GB White,Apple,""

Sam I9000 Galaxy 16GB Black,Samsung,""

 

Simple, isn't? Run this example using SQL Loader command line as shown below

 

C:\ sqlldr>sqlldr userid/pwd@db control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 17 14:24:15 2011

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

Commit point reached - logical record count 8

 

The output is on the expected lines in the table as shown below

 

Samsung I900 White H/Set 16GB               Samsung              S

IPhone 3GS 16GB Black                                 Apple                    E

IPhone 3G S 16GB White                              Apple                    E

Nokia X6 16GB Black Handset                     Nokia                    E

Nokia X6 16GB White Handset                   Nokia                    E

IPhone 4 16GB Black                                       Apple                    E

IPhone 4 16GB White                                     Apple                    E

Sam I9000 Galaxy 16GB Black                      Samsung              S

 

The log file for this load example is shown below

 

Table MOBILEPHONE, loaded from every logical record.

Insert option in effect for this table: INSERT

 

   Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

PHONE_DESCRIPTION                   FIRST     *   ,  O(") CHARACTER           

PHONE_MAKE                           NEXT     *   ,  O(") CHARACTER           

PHONE_ABB                            NEXT     *   ,  O(") CHARACTER           

    SQL string for column : "decode(:PHONE_MAKE, 'Samsung', 'S', 'E')"

 

Let us see some  more complex examples of using Decode Function in SQL Loader Control file in future posts..