SQL*Loader Basics

SQL*Loader Basics

Oracle SQL*Loader is a high-performance bulk data loading utility that efficiently loads data from external files into Oracle database tables. It’s designed for loading large volumes of data with minimal overhead.

What is SQL*Loader?

SQL*Loader is Oracle’s premier bulk loading tool that provides:

• High-performance loading - Direct path and conventional path options
• Flexible data formats - Fixed, delimited, and variable record formats
• Data transformation - Built-in functions and expressions
• Error handling - Comprehensive logging and bad record management
• Loading modes - INSERT, APPEND, REPLACE, TRUNCATE
• Parallel loading - Multiple concurrent SQL*Loader sessions

SQL*Loader Architecture

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Data Files    │    │   SQL*Loader    │    │ Oracle Database │
│ (CSV, Fixed,    │───►│    Process      │───►│    Tables       │
│  Variable)      │    │                 │    │                 │
└─────────────────┘    └─────────────────┘    └─────────────────┘
         │                       │                       │
         ▼                       ▼                       ▼
┌─────────────────────────────────────────────────────────────────┐
│                    Control & Output Files                      │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐            │
│  │ Control File│  │  Log File   │  │  Bad/Discard │            │
│  │ (Metadata & │  │ (Execution  │  │    Files     │            │
│  │  Commands)  │  │   Log)      │  │  (Errors)    │            │
│  └─────────────┘  └─────────────┘  └─────────────┘            │
└─────────────────────────────────────────────────────────────────┘

Core Components

1. Input Data File

Contains the actual data to be loaded:

employees.csv

101,John,Doe,MANAGER,50000,2023-01-15
102,Jane,Smith,ANALYST,45000,2023-02-01
103,Bob,Johnson,CLERK,35000,2023-03-01

2. Control File

Defines how to load the data:

-- employees.ctl
LOAD DATA
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
    employee_id,
    first_name,
    last_name,
    job_title,
    salary,
    hire_date DATE "YYYY-MM-DD"
)

3. SQL*Loader Command

sqlldr userid=hr/password control=employees.ctl log=employees.log

Basic Loading Examples

1. Simple CSV Load

Data File (employees.csv):

101,John,Doe,50000
102,Jane,Smith,45000
103,Bob,Johnson,35000

Control File (load_employees.ctl):

LOAD DATA
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(
    employee_id INTEGER EXTERNAL,
    first_name CHAR(20),
    last_name CHAR(20),
    salary INTEGER EXTERNAL
)

Command:

sqlldr hr/password control=load_employees.ctl log=load_employees.log

2. Fixed-Width Data Load

Data File (fixed_employees.dat):

101John      Doe       50000
102Jane      Smith     45000
103Bob       Johnson   35000

Control File (fixed_load.ctl):

LOAD DATA
INFILE 'fixed_employees.dat'
INTO TABLE employees
(
    employee_id POSITION(1:3) INTEGER EXTERNAL,
    first_name POSITION(4:13) CHAR,
    last_name POSITION(14:23) CHAR,
    salary POSITION(24:28) INTEGER EXTERNAL
)

3. Loading with Data Transformation

Control File (transform_load.ctl):

LOAD DATA
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(
    employee_id,
    first_name UPPER(:first_name),
    last_name UPPER(:last_name),
    full_name ":first_name || ' ' || :last_name",
    salary,
    hire_date DATE "YYYY-MM-DD",
    created_date SYSDATE
)

Loading Modes

1. INSERT (Default)

Loads data into empty tables:

LOAD DATA
INFILE 'data.csv'
INSERT INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name)

2. APPEND

Adds data to existing table data:

LOAD DATA
INFILE 'new_employees.csv'
APPEND INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name)

3. REPLACE

Deletes existing data and loads new data:

LOAD DATA
INFILE 'all_employees.csv'
REPLACE INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name)

4. TRUNCATE

Truncates table and loads new data:

LOAD DATA
INFILE 'complete_employees.csv'
TRUNCATE INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name)

Data Types and Formatting

1. Numeric Data

LOAD DATA
INFILE 'numbers.csv'
INTO TABLE test_numbers
FIELDS TERMINATED BY ','
(
    id INTEGER EXTERNAL,
    price DECIMAL EXTERNAL,
    quantity NUMBER,
    percentage FLOAT EXTERNAL
)

2. Date and Time Data

