Loading Data With SQLLDR (SQL Loader)

IS 475

Loading Data With SQLLDR (SQL Loader)

SQLLDR.EXE is a program you can use to import large amounts of data quickly. The data to be imported must be in a consistent layout, i.e. same number of columns in each row. The fields must be separated with a delimiter; for example, a semi-colon or tab. Empty fields needs to be designated with NULL. The import process requires a CTL (control) section or file with information about how the data is structured and a DAT (data) section with the data to import.

  1. In trader8, log on with your regular user account and drop your university tables.
  1. Download the revised university zipped file in the DBA learning module. It is important that this file be in a disk and directory with any spacing the name. It is recommended that you download this file to C:\temp. When this revised university database is built and populated, you’ll see that another table has been added. Chapter 11 in Mannino presents this revised data model.
  • A REGISTRATION table has been added between STUDENT and ENROLLMENT.
  • The OFFERING table now has two more fields: offLimit and OffNumEnrolled.
  1. Unzip the file you downloaded. You can put the files in a subfolder but be sure not to have spacing in the folder name. For example: c:\temp\univ_revised
  1. From SQLPlus and run the create_revised_univ_tables script using the following command syntax:

@c:\temp\univ_revised\create_revised_univ_tables;

NOTE: When you log on to SQL Plus you must enter your ID and the database server configuration name. Example: jkreie@trader8

  1. Open studentCTL.sql to see what it contains. The first part of the file is the CTL section.
  • The first two lines specify that data to be loaded is contained within this file. (It is possible reference a separate data file.)
  • The third line specifies an output file for errors that may occur. Because no directory path is given, the error file will be in the same folder as the CTL file.
  • The remaining lines before BEGINDATA specify what the field delimiter is (comma in this case), what symbol surrounds text data, and the order and name of fields to be populated.

The data section begins with BEGINDATA.

  1. Along with the SQL files, the zipped file also has a copy of SQLLDR.EXE. This file can be found in the BIN folder where ever Oracle is installed on a hard drive. A copy has been provided here so you don’t have to find it. To run this program you will log on SQLPlus, not SQL Developer.
  1. At the SQL prompt, type hostand hit enter.--This will move control from the SQL Plus interface to operating system at the command-line (no Windows here).
  1. Change to the drive with your CTL files.

Type the drive letter followed by a colon and hit enter.

Example: C: --This moves to drive C.

Move to the folder where SQLLDR is stored (the path may vary).

cd \temp\university_revised

  1. Run sqlldr.exe based on the following example to explain the command syntax. The following syntax assume the CTL file resides in the temp folder on the C drive.

Example: sqlldr jkreie@trader8/mypassword studentctl.sql

Syntax: sqlldr <userID>@<connectString/<password> <drive:\dir_path\controlFilename.ext

NOTE: The drive letter and directory path aren’t needed if all the files are in the same location.

  1. Repeat this step for each file. Keep in mind the order in which the tables must be populated.
  1. Verify all the records were imported successfully by opening the <control filename>.log file. This file is automatically created by the SQLLoader utility. There will also be a <control filename>.bad file that lists the rejected records, if there are any.

Note: If you want to start over when importing data to a table, you may want to remove the records that were previously imported. Use the following command at the SQL prompt of the SQL Plus window. This example removes ALL the records in the zipcode table.

SQL> delete from zipcode;

  1. You return to the SQL Plus window by typing exit and hitting enter.

What to submit in the assignment dropbox:

  1. Start a spool file. Example: spool c:\temp\revised_univ_count.txt;
  1. Show user and system date. (See past assigment files for these commands, if you don’t remember.)
  1. Display the record count in each university table. Use a column alias to specify which table records are counted.
  1. End the spool file. Example: spool off;
  1. Submit the spool file and one LOG filefor student.log. A LOG file is created for each file loaded.

Possible Problems & Solutions:

If you start the SQL Load utility in the HOST window and the cursor seems frozen (more than 5-10 seconds), hit CTRL-C to stop the program. Make sure you don’t have any of the files that SQL Loader uses open with Notepad or any other program.

The above “message 2100” means you’re probably trying to run the wrong version of SQLLDR.EXE.

Repeat this exercise after you complete DBA lesson 2 and you have created a regular use account for yourself. Create the university tables and import the data into your regular user account (NOT your database administrator account).

ora_SQLLDR.doc - 1