SQL*Loader is an Oracle utility that enables you to efficiently load large amounts of data into a database. If you have data in a flat file, such as a comma-delimited text file, and you need to get that data into an Oracle database, SQL*Loader is the tool to use. This chapter introduces you to the SQL*Loader utility, discusses its control file, provides the syntax for using the SQL*Loader command, and provides examples of using SQL*Loader to load data into databases.
Using SQL*Loader, you can do the following:
Ø Load data from a delimited text file, such as a comma delimited file
Ø Load data from a fixed-width text file
Ø Load data from a binary file
Ø Combine multiple input records into one logical record
Ø Store data from one logical record into one table or into several tables
Ø Write SQL expressions to validate and transform data as it is being read from a file
Ø Combine data from multiple data files into one
Ø Filter the data in the input file, loading only selected records
Ø Collect bad records—that is, those records that won’t load—into a separate file where you can fix them
Control files, contain a number of commands and clauses describing the data that SQL*Loader is reading. Control files also tell SQL*Loader where to store that data, and they can define validation expressions for the data. Understanding control file syntax is crucial to using SQL*Loader effectively. The control file is aptly named, because it controls almost every aspect of how SQL*Loader operates. The control file describes the format of the data in the input file and tells SQL*Loader which tables and columns to populate with that data.
Understanding the SQL*Loader Commands
The syntax for
the SQL*Loader command looks like this: sqlldr [param=value[, param=value…]]
userid : Passes in your username, password, and Net8 service name. The syntax to use is the same as for any other command-line utility, and looks like this: userid=username[/password][@service]
control: Passes in the control file name. Here’s an example: control=[path]filename[.ext] The default extension for control files is .ctl.
log: Passes in the log file name. Here’s an example: log=[path]filename[.ext] The default extension used for log files is .log. If you don’t supply a file name, the log file will be named to match the control file.
bad Passes in the bad file name. Here’s an example: bad=[path]filename[.ext] The default extension for bad files is .bad. If you don’t supply a file name, the bad file will be named to match the control file. Using this parameter overrides any file name that may be specified in the control file.
data Passes in the data file name. Here’s an example: data=[path]filename[.ext] The default extension used for data files is .dat. Specifying a data file name on the command line overrides the name specified in the control file. If no data file name is specified anywhere, it defaults to the same name as the control file, but with the .dat extension.
discard Passes in the discard file name. Here’s an example: discard=[path]filename[.ext]
The default extension used for discard files is .dis. If you don’t supply a file name, the discard file will be named to match the control file. Using this parameter overrides any discard file name that may be specified in the control file.
discardmax Optionally places a limit on the number of discarded records that will
be allowed. The syntax looks like this: discardmax=number_of_records If the number of discarded records exceeds this limit, the load is aborted.
skip Allows you to skip a specified number of logical records. The syntax looks like this:
skip=number_of_records Use the skip parameter when you want to continue a load that has
been aborted and when you know how far into the file you want to go before you restart.
load Optionally places a limit on the number of logical records to load into the database. The syntax looks like this:
load=number_of_records Once the specified limit has been reached, SQL*Loader will stop.
Errors Specifies the number of errors to allow before SQL*Loader aborts the load. The syntax looks like this: errors=number_of_records SQL*Loader will stop the load if more than the specified number of errors has been received. The default limit is 50. There is no way to allow an unlimited number. The best you can do is to specify a very high value, such as 999999999.
Rows indirectly controls how often commits occur during the load process. The rows parameter specifies the size of the bind array used for conventional-path loads in terms of rows. SQL*Loader will round that value off to be some multiple of the I/O block size. The syntax for the rows parameter looks like this: rows=number_of_rows The default value is 64 for conventional-path loads. Direct-path loads, by default, are saved only when the entire load is done. However, when a direct-path load is done, this parameter can be used to control the commit frequency directly.
4. Run the applypreferences script.
Ø SQL*Loader is a versatile utility for loading large amounts of data into an Oracle database.
Ø SQL*Loader control files are used to describe the data being loaded and to specify the table(s) into which that data is stored.
Ø You can use the INFILE clause to identify the file, or files, that you want SQL*Loader to read.
Ø You can use the INTO TABLE clause to identify the table, and the columns within that table that you wish to populate using the data read from the input file.
Ø You can use the APPEND option after the INFILE clause to tell SQL*Loader to insert data into a table that already contains data to begin with.
Ø You can use SQL*Loader to load delimited data, such as comma-delimited data, or you can use it to load data stored in fixed-width columns.
Ø SQL*Loader fully supports all of Oracle8i’s datatypes, even to the point of allowing you to populate LOB columns.
Thanks Shivmohan Purohit