LOAD DATA
INFILE 'dates.csv'
INTO TABLE test_dates
FIELDS TERMINATED BY ','
(
    id,
    birth_date DATE "MM/DD/YYYY",
    hire_timestamp TIMESTAMP "YYYY-MM-DD HH24:MI:SS",
    modified_date DATE "YYYY-MM-DD" DEFAULTIF modified_date=BLANKS
)

3. Character Data with Special Handling

LOAD DATA
INFILE 'text_data.csv'
INTO TABLE test_text
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    id,
    description CHAR(100),
    notes CHAR(500) "UPPER(:notes)",
    status CHAR(10) DEFAULTIF status=BLANKS
)

Error Handling

1. Bad Records Management

LOAD DATA
INFILE 'employees.csv'
BADFILE 'employees.bad'    -- Records that couldn't be loaded
DISCARDFILE 'employees.dis' -- Records that didn't meet criteria
INTO TABLE employees
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
    employee_id,
    first_name,
    last_name,
    salary "DECODE(:salary, '', NULL, :salary)"
)

2. Error Limits

LOAD DATA
INFILE 'employees.csv'
BADFILE 'employees.bad'
ERRORS=100              -- Stop after 100 bad records
ROWS=1000              -- Commit every 1000 rows
INTO TABLE employees
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name, salary)

Advanced Features

1. Loading Multiple Tables

LOAD DATA
INFILE 'employee_data.csv'
INTO TABLE employees
WHEN (1:1) = 'E'        -- Employee records start with 'E'
FIELDS TERMINATED BY ','
(
    record_type FILLER,  -- Don't load this field
    employee_id,
    first_name,
    last_name
)
INTO TABLE departments
WHEN (1:1) = 'D'        -- Department records start with 'D'
FIELDS TERMINATED BY ','
(
    record_type FILLER,
    dept_id,
    dept_name,
    location
)

2. Loading LOB Data

LOAD DATA
INFILE 'documents.csv'
INTO TABLE document_store
FIELDS TERMINATED BY ','
(
    doc_id,
    title,
    content LOBFILE(CONSTANT '/docs/') TERMINATED BY EOF,
    created_date SYSDATE
)

3. Using SQL Functions

LOAD DATA
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(
    employee_id,
    first_name "INITCAP(:first_name)",
    last_name "UPPER(:last_name)",
    email ":first_name || '.' || :last_name || '@company.com'",
    hire_date "TO_DATE(:hire_date, 'YYYY-MM-DD')",
    department_id "DECODE(:department_id, 'HR', 10, 'IT', 20, 'FIN', 30, 99)"
)

Performance Optimization

1. Direct Path Loading

# Enable direct path for better performance
sqlldr hr/password control=employees.ctl direct=true

2. Parallel Loading

#!/bin/bash
# parallel_load.sh - Split large file and load in parallel

# Split large file into chunks
split -l 100000 large_file.csv chunk_

# Load chunks in parallel
for chunk in chunk_*; do
    sqlldr hr/password control=load_template.ctl data=$chunk direct=true &
done

# Wait for all processes to complete
wait

3. Optimal Settings

LOAD DATA
INFILE 'large_file.csv'
INTO TABLE large_table
OPTIONS (
    SKIP=1,              -- Skip header row
    LOAD=1000000,        -- Load only first 1M rows
    ERRORS=1000,         -- Allow up to 1000 errors
    ROWS=10000,          -- Commit every 10K rows
    BINDSIZE=1048576,    -- 1MB bind array
    READSIZE=1048576     -- 1MB read buffer
)
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name, salary)

Monitoring and Logging

1. Log File Analysis

# Check load statistics
grep -A 5 "Total logical records" employees.log

# Check for errors
grep -i "error\|rejected\|ora-" employees.log

# Performance metrics
grep "elapsed time\|rows per second" employees.log

2. Database Monitoring

-- Check current SQL*Loader sessions
SELECT sid, serial#, username, program, status
FROM v$session
WHERE program LIKE '%sqlloader%';

-- Monitor table loading progress
SELECT table_name, num_rows
FROM user_tables
WHERE table_name = 'EMPLOYEES';

Common Control File Templates

1. Standard CSV Template

LOAD DATA
INFILE '*'                    -- Use same name as control file
APPEND INTO TABLE <table_name>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    column1,
    column2,
    column3 DATE "YYYY-MM-DD"
)

2. Tab-Delimited Template

LOAD DATA
INFILE 'data.txt'
INTO TABLE <table_name>
FIELDS TERMINATED BY X'09'   -- Tab character
TRAILING NULLCOLS
(
    column1,
    column2,
    column3
)

