book

Oracle SQL*Loader: The Definitive Guide

by Jonathan Gennick, Sanjay Mishra

April 2001

Intermediate to advanced

272 pages

6h 59m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Copyright
Dedication
A Note Regarding Supplemental Files
Preface
Why We Wrote This BookAudience for This BookPlatform and VersionStructure of This BookConventions Used in This BookComments and Questions AcknowledgmentsFrom JonathanFrom Sanjay
1. Introduction to SQL*Loader
1.1. The SQL*Loader Environment1.1.1. The SQL*Loader Control File1.1.2. The Log File1.1.3. The Bad File and the Discard File1.2. A Short SQL*Loader Example1.2.1. The Data1.2.2. The Control File1.2.3. The Command Line1.2.4. The Log File1.3. SQL*Loader’s Capabilities1.4. Issues when Loading Data1.4.1. Recovery from Failure1.4.2. Transaction Size1.4.3. Data Validation1.4.4. Data Transformation1.5. Invoking SQL*Loader1.5.1. Command-Line Parameters1.5.2. Command-Line Syntax Rules1.5.3. Parameter Precedence
2. The Mysterious Control File
2.1. Syntax Rules2.1.1. Free Format2.1.2. Case Sensitivity2.1.2.1. Table and column names2.1.2.2. Filenames2.1.3. Comments2.1.4. Special Characters2.1.4.1. Table and column names2.1.4.2. Filenames2.1.4.3. The escape character2.1.5. Reserved Words2.2. The LOAD Statement2.2.1. LOAD DATA Syntax2.2.2. Specifying Input Files2.2.2.1. Loading from one input file2.2.2.2. Loading from multiple input files2.2.2.3. Specifying the table loading method2.2.3. Specifying the Target Tables2.2.3.1. Loading one table2.2.3.2. Loading delimited data into multiple tables2.2.3.3. Loading fixed-width data into multiple tables2.2.3.4. Loading a table partition2.3. Command-Line Parameters in the Control File2.4. Placing Data in the Control File
3. Fields and Datatypes
3.1. Field Specifications3.1.1. Scalar Fields3.1.2. Understanding Field Positions3.1.2.1. Specifying the starting position of a field3.1.2.2. Specifying the ending position of a field3.1.2.3. Advantages of relative positioning3.1.3. Filler Fields3.1.3.1. Using filler fields to skip data3.1.3.2. Other uses for filler fields3.1.4. Generated Fields3.2. Datatypes3.2.1. Portable Datatypes3.2.1.1. CHAR3.2.1.2. DATE3.2.1.3. INTEGER, FLOAT, DECIMAL, and ZONED EXTERNAL3.2.1.4. GRAPHIC3.2.1.5. GRAPHIC EXTERNAL3.2.1.6. RAW3.2.1.7. VARCHARC3.2.1.8. VARRAWC3.2.2. Nonportable Datatypes3.2.2.1. INTEGER, SMALLINT, FLOAT, DOUBLE, and BYTEINT3.2.2.2. ZONED3.2.2.3. DECIMAL3.2.2.4. VARGRAPHIC3.2.2.5. VARCHAR3.2.2.6. VARRAW3.2.2.7. LONG VARRAW
4. Loading from Fixed-Width Files
4.1. Common Datatypes Encountered4.2. Specifying Field Positions4.2.1. Starting and Ending Position4.2.2. Starting Position and Length4.2.3. Field Type and Length4.3. Handling Anomalous Data4.3.1. Trimming Whitespace4.3.1.1. The PRESERVE BLANKS clause4.3.1.2. Selective trimming of whitespace4.3.2. Dealing with Nulls4.3.2.1. SQL*Loader’s default behavior4.3.2.2. The effect of PRESERVE BLANKS4.3.2.3. NULLIF4.3.2.4. DEFAULTIF4.3.3. Dealing with “Short” Records4.3.4. Database Column Defaults4.4. Concatenating Records4.4.1. CONCATENATE4.4.1.1. CONCATENATE syntax4.4.1.2. CONCATENATE example4.4.2. CONTINUEIF4.4.2.1. CONTINUEIF syntax4.4.2.2. CONTINUEIF THIS4.4.2.3. CONTINUEIF NEXT4.4.2.4. Using not-equals4.5. Nesting Delimited Fields4.5.1. Extracting Subfields4.5.1.1. Separating the latitude and longitude4.5.1.2. Separating the individual latitude and longitude components4.5.2. Variable-Length Records
5. Loading Delimited Data
5.1. Common Datatypes Encountered5.2. Example Data5.3. Using Delimiters to Identify Fields5.3.1. Specifying Termination Characters5.3.1.1. Syntax for TERMINATED BY5.3.1.2. Example: Field-specific delimiters5.3.1.3. Example: One delimiter for all fields5.3.1.4. Example: Multi-character delimiters5.3.2. Specifying Enclosing Characters5.3.2.1. Syntax for ENCLOSED BY5.3.2.2. Example: Loading CSV files5.3.2.3. Example: Whitespace as a delimiter5.3.2.4. Example: Enclosing characters with no delimiters5.3.2.5. Example: Different beginning and ending enclosing characters5.4. Common Issues with Delimited Data5.4.1. Dealing with Nulls5.4.1.1. Enclosures must be optional5.4.1.2. The presence of enclosing characters affects the treatment of spaces5.4.1.3. Errors result if the final field is missing5.4.2. Dealing with Short Records5.4.2.1. TRAILING NULLCOLS syntax5.4.2.2. Example: TRAILING NULLCOLS5.4.3. Skipping Fields You Don’t Want to Load5.4.3.1. Using the FILLER clause5.4.3.2. Skipping a fixed portion of the record5.4.3.3. Skipping columns before Oracle8i5.5. Concatenating Records5.5.1. Syntax for CONTINUEIF LAST5.5.2. CONTINUEIF LAST Examples5.5.2.1. Example: A trailing comma as a continuation character5.5.2.2. Example: Marking the end of each logical record5.6. Handling Nested Fields5.6.1. Nested Delimited Fields5.6.2. Nested Fixed-Width Fields
6. Recovering from Failure
6.1. Deleting and Starting Over6.1.1. When You’re Loading an Empty Table6.1.2. When You’re Appending to a Table with Existing Data6.2. Restarting a Conventional Path Load6.2.1. Determining the Number of Records to Skip6.2.2. Using the SKIP Command-Line Parameter6.2.3. Continuing a Continued Load6.3. Restarting a Direct Path Load6.3.1. Determining the Number of Records to Skip6.3.2. Using the SKIP Command-Line Parameter6.3.3. Using SKIP in the Control File6.3.4. Index-Related Issues

