Oracle SQL*Loader: The Definitive Guide

Oracle SQL*Loader: The Definitive Guide

by Jonathan Gennick, Sanjay Mishra
Released April 2001
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565929487

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

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.

Table of contents

  1. Copyright
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. Preface
    1. Why We Wrote This Book
    2. Audience for This Book
    3. Platform and Version
    4. Structure of This Book
    5. Conventions Used in This Book
    6. Comments and Questions
    7. Acknowledgments
      1. From Jonathan
      2. From Sanjay
  5. 1. Introduction to SQL*Loader
    1. 1.1. The SQL*Loader Environment
      1. 1.1.1. The SQL*Loader Control File
      2. 1.1.2. The Log File
      3. 1.1.3. The Bad File and the Discard File
    2. 1.2. A Short SQL*Loader Example
      1. 1.2.1. The Data
      2. 1.2.2. The Control File
      3. 1.2.3. The Command Line
      4. 1.2.4. The Log File
    3. 1.3. SQL*Loader’s Capabilities
    4. 1.4. Issues when Loading Data
      1. 1.4.1. Recovery from Failure
      2. 1.4.2. Transaction Size
      3. 1.4.3. Data Validation
      4. 1.4.4. Data Transformation
    5. 1.5. Invoking SQL*Loader
      1. 1.5.1. Command-Line Parameters
      2. 1.5.2. Command-Line Syntax Rules
      3. 1.5.3. Parameter Precedence
  6. 2. The Mysterious Control File
    1. 2.1. Syntax Rules
      1. 2.1.1. Free Format
      2. 2.1.2. Case Sensitivity
        1. 2.1.2.1. Table and column names
        2. 2.1.2.2. Filenames
      3. 2.1.3. Comments
      4. 2.1.4. Special Characters
        1. 2.1.4.1. Table and column names
        2. 2.1.4.2. Filenames
        3. 2.1.4.3. The escape character
      5. 2.1.5. Reserved Words
    2. 2.2. The LOAD Statement
      1. 2.2.1. LOAD DATA Syntax
      2. 2.2.2. Specifying Input Files
        1. 2.2.2.1. Loading from one input file
        2. 2.2.2.2. Loading from multiple input files
        3. 2.2.2.3. Specifying the table loading method
      3. 2.2.3. Specifying the Target Tables
        1. 2.2.3.1. Loading one table
        2. 2.2.3.2. Loading delimited data into multiple tables
        3. 2.2.3.3. Loading fixed-width data into multiple tables
        4. 2.2.3.4. Loading a table partition
    3. 2.3. Command-Line Parameters in the Control File
    4. 2.4. Placing Data in the Control File
  7. 3. Fields and Datatypes
    1. 3.1. Field Specifications
      1. 3.1.1. Scalar Fields
      2. 3.1.2. Understanding Field Positions
        1. 3.1.2.1. Specifying the starting position of a field
        2. 3.1.2.2. Specifying the ending position of a field
        3. 3.1.2.3. Advantages of relative positioning
      3. 3.1.3. Filler Fields
        1. 3.1.3.1. Using filler fields to skip data
        2. 3.1.3.2. Other uses for filler fields
      4. 3.1.4. Generated Fields
    2. 3.2. Datatypes
      1. 3.2.1. Portable Datatypes
        1. 3.2.1.1. CHAR
        2. 3.2.1.2. DATE
        3. 3.2.1.3. INTEGER, FLOAT, DECIMAL, and ZONED EXTERNAL
        4. 3.2.1.4. GRAPHIC
        5. 3.2.1.5. GRAPHIC EXTERNAL
        6. 3.2.1.6. RAW
        7. 3.2.1.7. VARCHARC
        8. 3.2.1.8. VARRAWC
      2. 3.2.2. Nonportable Datatypes
        1. 3.2.2.1. INTEGER, SMALLINT, FLOAT, DOUBLE, and BYTEINT
        2. 3.2.2.2. ZONED
        3. 3.2.2.3. DECIMAL
        4. 3.2.2.4. VARGRAPHIC
        5. 3.2.2.5. VARCHAR
        6. 3.2.2.6. VARRAW
        7. 3.2.2.7. LONG VARRAW
  8. 4. Loading from Fixed-Width Files
    1. 4.1. Common Datatypes Encountered
    2. 4.2. Specifying Field Positions
      1. 4.2.1. Starting and Ending Position
      2. 4.2.2. Starting Position and Length
      3. 4.2.3. Field Type and Length
    3. 4.3. Handling Anomalous Data
      1. 4.3.1. Trimming Whitespace
        1. 4.3.1.1. The PRESERVE BLANKS clause
        2. 4.3.1.2. Selective trimming of whitespace
      2. 4.3.2. Dealing with Nulls
        1. 4.3.2.1. SQL*Loader’s default behavior
        2. 4.3.2.2. The effect of PRESERVE BLANKS
        3. 4.3.2.3. NULLIF
        4. 4.3.2.4. DEFAULTIF
      3. 4.3.3. Dealing with “Short” Records
      4. 4.3.4. Database Column Defaults
    4. 4.4. Concatenating Records
      1. 4.4.1. CONCATENATE
        1. 4.4.1.1. CONCATENATE syntax
        2. 4.4.1.2. CONCATENATE example
      2. 4.4.2. CONTINUEIF
        1. 4.4.2.1. CONTINUEIF syntax
        2. 4.4.2.2. CONTINUEIF THIS
        3. 4.4.2.3. CONTINUEIF NEXT
        4. 4.4.2.4. Using not-equals
    5. 4.5. Nesting Delimited Fields
      1. 4.5.1. Extracting Subfields
        1. 4.5.1.1. Separating the latitude and longitude
        2. 4.5.1.2. Separating the individual latitude and longitude components
      2. 4.5.2. Variable-Length Records
  9. 5. Loading Delimited Data
    1. 5.1. Common Datatypes Encountered
    2. 5.2. Example Data
    3. 5.3. Using Delimiters to Identify Fields
      1. 5.3.1. Specifying Termination Characters
        1. 5.3.1.1. Syntax for TERMINATED BY
        2. 5.3.1.2. Example: Field-specific delimiters
        3. 5.3.1.3. Example: One delimiter for all fields
        4. 5.3.1.4. Example: Multi-character delimiters
      2. 5.3.2. Specifying Enclosing Characters
        1. 5.3.2.1. Syntax for ENCLOSED BY
        2. 5.3.2.2. Example: Loading CSV files
        3. 5.3.2.3. Example: Whitespace as a delimiter
        4. 5.3.2.4. Example: Enclosing characters with no delimiters
        5. 5.3.2.5. Example: Different beginning and ending enclosing characters
    4. 5.4. Common Issues with Delimited Data
      1. 5.4.1. Dealing with Nulls
        1. 5.4.1.1. Enclosures must be optional
        2. 5.4.1.2. The presence of enclosing characters affects the treatment of spaces
        3. 5.4.1.3. Errors result if the final field is missing
      2. 5.4.2. Dealing with Short Records
        1. 5.4.2.1. TRAILING NULLCOLS syntax
        2. 5.4.2.2. Example: TRAILING NULLCOLS
      3. 5.4.3. Skipping Fields You Don’t Want to Load
        1. 5.4.3.1. Using the FILLER clause
        2. 5.4.3.2. Skipping a fixed portion of the record
        3. 5.4.3.3. Skipping columns before Oracle8i
    5. 5.5. Concatenating Records
      1. 5.5.1. Syntax for CONTINUEIF LAST
      2. 5.5.2. CONTINUEIF LAST Examples
        1. 5.5.2.1. Example: A trailing comma as a continuation character
        2. 5.5.2.2. Example: Marking the end of each logical record
    6. 5.6. Handling Nested Fields
      1. 5.6.1. Nested Delimited Fields
      2. 5.6.2. Nested Fixed-Width Fields
  10. 6. Recovering from Failure
    1. 6.1. Deleting and Starting Over
      1. 6.1.1. When You’re Loading an Empty Table
      2. 6.1.2. When You’re Appending to a Table with Existing Data
    2. 6.2. Restarting a Conventional Path Load
      1. 6.2.1. Determining the Number of Records to Skip
      2. 6.2.2. Using the SKIP Command-Line Parameter
      3. 6.2.3. Continuing a Continued Load
    3. 6.3. Restarting a Direct Path Load
      1. 6.3.1. Determining the Number of Records to Skip
      2. 6.3.2. Using the SKIP Command-Line Parameter
      3. 6.3.3. Using SKIP in the Control File
      4. 6.3.4. Index-Related Issues
  11. 7. Validating and Selectively Loading Data
    1. 7.1. Handling Rejected Records
      1. 7.1.1. Naming the Bad File
      2. 7.1.2. Bad Records in the Log File
      3. 7.1.3. Fixing Bad Records
      4. 7.1.4. Limiting the Number of Errors
    2. 7.2. Selectively Loading Data
      1. 7.2.1. Writing Field Conditions
      2. 7.2.2. Naming the Discard File
      3. 7.2.3. Discarded Records in the Log File
      4. 7.2.4. Limiting the Number of Discards
      5. 7.2.5. Loading Multiple Tables
  12. 8. Transforming Data During a Load
    1. 8.1. Using Oracle’s Built-in SQL Functions
      1. 8.1.1. Syntax for SQL Expressions
      2. 8.1.2. Example: Loading Book Price Data
    2. 8.2. Writing Your Own Functions
    3. 8.3. Passing Data Through Work Tables
    4. 8.4. Using Triggers
    5. 8.5. Performing Character Set Conversion
      1. 8.5.1. SQL*Loader and Character Sets
        1. 8.5.1.1. Data in the SQL*Loader control file
        2. 8.5.1.2. Datatypes affected
        3. 8.5.1.3. Characters that can’t be converted
      2. 8.5.2. Syntax for Specifying the Character Set
      3. 8.5.3. Character Set Example
  13. 9. Transaction Size and Performance Issues
    1. 9.1. Transaction Processing in SQL*Loader
    2. 9.2. Commit Frequency and Load Performance
      1. 9.2.1. READSIZE
      2. 9.2.2. BINDSIZE
      3. 9.2.3. ROWS
      4. 9.2.4. Large Versus Small Bind Arrays
      5. 9.2.5. Setting READSIZE, BINDSIZE, and ROWS
    3. 9.3. Commit Frequency and Rollback Segments
      1. 9.3.1. Rollback Segment Errors
      2. 9.3.2. Estimating Rollback Segment Requirements
        1. 9.3.2.1. Step 1: Determine the size of a single row
        2. 9.3.2.2. Step 2: Determine the size of the bind array
        3. 9.3.2.3. Step 3: Estimate the size of the rollback segment required
      3. 9.3.3. Using the Right Rollback Segments
    4. 9.4. Performance Improvement Guidelines
  14. 10. Direct Path Loads
    1. 10.1. What is the Direct Path?
    2. 10.2. Performing Direct Path Loads
      1. 10.2.1. Preparing to Use Direct Path Loads
      2. 10.2.2. Invoking a Direct Path Load
      3. 10.2.3. Restrictions on Direct Path Loads
      4. 10.2.4. Storage Issues with Parallel Direct Path Load
      5. 10.2.5. Index Maintenance with Direct Path Loads
        1. 10.2.5.1. Storage requirements for index maintenance
        2. 10.2.5.2. Unusable index state
        3. 10.2.5.3. SINGLEROW index option
        4. 10.2.5.4. Presorting your input data
      6. 10.2.6. Direct Path Loads and Integrity Constraints
      7. 10.2.7. Direct Path Loads and Database Triggers
    3. 10.3. Data Saves
    4. 10.4. Loading Data Fields Greater than 64K
    5. 10.5. UNRECOVERABLE Loads
    6. 10.6. Parallel Data Loading
      1. 10.6.1. Preparing for Parallel Data Loading
      2. 10.6.2. Concurrent Conventional Path Loads
      3. 10.6.3. Concurrent Direct Path Loads
        1. 10.6.3.1. Loading multiple segments concurrently
        2. 10.6.3.2. Loading one segment concurrently
        3. 10.6.3.3. How a parallel direct path load works
        4. 10.6.3.4. Restrictions on parallel direct path loads
        5. 10.6.3.5. Storage management with parallel direct path loads
  15. 11. Loading Large Objects
    1. 11.1. About Large Objects
      1. 11.1.1. What Are Large Objects?
      2. 11.1.2. This Chapter’s Examples
      3. 11.1.3. Understanding Your Options
        1. 11.1.3.1. One object, one file
        2. 11.1.3.2. Many LOBS in one file
        3. 11.1.3.3. LOBS in your main data file
    2. 11.2. Considerations when Loading LOBs
      1. 11.2.1. Record and Field Sizes
      2. 11.2.2. DEFAULTIF and NULLIF
    3. 11.3. Loading Inline LOBs
      1. 11.3.1. Treating a LOB Like Scalar Data
      2. 11.3.2. Changing the Stream Delimiter
    4. 11.4. Loading LOBs from External Data Files
      1. 11.4.1. LOBFILE Clause Syntax
      2. 11.4.2. Loading Entire Files
      3. 11.4.3. Placing Multiple LOBs in One File
    5. 11.5. Loading BFILEs
      1. 11.5.1. Directory Objects
      2. 11.5.2. BFILE Clause Syntax
      3. 11.5.3. BFILE Field Specifications
  16. 12. Loading Objects and Collections
    1. 12.1. Loading Object Tables and Columns
      1. 12.1.1. Loading Object Tables
      2. 12.1.2. Loading Object Columns
        1. 12.1.2.1. Creating an object column
        2. 12.1.2.2. The COLUMN OBJECT clause
    2. 12.2. Loading Collections
      1. 12.2.1. Memory Implications
      2. 12.2.2. Loading Collections from Inline Data
        1. 12.2.2.1. Specifying an inline collection
        2. 12.2.2.2. Example: Loading inline, delimited data
        3. 12.2.2.3. Example: Specifying an occurrence count
      3. 12.2.3. Loading Collections from Secondary Data Files
        1. 12.2.3.1. Specifying a secondary data file
        2. 12.2.3.2. Example: Loading a collection from a secondary data file
    3. 12.3. Using NULLIF and DEFAULTIF with an Object or a Collection
      1. 12.3.1. Applying NULLIF or DEFAULTIF to an Object or a Collection as a Whole
      2. 12.3.2. Applying NULLIF or DEFAULTIF to a Field Within an Object or a Collection
  17. About the Authors
  18. Colophon
  19. Copyright

Product information

  • Title: Oracle SQL*Loader: The Definitive Guide
  • Author(s): Jonathan Gennick, Sanjay Mishra
  • Release date: April 2001
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565929487