Loading Data into Oracle with SQL Loader

2013-10-18 Tags: databases oracle

This post will be, what I hope, an easy guide on how to use SQL loader to load data into an Oracle database. This tasks is a required component in DBST652 at UMUC so hopefully this post will be able to help out future students who are learning the magic of oracle. With that in mind, this post is geared toward UMUC students who are taking this course (especially when it comes to connecting to the server etc.) SQL loader is a neat little utility for loading datasets into tables in Oracle. In my few months of using Oracle, I have found it to be overall cumbersome when compared to other DBSM like MS SQL Server, or even MySQL. Oracle SQL Developer is slow and clunky and a pain to use, iSQLPlus is an “enterpirse” version of phpmyadmin and although it works, it is also a pain to use. The only good thing about Oracle is that it is pretty simple to connect through a terminal and it has some neat utilities such as SQL Loader. So let’s dig right in. To use SQL Loader you need three things.

  1. A text editor
  2. A control file
  3. A Terminal Application
The terminal app allows you to connect to a SQL database and run the SQL loader utility, a text editor allows you to create your control file, and your control file contains the data that you want to load into a table. For this example we are going to load a data set into a table called EMPLOYEE which contains information about an employee at a company. The EMPLOYEE table has not yet been created in our database – first we must create the table. A simple query for that would be: CREATE TABLE EMPLOYEE (empid int, FirstName char(50), LastName char(50), MiddleName char(10), Address char(50), City Char(50), State Char(2), ZipCode number, PhoneNumber varchar(20));

Creating the Control File for SQL Loader

First, open up your favorite text editor and create the control file. The control file typically looks something like this. LOAD DATA  INFILE * APPEND INTO TABLE EMPLOYEE FIELDS TERMINATED BY ‘|’ OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘ TRAILING NULLCOLS ( EmpID,  FirstName,  LastName,  MiddleName, Address,  City,  State, ZipCode, PhoneNumber) begindata 1 | “John” | “Smith” | “I” | “5210 Smitty Ln.” | “Cincinnati” | “OH” | 45221 | “513-120-1234” |  2 | “Betty” | “Crocker” | “J” | “1200 Candy Ln.” | “Cincinnati” | “OH” | 45212 |  “513-122-2345” |  3 | “Paul” | “Jones” | “A” | “3450 Revolution Blvd.” | “Cincinnati” | “OH” | 45332 |  “513-345-3452” |  4 | “Peter” | “Burges” | “B” | “435 Hollins Ave. ” | “Cincinnati” | “OH” | 45223 |  “513-223-4353” |  5 | “Mary” | “Brown” | “J” | “5636 5th St. ” | “Cincinnati” | “OH” | 45221 | “513-435-3435″| The text highlighted in orange above is the name of the table that you are going to be putting the data into.  The text highlighted in blue above are the names of the columns in the table that you are going to be adding the data into.  The text highlighted in green above is the data that you are going to be loading. Each field is separate by a “|” pipe and corresponds to the name of the column that the data is going to be written into.  When you have loaded all of your data we are ready to move on to the next step. Open up a terminal app and connect to your database server. [For UMUC this is typically dbcourse2.umuc.edu]  ssh username@databaseserver.com  Open up the pico text editor on your server (most UNIX servers have PICO, most Linux servers will have nano — it’s the same application)
Copy your control file into pico and hit Ctrl+O to save the file. A good naming convention is to name the file based on the table that it is going to control. In this case we will call our file employee.ctl . Once you have saved the file close pico by hitting Ctrl+X and you will be back to the command line. Now we are ready to use SQL loader to load the data.

Loading the Data Using SQL Loader

Most command line oracle activity takes place by invoking sqlplus and running queries. SQL loader can be run directly from the main terminal without going into sql plus first. You can invoke SQL loader and load the control file by running the following command in the same directory where the control file is loaded. In our case we saved the control file into the home directory so we do not need to change the directory. sqlldr userid=userid control=employee.ctl SQL loader will now prompt for a password, and if everything was successful you will see the following output. We can now log into sqlplus and run a SELCT * FROM EMPLOYEE to see all of our data. That’s it! Keep in mind that this is a very simple example and the goal was to get up and running with SQL Loader. The control file can be used to point to other files with larger data sets (instead of adding the data directly in the control file) which reduces the amount of time it takes to format the text properly. If you have a large data set , SQL Loader is the best way to go. It sure is a lot better than writing a thousand insert statements!