7. Validating and Selectively Loading Data
7.1. Handling Rejected Records7.1.1. Naming the Bad File7.1.2. Bad Records in the Log File7.1.3. Fixing Bad Records7.1.4. Limiting the Number of Errors7.2. Selectively Loading Data7.2.1. Writing Field Conditions7.2.2. Naming the Discard File7.2.3. Discarded Records in the Log File7.2.4. Limiting the Number of Discards7.2.5. Loading Multiple Tables
8. Transforming Data During a Load
8.1. Using Oracle’s Built-in SQL Functions8.1.1. Syntax for SQL Expressions8.1.2. Example: Loading Book Price Data8.2. Writing Your Own Functions8.3. Passing Data Through Work Tables8.4. Using Triggers8.5. Performing Character Set Conversion8.5.1. SQL*Loader and Character Sets8.5.1.1. Data in the SQL*Loader control file8.5.1.2. Datatypes affected8.5.1.3. Characters that can’t be converted8.5.2. Syntax for Specifying the Character Set8.5.3. Character Set Example
9. Transaction Size and Performance Issues
9.1. Transaction Processing in SQL*Loader9.2. Commit Frequency and Load Performance9.2.1. READSIZE9.2.2. BINDSIZE9.2.3. ROWS9.2.4. Large Versus Small Bind Arrays9.2.5. Setting READSIZE, BINDSIZE, and ROWS9.3. Commit Frequency and Rollback Segments9.3.1. Rollback Segment Errors9.3.2. Estimating Rollback Segment Requirements9.3.2.1. Step 1: Determine the size of a single row9.3.2.2. Step 2: Determine the size of the bind array9.3.2.3. Step 3: Estimate the size of the rollback segment required9.3.3. Using the Right Rollback Segments9.4. Performance Improvement Guidelines
10. Direct Path Loads
10.1. What is the Direct Path?10.2. Performing Direct Path Loads10.2.1. Preparing to Use Direct Path Loads10.2.2. Invoking a Direct Path Load10.2.3. Restrictions on Direct Path Loads10.2.4. Storage Issues with Parallel Direct Path Load10.2.5. Index Maintenance with Direct Path Loads10.2.5.1. Storage requirements for index maintenance10.2.5.2. Unusable index state10.2.5.3. SINGLEROW index option10.2.5.4. Presorting your input data10.2.6. Direct Path Loads and Integrity Constraints10.2.7. Direct Path Loads and Database Triggers10.3. Data Saves10.4. Loading Data Fields Greater than 64K10.5. UNRECOVERABLE Loads10.6. Parallel Data Loading10.6.1. Preparing for Parallel Data Loading10.6.2. Concurrent Conventional Path Loads10.6.3. Concurrent Direct Path Loads10.6.3.1. Loading multiple segments concurrently10.6.3.2. Loading one segment concurrently10.6.3.3. How a parallel direct path load works10.6.3.4. Restrictions on parallel direct path loads10.6.3.5. Storage management with parallel direct path loads
11. Loading Large Objects
11.1. About Large Objects 11.1.1. What Are Large Objects?11.1.2. This Chapter’s Examples11.1.3. Understanding Your Options11.1.3.1. One object, one file11.1.3.2. Many LOBS in one file11.1.3.3. LOBS in your main data file11.2. Considerations when Loading LOBs11.2.1. Record and Field Sizes11.2.2. DEFAULTIF and NULLIF 11.3. Loading Inline LOBs11.3.1. Treating a LOB Like Scalar Data11.3.2. Changing the Stream Delimiter11.4. Loading LOBs from External Data Files11.4.1. LOBFILE Clause Syntax11.4.2. Loading Entire Files11.4.3. Placing Multiple LOBs in One File11.5. Loading BFILEs11.5.1. Directory Objects11.5.2. BFILE Clause Syntax11.5.3. BFILE Field Specifications
12. Loading Objects and Collections
12.1. Loading Object Tables and Columns12.1.1. Loading Object Tables12.1.2. Loading Object Columns12.1.2.1. Creating an object column12.1.2.2. The COLUMN OBJECT clause12.2. Loading Collections12.2.1. Memory Implications12.2.2. Loading Collections from Inline Data12.2.2.1. Specifying an inline collection12.2.2.2. Example: Loading inline, delimited data12.2.2.3. Example: Specifying an occurrence count12.2.3. Loading Collections from Secondary Data Files12.2.3.1. Specifying a secondary data file12.2.3.2. Example: Loading a collection from a secondary data file12.3. Using NULLIF and DEFAULTIF with an Object or a Collection12.3.1. Applying NULLIF or DEFAULTIF to an Object or a Collection as a Whole12.3.2. Applying NULLIF or DEFAULTIF to a Field Within an Object or a Collection
About the Authors
Colophon
Copyright

