Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• Lesson 21: SQL*Loader
• SQL*Loader is the tool DBAs and developers use to load records from sequential files into Oracle tables. This lesson provides an overview of loading data using SQL*Loader.

Instructor's Notes

(If applicable)

• SQL*Loader is the tool DBAs and developers use to load records from sequential files into Oracle tables. It is a very robust utility.
• The input data is a sequential file containing fixed or variable length records. The fields in a variable length record are usually delimited by commas. The fields in a fixed length record start at specific positions within the record; the control file must inform SQL*Loader at what positions in the input record it will find the various fields.
• The control file is a text file where we specify the controls for the load.

Instructor's Notes

(If applicable)

• To execute SQL*Loader, you run the program from the command line of your operating system. The example in this slide loads this table:
• create table emp
• (empno number primary key,
• lastname varchar(30) not null,
• firstname varchar(30) not null,
• salary number);
• There are many command line parameters that control the SQL*Loader operation. You can either include those parameters individually on the command line or you can save them in a file and then reference the file using the PARFILE parameter.
• If you invoke SQL*Loader with no parameters, SQL*Loader displays a help screen with all available parameters and default values. The following list shows common SQL*Loader command line parameters:
• USERID — Oracle username/password. Specifies the user to connect to when performing the data load. Make sure the user has the necessary privileges.
• CONTROL — Control file name. Refer to the notes on Control Files in this lesson for more information.
• Notes continue on the next page…

Instructor's Notes

(If applicable)

• LOG — Log file name. SQL*Loader writes information and error messages to this file. Default file name is control file name with an extension of .LOG
• BAD — Bad file name. This is the file where SQL*Loader writes records that cannot be loaded due to conversion errors. Default bad file name is control file with an extension of .BAD.
• DISCARD — Discard file name. This is the where SQL*Loader writes records that do not pass the WHEN condition test. More on this later in this lesson.
• DISCARDMAX — Number of discards to allow (Default all).
• DATA — Data file name. Specifies the name of the data file that contains the data to load into the target table. Specify the data option only if you do not include the INFILE parameter in the control file or if you want to override the INFILE parameter in the control file. Refer to the notes on Control Files in this lesson for more information on the INFILE parameter.
• SKIP — Number of logical records to skip (Default 0)
• LOAD — Number of logical records to load (Default all)
• ERRORS — Number of errors to allow (Default 50)
• ROWS — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
• DIRECT — Use TRUE to invoke direct path load (default is FALSE).fsf
• PARFILE — Parameter file: name of file that contains parameter specifications
• PARALLEL — Perform parallel load (Default FALSE)

Instructor's Notes

(If applicable)

• The control file controls the SQL*Loader operation and describes the records to be loaded. A common misconception that leads to coding errors is thinking that the control file FIELDS parameter describes the table being loaded.
• Common control file parameters are:
• LOAD DATA/CONTINUE_LOAD - First option in file. CONTINUE_LOAD restarts an interrupted load.
• INFILE – Names the input file. The input file can alternatively be specified in the DATA parameter (on the "SQLLDR" command line). Use an asterisk (*) for in-stream data.
• BEGINDATA – Starts in-stream data. Use with INFILE parameter.
• INSERT/APPEND/REPLACE
• INSERT Table must be empty; error otherwise
• APPEND Adds data to the table; table can be empty
• REPLACE Deletes data, then inserts
• INTO TABLE tablename – Names the table to be loaded.
• The FIELDS parameter describes the input record and indicates which columns will receive data.

Instructor's Notes

(If applicable)

• Variable length data needs a delimiter character between the fields in the records. These examples use comma-delimited data.
• The first example is loaded in the EMP table:
• create table emp
• (empno number primary key,
• lastname varchar(30) not null,
• firstname varchar(30) not null,
• salary number)


• The second example is loaded into the EMP2 table:
• create table emp2
• (empno number primary key,
• name varchar(30) not null,
• hiredate date,
• salary number)

Instructor's Notes

(If applicable)

• This example shows that the control file can include the data to be loaded. The “INFILE *” parameter tells SQL*Loader that the data is within the control file (in-stream data). BEGINDATA marks the start of the in-stream data.

Instructor's Notes

(If applicable)

• The control file describes the records to be loaded. A common misconception that leads to coding errors is thinking that the control file describes the table being loaded.
• The column names within the table (DAVE.EMP) and the position of the values for those columns within the data file must be identified. Note that EMPNO, LASTNAME, etc are the actual column names from the table while the POSITION parameter identifies the location of the values within the data file which will be placed into those columns.
• The datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify the datatypes of the data in the data file, not the datatypes of the columns in the table. For detailed information about available datatypes, see the Oracle8i Utilities manual.
• Supplemental Notes
• Binary data must be in a fixed-format file
• Blanks are loaded as null
• Short fields are padded with blanks

Instructor's Notes

(If applicable)

• The LOG file contains critical diagnostics. For example, this load seems to indicate that all went well:
• C:\OracleScripts\DBA>sqlldr dave/dave control=emp3.ctl


• SQL*Loader: Release 9.2.0.1.0 - Production on Fri Oct 17
• Copyright (c) 1982, 2002, Oracle Corporation. All rights re


• Commit point reached - logical record count 2
• C:\OracleScripts\DBA>


• However, by examining the LOG file (an excerpt is shown in the slide), we can see that all is not OK with this load. The records were rejected because of a constraint failure. The rejected records were written to the BAD file.

Instructor's Notes

(If applicable)

• The WHEN option can be used to test a condition about the records being loaded to determine if they should be loaded or discarded. WHEN is similar to the WHERE clause of the SELECT statement. Records that do not meet the criteria specified in the WHEN clause are written to the discard file. In the slide example, we modified the control file to include a WHEN clause that only accepts records that have ‘And’ in positions 3-5. The comparison is case sensitive.
• The WHEN clause can test in one of two ways:
• Test the field to be loaded based on its position against a character or hex string
• Test the value being loaded against the column name within the table
• In either case, you may only test equal or not equal conditions.
• Examples:
• WHEN (3-10) = ‘Anderson’
• WHEN emp_no = ‘6’

Instructor's Notes

(If applicable)

• SQL*Loader Workshop
  1. Run the supplied script SALES.SQL to create a table called SALES in your schema.
  2. Review the data found in the supplied file "SALES.DAT". Note that each field is terminated by a “|” symbol.
  3. Use SQL*Loader to load all records in the SALES.DAT file into the SALES table.
  4. Query the SALES table to determine how many records were loaded. You should have 48,166 records.