Lab 8: SQL*Loader and External Tables
After completing this lab you should be able to:
- Load data from a text file into a table using SQL*Loader.
- Create an "external" table.
Lab Assignment Steps
- Create a table of stock prices with columns and data types as shown below.
Make the symbol and close_date
columns the primary key.
SQL> desc stock_price
Name Null? Type
------------------------------- -------- ----
SYMBOL NOT NULL VARCHAR2(5)
CLOSE_DATE NOT NULL DATE
Next, you will need to bulk load data into this table.
You do this with an Oracle database utility called SQL*Loader.
Your control file will look like this:
options (direct=false, rows=50000)
into table stock_price
fields terminated by ','
(symbol, close_date date "YYYYMMDD", open, hi, lo, close, volume)
You will create this control file with a text editor.
This is not a SQL file so don't create this in sqlplus, don't run it in sqlplus.
- Then to load the data, you use this command from the UNIX command line:
sqlldr userid=user/password control=stocks.ctl
You can simply type sqlldr at the command line to get help for this utility.
Also, sqlldr is another alternate spelling for the command.
The control files typically end with a .ctl extension.
A log file will store the results of your load, its extension will be .log and
will have the same name as the control file.
Records that are not loaded are stored in a bad file, its extension will be .bad
and will also have the same name as the control file.
The datafile you need can be downloaded here -->
You can also reference the data file in your .ctl file (in the infile part of the above) or copy it from this location (using the UNIX cp command) from in my directory: /home/droberts/public_html/n311/stocks.csv.
- Now you will create another table called STOCK_PRICE_EXTERNAL with these requirements:
- The columns for STOCK_PRICE_EXTERNAL will be the same as STOCK_PRICE that you created in step 1.
- You must use the ORGANIZATION EXTERNAL clause as described in chapter 25.
- The directory you will use is N311_DIR.
- The file in that directory is stocks.csv, the same file used to sqlload the other table.
You will also need to the following to make the external table work when you select from it:
Create your own directory to store the Log and Bad files. (even selecting from the external table generates the log files which require write permissions). So you will create a directory, map it to your own phoenix account with full OS permissions (chmod 777 yourdirectory).
- In your access parameters clause of the external table, you will need to specify where the bad and log files go. They will be located in your directory.
- example of how mine worked:
create table droberts.stock_price_external (
default directory N311_DIR
access parameters (records delimited by newline
fields terminated by ','
- Turn in the following:
I will be checking that both of your table have the same data in them so be sure that your tables are created and loaded properly.
- The SQL*Loader control (.ctl) file and log (.log) files from steps 2 and 3.
- The DDL (create table statements) from steps 1 and 4.