Oracle Sql*Loader

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 Guidehas 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 Guideis 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.

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at jonathan@gennick.com, and you can learn more about him personally by visiting his website at http://gennick.com.

Sanjay Mishra is a certified Oracle database administrator with more than ten years of IT experience. He has been involved in the design, architecture, and implementation of many mission-critical and decision support databases. He has worked extensively in the areas of database architecture, database management, backup / recovery, performance tuning, Oracle Parallel Server, and parallel execution. He has a Bachelor of Science degree in Electrical Engineering, and a Master of Engineering degree in Systems Science and Automation. He is the coauthor of Oracle Parallel Processing and Oracle SQL Loader: The Definitive Guide (both published by O'Reilly & Associates). Presently, he works as a database architect at Dallas Based i2 Technologies, and can be reached at sanjay_mishra@i2.com.

Preface

xi

Introduction to SQL*Loader

1

(21)

The SQL*Loader Environment

2

(2)

A Short SQL*Loader Example

4

(7)

SQL*Loader's Capabilities

11

(1)

Issues when Loading Data

11

(3)

Invoking SQL*Loader

14

(8)

The Mysterious Control File

22

(25)

Syntax Rules

22

(6)

The LOAD Statement

28

(15)

Command-Line Parameters in the Control File

43

(2)

Placing Data in the Control File

45

(2)

Fields and Datatypes

47

(31)

Field Specifications

47

(12)

Datatypes

59

(19)

Loading from Fixed-Width Files

78

(29)

Common Datatypes Encountered

79

(1)

Specifying Field Positions

79

(4)

Handling Anomalous Data

83

(13)

Concatenating Records

96

(7)

Nesting Delimited Fields

103

(4)

Loading Delimited Data

107

(23)

Common Datatypes Encountered

107

(1)

Example Data

108

(1)

Using Delimiters to Identify Fields

108

(10)

Common Issues with Delimited Data

118

(6)

Concatenating Records

124

(3)

Handling Nested Fields

127

(3)

Recovering from Failure

130

(11)

Deleting and Starting Over

131

(1)

Restarting a Conventional Path Load

132

(4)

Restarting a Direct Path Load

136

(5)

Validating and Selectively Loading Data

141

(11)

Handling Rejected Records

141

(5)

Selectively Loading Data

146

(6)

Transforming Data During a Load

152

(15)

Using Oracle's Built-in SQL Functions

152

(4)

Writing Your Own Functions

156

(2)

Passing Data Through Work Tables

158

(1)

Using Triggers

159

(2)

Performing Character Set Conversion

161

(6)

Transaction Size and Performance Issues

167

(15)

Transaction Processing in SQL*Loader

167

(1)

Commit Frequency and Load Performance

168

(7)

Commit Frequency and Rollback Segments

175

(4)

Performance Improvement Guidelines

179

(3)

Direct Path Loads

182

(23)

What is the Direct Path?

182

(2)

Performing Direct Path Loads

184

(12)

Data Saves

196

(1)

Loading Data Fields Greater than 64K

197

(1)

Unrecoverable Loads

198

(1)

Parallel Data Loading

199

(6)

Loading Large Objects

205

(16)

About Large Objects

205

(3)

Considerations when Loading LOBs

208

(2)

Loading Inline LOBs

210

(2)

Loading LOBs from External Data Files

212

(5)

Loading BFILEs

217

(4)

Loading Objects and Collections

221

(22)

Loading Object Tables and Columns

221

(4)

Loading Collections

225

(15)

Using Nullif and Defaultif with an Object or a Collection

240

(3)

Index

243

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Summary

Author Biography

Table of Contents

Supplemental Materials