Overview

SQLLoader is a ubiquitous tool in the Oracle world. It has been shipped with Oracle since at least Version 6 and continues to be supported and enhanced with each new version of Oracle, including Oracle8 and Oracle8i. The job of SQLLoader is to load data from flat files into an Oracle database. It's optimized for loading large volumes of data, and is flexible enough to handle virtually any input format.Almost every Oracle user has to use SQLLoader at one time or another, and DBAs are frequently called upon to load data for the users in their organization. Despite SQLLoader's wide availability and usage, few DBAs and developers know how to get the most out of it. Oracle SQLLoader: The Definitive Guide has everything you need to know to put SQLLoader to its best use: an introduction to SQLLoader, a reference to all of its syntax options, and most importantly, step-by-step instructions for all the SQLLoader tasks you'd want to perform--and maybe some you didn't realize you COULD perform.You'll learn how to construct the necessary control files and load from different types of files containing different types of data (e.g., fixed-width data, delimited data, and data of various datatypes). You'll also learn how to validate data, load it selectively, transform it as it is loaded, and recover after failure. This book explains how to optimize SQLLoader performance by adjusting the transaction size and using the new direct path option. It also covers the newest SQLLoader features--the loading of large object (LOB) columns and the new object types (nested tables, varying arrays, and object tables).Oracle SQLLoader: The Definitive Guide is an indispensable resource for anyone who is new to SQLLoader; a task-oriented learning tool for those who are already using it; and a quick reference for every user. If you want to take best advantage of an essential Oracle tool, you need this book.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 1565929489Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills