Browse DevX
Sign up for e-mail newsletters from DevX


Book Excerpt: Oracle9i The Complete Reference : Page 2

This book covers critical relational, object-relational, and Web database concepts, as well as SQL, SQL*PLUS, PL/SQL, Java, JDBC, SQLJ, and XML programming. You'll also get full details on database administration techniques—and much more. Read all of Chapter 21:''Using SQL*Loader to Load Data.''




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Loading Variable-Length Data

If the columns in your input file have variable lengths, you can use SQL*Loader commands to tell Oracle how to determine when a value ends. In the following example, a comma separates the input values:

LOAD DATA INFILE 'bookshelf.dat' BADFILE '/user/load/bookshelf.bad' TRUNCATE INTO TABLE BOOKSHELF FIELDS TERMINATED BY "," (Title, Publisher, CategoryName, Rating)

The fields terminated by "," clause tells SQL*Loader that during the load, each column value will be terminated by a comma. Thus, the input file does not have to be 142 characters wide for each row, as was the case in the first load example. The lengths of the columns are not specified in the control file, since they will be determined during the load.

In this example, the name of the bad file is specified by the badfile clause. In general, the name of the bad file is only given when you want to redirect the file to a different directory.

This example also shows the use of the truncate clause within a control file. When this control file is executed by SQL*Loader, the BOOKSHELF table will be truncated before the start of the load. Since truncate commands cannot be rolled back, you should use care when using this option. In addition to truncate, you can use the following options:

  • append   Adds rows to the table.
  • insert   Adds rows to an empty table. If the table is not empty, the load will abort with an error.
  • replace   Empties the table and then adds the new rows. The user must have DELETE privilege on the table.

Starting the Load

To execute the commands in the control file, you need to run SQL*Loader with the appropriate parameters. SQL*Loader is started via the SQLLDR command at the operating system prompt.

NOTE:   The SQL*Loader executable may consist of the name SQLLDR followed by a version number. Consult your platform-specific Oracle documentation for the exact name. For Oracle9i, the executable file should be named SQLLDR.

When you execute SQLLDR, you need to specify the control file, username/password, and other critical load information, as shown in Table 21-1.

SQLLDR Keyword



Username and password for the load, separated by a slash.


Name of the control file.


Name of the log file.


Name of the bad file.


Name of the discard file.


Maximum number of rows to discard before stopping the load. The default is to allow all discards.


Number of logical rows in the input file to skip before starting to load data. Usually used during reloads from the same input file following a partial load. The default is 0.


Number of logical rows to load. The default is all.


Number of errors to allow. The default is 50.


Number of rows to commit at a time. Use this parameter to break up the transaction size during the load. The default for conventional path loads is 64; the default for Direct Path loads is all rows.


Size of conventional path bind array, in bytes. The default is operating-system-dependent.


Suppress messages during the load.


Use Direct Path loading. The default is FALSE.


Name of the parameter file that contains additional load parameter specifications.


Perform parallel loading. The default is FALSE.


File to allocate extents from (for parallel loading).


Allows loads into tables that have indexes in unusable states. The default is FALSE.


Stops index maintenance for Direct Path loads, leaving them in unusable states. The default is FALSE.


Size of the read buffer; default is 1MB.


Use external table for load; default is NOT_USED; other valid values are GENERATE_ONLY and EXECUTE.


Number of rows for direct path column array; default is 5,000.


Size in bytes of the direct path stream buffer; default is 256,000.


A flag to indicate if multithreading should be used during a direct path load.


A TRUE/FALSE flag to enable or disable resumable operations for the current session; default is FALSE.


Text identifier for the resumable operation.


Wait time for resumable operation; default is 7200 seconds.

Table 21-1   SQL*Loader Options

Each load must have a control file, since none of the input parameters specifies critical information for the loadthe input file and the table being loaded.

If you want, you can separate the arguments to SQLLDR with commas. Enter them with the keywords (such as userid or log), followed by the parameter value. Keywords are always followed by an equal sign (=) and the appropriate argument.

If the userid keyword is omitted, you will be asked for it. If a slash is given after the equal sign, an externally identified account will be used. You also can use an Oracle Net database specification string to log in to a remote database and load the data into it. For example, your command may start:

sqlldr userid=usernm/mypass@dev

The direct keyword, which invokes the Direct Path load option, is described in "Direct Path Loading" later in this chapter.

The SILENT parameter tells SQLLDR to suppress certain informative data:

  • HEADER suppresses the SQL*LOADER header.
  • FEEDBACK suppresses the feedback at each commit point.
  • ERRORS suppresses the logging (in the log file) of each record that caused an Oracle error, although the count is still logged.
  • DISCARDS suppresses the logging (in the log file) of each record that was discarded, although the count is still logged.
  • PARTITIONS disables the writing of the per-partition statistics to the log file.
  • ALL suppresses all of the preceding.
If more than one of these is entered, separate each with a comma and enclose the list in parentheses. For example, you can suppress the header and errors information via the following keyword setting:


NOTE:   Commands in the control file override any in the calling command line.

Let's load a sample set of data into the BOOKSHELF table, which has four columns (Title, Publisher, CategoryName, and Rating). The data to be loaded is in a file called bookshelf.txt, and consists of two records:

Good Record,Some Publisher, ADULTNF, 3
Another Title, Some Publisher, ADULTPIC, 4

NOTE:   Each line is ended by a carriage return. Even though the first line's last value is not as long as the column it is being loaded into, the row will stop at the carriage return.

The data is separated by commas, and we don't want to delete the data previously loaded into BOOKSHELF, so the control file will look like this:

INFILE 'bookshelf.txt'
(Title, Publisher, CategoryName, Rating)

Save that file as bookshelf.ctl, in the same directory as the input data file. Next, run SQLLDR and tell it to use the control file:

sqlldr practice/practice control=bookshelf.ctl log=bookshelf.log

When the load completes, you should have one successfully loaded record and one failure. The successfully loaded record will be in the BOOKSHELF table:

select Title
 where Publisher like '%Publisher';

Good Record

A file named bookshelf.bad will be created, and will contain one record:

Another Title,Some Publisher,ADULTPIC,4

Why was that record rejected? Check the log file, bookshelf.log, which will say, in part:

Record 2: Rejected - Error on table BOOKSHELF.
ORA-02291: integrity constraint (PRACTICE.CATFK) violated -
parent key not found

  1 Row successfully loaded.
  1 Row not loaded due to data errors.

Row 2, the "Another Title" row, was rejected because the value for the CategoryName column violated the foreign key constraintADULTPIC is not listed as a category in the CATEGORY table.

Because the rows that failed are isolated into the bad file, you can use that file as the input for a later load once the data has been corrected.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date