
Oracle SQL*Loader: The Definitive Guide
Description
Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.
More details
Other editions
Additional editions

Content
- Intro
- Table of Contents
- Preface
- Why We Wrote This Book
- Audience for This Book
- Platform and Version
- Structure of This Book
- Conventions Used in This Book
- Comments and Questions
- Acknowledgments
- From Jonathan
- From Sanjay
- Introduction to SQL*Loader
- The SQL*Loader Environment
- The SQL*Loader Control File
- The Log File
- The Bad File and the Discard File
- A Short SQL*Loader Example
- The Data
- The Control File
- The Command Line
- The Log File
- SQL*Loader's Capabilities
- Issues when Loading Data
- Recovery from Failure
- Transaction Size
- Data Validation
- Data Transformation
- Invoking SQL*Loader
- Command-Line Parameters
- Command-Line Syntax Rules
- Parameter Precedence
- The Mysterious Control File
- Syntax Rules
- Free Format
- Case Sensitivity
- Table and column names
- Filenames
- Comments
- Special Characters
- Table and column names
- Filenames
- The escape character
- Reserved Words
- The LOAD Statement
- LOAD DATA Syntax
- Specifying Input Files
- Loading from one input file
- Loading from multiple input files
- Specifying the table loading method
- Specifying the Target Tables
- Loading one table
- Loading delimited data into multiple tables
- Loading fixed-width data into multiple tables
- Loading a table partition
- Command-Line Parameters in the Control File
- Placing Data in the Control File
- Fields and Datatypes
- Field Specifications
- Scalar Fields
- Understanding Field Positions
- Specifying the starting position of a field
- Specifying the ending position of a field
- Advantages of relative positioning
- Filler Fields
- Using filler fields to skip data
- Other uses for filler fields
- Generated Fields
- Datatypes
- Portable Datatypes
- CHAR
- DATE
- INTEGER, FLOAT, DECIMAL, and ZONED EXTERNAL
- GRAPHIC
- GRAPHIC EXTERNAL
- RAW
- VARCHARC
- VARRAWC
- Nonportable Datatypes
- INTEGER, SMALLINT, FLOAT, DOUBLE, and BYTEINT
- ZONED
- DECIMAL
- VARGRAPHIC
- VARCHAR
- VARRAW
- LONG VARRAW
- Loading from Fixed-Width Files
- Common Datatypes Encountered
- Specifying Field Positions
- Starting and Ending Position
- Starting Position and Length
- Field Type and Length
- Handling Anomalous Data
- Trimming Whitespace
- The PRESERVE BLANKS clause
- Selective trimming of whitespace
- Dealing with Nulls
- SQL*Loader's default behavior
- The effect of PRESERVE BLANKS
- NULLIF
- DEFAULTIF
- Dealing with "Short" Records
- Database Column Defaults
- Concatenating Records
- CONCATENATE
- CONCATENATE syntax
- CONCATENATE example
- CONTINUEIF
- CONTINUEIF syntax
- CONTINUEIF THIS
- CONTINUEIF NEXT
- Using not-equals
- Nesting Delimited Fields
- Extracting Subfields
- Separating the latitude and longitude
- Separating the individual latitude and longitude components
- Variable-Length Records
- Loading Delimited Data
- Common Datatypes Encountered
- Example Data
- Using Delimiters to Identify Fields
- Specifying Termination Characters
- Syntax for TERMINATED BY
- Example: Field-specific delimiters
- Example: One delimiter for all fields
- Example: Multi-character delimiters
- Specifying Enclosing Characters
- Syntax for ENCLOSED BY
- Example: Loading CSV files
- Example: Whitespace as a delimiter
- Example: Enclosing characters with no delimiters
- Example: Different beginning and ending enclosing characters
- Common Issues with Delimited Data
- Dealing with Nulls
- Enclosures must be optional
- The presence of enclosing characters affects the treatment of spaces
- Errors result if the final field is missing
- Dealing with Short Records
- TRAILING NULLCOLS syntax
- Example: TRAILING NULLCOLS
- Skipping Fields You Don't Want to Load
- Using the FILLER clause
- Skipping a fixed portion of the record
- Skipping columns before Oracle8i
- Concatenating Records
- Syntax for CONTINUEIF LAST
- CONTINUEIF LAST Examples
- Example: A trailing comma as a continuation character
- Example: Marking the end of each logical record
- Handling Nested Fields
- Nested Delimited Fields
- Nested Fixed-Width Fields
- Recovering from Failure
- Deleting and Starting Over
- When You're Loading an Empty Table
- When You're Appending to a Table with Existing Data
- Restarting a Conventional Path Load
- Determining the Number of Records to Skip
- Using the SKIP Command-Line Parameter
- Continuing a Continued Load
- Restarting a Direct Path Load
- Determining the Number of Records to Skip
- Using the SKIP Command-Line Parameter
- Using SKIP in the Control File
- Index-Related Issues
- Validating and Selectively Loading Data
- Handling Rejected Records
- Naming the Bad File
- Bad Records in the Log File
- Fixing Bad Records
- Limiting the Number of Errors
- Selectively Loading Data
- Writing Field Conditions
- Naming the Discard File
- Discarded Records in the Log File
- Limiting the Number of Discards
- Loading Multiple Tables
- Transforming Data During a Load
- Using Oracle's Built-in SQL Functions
- Syntax for SQL Expressions
- Example: Loading Book Price Data
- Writing Your Own Functions
- Passing Data Through Work Tables
- Using Triggers
- Performing Character Set Conversion
- SQL*Loader and Character Sets
- Data in the SQL*Loader control file
- Datatypes affected
- Characters that can't be converted
- Syntax for Specifying the Character Set
- Character Set Example
- Transaction Size and Performance Issues
- Transaction Processing in SQL*Loader
- Commit Frequency and Load Performance
- READSIZE
- BINDSIZE
- ROWS
- Large Versus Small Bind Arrays
- Setting READSIZE, BINDSIZE, and ROWS
- Commit Frequency and Rollback Segments
- Rollback Segment Errors
- Estimating Rollback Segment Requirements
- Step 1: Determine the size of a single row
- Step 2: Determine the size of the bind array
- Step 3: Estimate the size of the rollback segment required
- Using the Right Rollback Segments
- Performance Improvement Guidelines
- Direct Path Loads
- What is the Direct Path?
- Performing Direct Path Loads
- Preparing to Use Direct Path Loads
- Invoking a Direct Path Load
- Restrictions on Direct Path Loads
- Storage Issues with Parallel Direct Path Load
- Index Maintenance with Direct Path Loads
- Storage requirements for index maintenance
- Unusable index state
- SINGLEROW index option
- Presorting your input data
- Direct Path Loads and Integrity Constraints
- Direct Path Loads and Database Triggers
- Data Saves
- Loading Data Fields Greater than 64K
- UNRECOVERABLE Loads
- Parallel Data Loading
- Preparing for Parallel Data Loading
- Concurrent Conventional Path Loads
- Concurrent Direct Path Loads
- Loading multiple segments concurrently
- Loading one segment concurrently
- How a parallel direct path load works
- Restrictions on parallel direct path loads
- Storage management with parallel direct path loads
- Loading Large Objects
- About Large Objects
- What Are Large Objects?
- This Chapter's Examples
- Understanding Your Options
- One object, one file
- Many LOBS in one file
- LOBS in your main data file
- Considerations when Loading LOBs
- Record and Field Sizes
- DEFAULTIF and NULLIF
- Loading Inline LOBs
- Treating a LOB Like Scalar Data
- Changing the Stream Delimiter
- Loading LOBs from External Data Files
- LOBFILE Clause Syntax
- Loading Entire Files
- Placing Multiple LOBs in One File
- Loading BFILEs
- Directory Objects
- BFILE Clause Syntax
- BFILE Field Specifications
- Loading Objects and Collections
- Loading Object Tables and Columns
- Loading Object Tables
- Loading Object Columns
- Creating an object column
- The COLUMN OBJECT clause
- Loading Collections
- Memory Implications
- Loading Collections from Inline Data
- Specifying an inline collection
- Example: Loading inline, delimited data
- Example: Specifying an occurrence count
- Loading Collections from Secondary Data Files
- Specifying a secondary data file
- Example: Loading a collection from a secondary data file
- Using NULLIF and DEFAULTIF with an Object or a Collection
- Applying NULLIF or DEFAULTIF to an Object or a Collection as a Whole
- Applying NULLIF or DEFAULTIF to a Field Within an Object or a Collection
- Index
System requirements
File format: PDF
Copy-Protection: Adobe-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Install the free reader Adobe Digital Editions prior to download (see eBook Help).
- Tablet/smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook before downloading (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (only limited: Kindle).
The file format PDF always displays a book page identically on any hardware. This makes PDF suitable for complex layouts such as those used in textbooks and reference books (images, tables, columns, footnotes). Unfortunately, on the small screens of e-readers or smartphones, PDFs are rather annoying, requiring too much scrolling.
This eBook uses Adobe-DRM, a „hard” copy protection. If the necessary requirements are not met, unfortunately you will not be able to open the eBook. You will therefore need to prepare your reading hardware before downloading.
Please note: We strongly recommend that you authorise using your personal Adobe ID after installation of any reading software.
For more information, see our eBook Help page.