Oracle sql loader 2001.pdf

,AUTHOR.COLO.14826 Page 1 Sunday, June 17, 2001 6:53 PM

About the Authors

Jonathan Gennick is a writer and editor. His writing career began in 1997 when he

coauthored Teach Yourself PL/SQL in 21 Days . Since then, he has written several

O’Reilly books, including Oracle SQL*Plus: The Definitive Guide , Oracle SQL*Plus

Pocket Reference , and Oracle Net8 Configuration and Troubleshooting . He has also

edited a number of books for O’Reilly and other publishers, and he recently joined

O’Reilly as an associate editor, specializing in Oracle books. Jonathan was formerly

a manager in KPMG’s Public Services Systems Integration practice, where he was

also the lead database administrator for the utilities group working out of KPMG’s

Detroit office. He has more than a decade of experience with relational databases.

Jonathan is a member of MENSA, and he holds a Bachelor of Arts degree in Infor-

mation and Computer Science from Andrews University in Berrien Springs,

Michigan. He currently resides in Munising, Michigan, with his wife Donna and

their two children: twelve-year-old Jenny, who often wishes her father wouldn’t

spend quite so much time writing, and five-year-old Jeff, who has never seen it

any other way. You can reach Jonathan by email at jonathan@gennick.com . You

can also visit Jonathan’s web site at http://gennick.com .

Sanjay Mishra is a certified Oracle database administrator with more than nine

years of IT experience. For the past six years, 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, data-

base management, backup/recovery, disaster planning, 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 (O’Reilly & Asso-

ciates) and can be reached at sanjay_mishra@i2.com .

Colophon

Our look is the result of reader comments, our own experimentation, and feedback

from distribution channels. Distinctive covers complement our distinctive approach

to technical topics, breathing personality and life into potentially dry subjects.

The animal on the cover of Oracle SQL*Loader: The Definitive Guide is a scarab

beetle. There are nearly 30,000 members of the scarab beetle family, and over

1,200 in North America alone. This large, heavy-bodied beetle is classified in the

order Coleoptera , family Scarabaeidae . Many scarab beetles are brightly colored,

and some are iridescent. In North America, the largest scarabs are the Hercules

,AUTHOR.COLO.14826 Page 2 Sunday, June 17, 2001 6:53 PM

beetle and the closely related elephant and rhinoceros beetles. The males of these

species have prominent horns.

Many scarabs are scavengers, living on decaying vegetation and animal dung. They

are consider efficient recyclers and valuable for reducing disease-breeding waste.

Some of the scavengers of the scarab family use their front legs to gather dung and

roll it into a ball. They carry the ball underground and use it as food and a place to

lay their eggs. The Mediterranean black scarab’s apparently magical ability to repro-

duce from mud and decaying organic materials led the ancient Egyptians to

associate the scarab with resurrection and immortality. The beetles were consid-

ered sacred, and representations in stone and metal were buried with mummies.

A member of the North American scarab family plays a key role in Edgar Allen

Poe’s story “The Gold-Bug.” In his search of Sullivan’s Island, South Carolina, a

scarab beetle is William Legrand’s mysterious guide to the buried treasure of

Captian Kidd.

Colleen Gorman was the production editor and the copyeditor for Oracle

SQL*Loader: The Definitive Guide . Sarah Jane Shangraw and Linley Dolby provided

quality control, and Leanne Soylemez was the proofreader. John Bickelhaupt

wrote the index.

Ellie Volckhausen designed the cover of this book, based on a series design by

Edie Freedman. The cover image is from Cuvier’s Animals . Emma Colby produced

the cover layout with QuarkXPress 4.1 using Adobe’s ITC Garamond font.

Melanie Wang designed the interior layout based on a series design by Nancy

Priest. Anne-Marie Vaduva converted the files from Microsoft Word to FrameMaker

5.5.6 using tools created by Mike Sierra. The text and heading fonts are ITC Gara-

mond Light and Garamond Book; the code font is Constant Willison. The

illustrations that appear in the book were produced by Robert Romano and