3. Pipe-Delimited with NULL Handling

LOAD DATA
INFILE 'data.txt'
INTO TABLE <table_name>
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
    column1 NULLIF column1 = 'NULL',
    column2 DEFAULTIF column2 = BLANKS,
    column3 "NVL(:column3, 'DEFAULT_VALUE')"
)

Best Practices

1. Pre-Loading Preparation

-- Disable constraints during load
ALTER TABLE employees DISABLE CONSTRAINT emp_pk;
ALTER TABLE employees DISABLE CONSTRAINT emp_fk;

-- Drop indexes for large loads
DROP INDEX emp_name_idx;

-- Load data with SQL*Loader

-- Re-enable constraints
ALTER TABLE employees ENABLE CONSTRAINT emp_pk;
ALTER TABLE employees ENABLE CONSTRAINT emp_fk;

-- Recreate indexes
CREATE INDEX emp_name_idx ON employees(last_name, first_name);

2. Data Validation Script

validate_and_load.sh

#!/bin/bash
DATA_FILE="employees.csv"
CONTROL_FILE="employees.ctl"
LOG_FILE="employees.log"

# Count input records
INPUT_COUNT=$(wc -l < $DATA_FILE)
echo "Input records: $INPUT_COUNT"

# Run SQL*Loader
sqlldr hr/password control=$CONTROL_FILE log=$LOG_FILE

# Check results
LOADED_COUNT=$(grep "successfully loaded" $LOG_FILE | awk '{print $1}')
ERROR_COUNT=$(grep "rejected" $LOG_FILE | awk '{print $1}')

echo "Records loaded: $LOADED_COUNT"
echo "Records rejected: $ERROR_COUNT"

# Validate counts match
if [ $((LOADED_COUNT + ERROR_COUNT)) -ne $INPUT_COUNT ]; then
    echo "WARNING: Record count mismatch!"
    exit 1
fi

Troubleshooting Common Issues

1. ORA-00001: Unique constraint violated

-- Use APPEND mode or handle duplicates
LOAD DATA
INFILE 'data.csv'
APPEND INTO TABLE employees
WHEN employee_id NOT IN (SELECT employee_id FROM employees)
FIELDS TERMINATED BY ','
(employee_id, first_name, last_name)

2. Character set issues

# Specify character set
sqlldr hr/password control=data.ctl characterset=UTF8

3. Performance problems

# Use direct path and increase buffer sizes
sqlldr hr/password control=data.ctl direct=true \
  bindsize=1048576 readsize=1048576

Integration Examples

1. Automated Daily Load

daily_data_load.sh

#!/bin/bash
DATE=$(date +%Y%m%d)
DATA_DIR="/data/incoming"
ARCHIVE_DIR="/data/archive"

for file in $DATA_DIR/*.csv; do
    if [ -f "$file" ]; then
        filename=$(basename "$file" .csv)

        # Load data
        sqlldr hr/password control=${filename}.ctl data="$file" \
               log="${filename}_${DATE}.log"

        # Archive processed file
        mv "$file" "$ARCHIVE_DIR/${filename}_${DATE}.csv"
    fi
done

2. Error Notification

load_with_notification.sh

#!/bin/bash
LOG_FILE="employees.log"
EMAIL="[email protected]"

sqlldr hr/password control=employees.ctl log=$LOG_FILE

# Check for errors
if grep -i "ora-\|error" $LOG_FILE > /dev/null; then
    mail -s "SQL*Loader Error Detected" $EMAIL < $LOG_FILE
fi

Quick Reference

Essential Commands

# Basic load
sqlldr userid=user/password control=file.ctl

# Direct path load
sqlldr userid=user/password control=file.ctl direct=true

# Parallel load with multiple sessions
sqlldr userid=user/password control=file.ctl parallel=true

# Skip header rows
sqlldr userid=user/password control=file.ctl skip=1

Common Control File Clauses

Clause	Purpose	Example
INFILE	Specify data file	INFILE 'data.csv'
BADFILE	Bad records file	BADFILE 'data.bad'
FIELDS TERMINATED BY	Field delimiter	FIELDS TERMINATED BY ','
TRAILING NULLCOLS	Handle missing columns	TRAILING NULLCOLS
WHEN	Conditional loading	WHEN (1:1) = 'A'

This foundation will help you implement effective bulk data loading with SQL*Loader in your Oracle environment.