oracle sql loader

database administration Hits: 95

Hello! SQL Loader is one of those tools that boast very high speeds for loading data into the database. Sometimes, it feels like it memorizes part of the data! 😄

Here's the test file we'll be working with today:

DATE,NUMBER_TEST,PHONE,TEXT_TEST
2018/08/23-11:10:17,121,78903456789,1_row
2018/08/24-11:11:17,131,78903256789,2_row
2018/08/22-11:12:17,141,78903456789,3_row
2018/08/27-11:13:17,151,78903456789,4_row
2018/08/24-11:14:17,161,78903456789,5_row
2018/08/29-11:15:17,171,78903456789,6_row
2018/08/21-11:20:17,181,78903456789,7_row
2018/08/24-11:30:17,191,78903356789,8_row
2018/08/20-11:40:17,101,78903436789,9_row
2018/08/24-11:50:17,111,78903456789,10_row
2018/08/22-12:10:17,121,78903456789,11_row
2018/08/21-13:10:17,131,78903456789,12_row
2018/08/13-14:10:17,141,78903453339,13_row
2018/08/14-15:10:17,161,78903456739,14_row
2018/08/25-15:11:17,171,78903456389,15_row
2018/08/29-12:10:17,181,78903453789,16_row
2018/08/21-11:10:27,191,78903456789,17_row

To use SQL Loader, we need a control file that specifies the table structure and how to map the data:

 
[oracle@db11g-node2 ~]$ cat sqlldr.ctl
OPTIONS
( SKIP=1,
PARALLEL=true,
DIRECT=true,
SKIP_INDEX_MAINTENANCE=true )
LOAD DATA
APPEND
INTO TABLE VAHID_TEST_SQLLDR FIELDS
TERMINATED BY ","
TRAILING
NULLCOLS
( ID sequence,
DATE date 'yyyy/mm/dd-hh24:mi:ss',
NUMBER_TEST,
PHONE,
TEXT_TEST,
TEXT_CONSTANT constant 'vahidnowrouzi.blog.ir' )

Key Points in the Control File:

  1. Auto-Generating Sequence Values: Initially, I tried using sequence1.nextval for the ID field but encountered issues. Eventually, I found that simply using the keyword sequence allows SQL Loader to handle this automatically. You can find more details here.

  2. Date Format Handling: The control file includes the date format specification to correctly parse and insert date values into a DATE field.

  3. Adding Constant Values: The control file also demonstrates how to add constant values for specific fields, as shown with TEXT_CONSTANT.

Table Creation

The table to hold the imported data is created as follows:

 
CREATE TABLE VAHID_TEST_SQLLDR
( "ID" NUMBER(20,0),
"DATE" DATE,
"NUMBER_TEST" NUMBER(3,0),
"PHONE" VARCHAR2(20 BYTE),
"TEXT_TEST" VARCHAR2(50),
"TEXT_CONSTANT" VARCHAR2(50) );

Running SQL Loader

Execute the SQL Loader command as follows:

 
sqlldr vahid/vahid data=/home/oracle/sqlldr.data control=/home/oracle/sqlldr.ctl log=/home/oracle/sqlldr.log bad=/home/oracle/sqlldr.bad

The log and bad parameters specify files that will be created to log the process and capture any problematic records, respectively.

This ensures that any issues can be tracked and resolved based on these log files.