Jessamyn Read using Macromedia FreeHand 9 and Adobe Photoshop 6. This colo-

phon was written by Colleen Gorman.

Whenever possible, our books use a durable and flexible lay-flat binding. If the

page count exceeds this binding’s limit, perfect binding is used.

,sql_loaderTOC.fm.28101 Page vii Wednesday, April 11, 2001 1:48 PM

Table of Contents

Preface ..................................................................................................................... xi

1. Introduction to SQL*Loader .................................................................. 1

The SQL*Loader Environment ......................................................................... 2

A Short SQL*Loader Example .......................................................................... 4

SQL*Loader’s Capabilities .............................................................................. 11

Issues when Loading Data ............................................................................. 11

Invoking SQL*Loader ..................................................................................... 14

2. The Mysterious Control File ................................................................ 22

Syntax Rules ................................................................................................... 22

The LOAD Statement ..................................................................................... 28

Command-Line Parameters in the Control File ............................................ 43

Placing Data in the Control File .................................................................... 45

3. Fields and Datatypes ............................................................................. 47

Field Specifications ........................................................................................ 47

Datatypes ........................................................................................................ 59

4. Loading from Fixed-Width Files ........................................................ 78

Common Datatypes Encountered ................................................................. 79

Specifying Field Positions .............................................................................. 79

Handling Anomalous Data ............................................................................ 83

Concatenating Records .................................................................................. 96

Nesting Delimited Fields .............................................................................. 103

vii

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory, eMatter Edition

,sql_loaderTOC.fm.28101 Page viii Wednesday, April 11, 2001 1:48 PM

viii

Table of Contents

5. Loading Delimited Data .................................................................... 107

Common Datatypes Encountered ............................................................... 107

Example Data ............................................................................................... 108

Using Delimiters to Identify Fields .............................................................. 108

Common Issues with Delimited Data ......................................................... 118

Concatenating Records ................................................................................ 124

Handling Nested Fields ................................................................................ 127

6. Recovering from Failure .................................................................... 130

Deleting and Starting Over .......................................................................... 131

Restarting a Conventional Path Load .......................................................... 132

Restarting a Direct Path Load ...................................................................... 136

7. Validating and Selectively Loading Data .................................... 141

Handling Rejected Records .......................................................................... 141

Selectively Loading Data .............................................................................. 146

8. Transforming Data During a Load ............................................... 152

Using Oracle’s Built-in SQL Functions ........................................................ 152

Writing Your Own Functions ...................................................................... 156

Passing Data Through Work Tables ........................................................... 158

Using Triggers .............................................................................................. 159

Performing Character Set Conversion ......................................................... 161

9. Transaction Size and Performance Issues .................................. 167

Transaction Processing in SQL*Loader ....................................................... 167

Commit Frequency and Load Performance ................................................ 168

Commit Frequency and Rollback Segments ............................................... 175

Performance Improvement Guidelines ....................................................... 179

10. Direct Path Loads .................................................................................. 182

What is the Direct Path? ............................................................................... 182

Performing Direct Path Loads ..................................................................... 184

Data Saves .................................................................................................... 196

Loading Data Fields Greater than 64K ........................................................ 197

UNRECOVERABLE Loads ............................................................................ 198

Parallel Data Loading ................................................................................... 199

11. Loading Large Objects ........................................................................ 205

About Large Objects .................................................................................... 205

Considerations when Loading LOBs ........................................................... 208

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory, eMatter Edition

,sql_loaderTOC.fm.28101 Page ix Wednesday, April 11, 2001 1:48 PM

Table of Contents

Loading Inline LOBs .................................................................................... 210

Loading LOBs from External Data Files ...................................................... 212

Loading BFILEs ............................................................................................. 217

12. Loading Objects and Collections .................................................... 221

Loading Object Tables and Columns .......................................................... 221

Loading Collections ...................................................................................... 225

Using NULLIF and DEFAULTIF with an Object or a Collection ................ 240

Index .................................................................................................................... 243

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory, eMatter Edition

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: