This document demonstrates how to use Secure SQL Loader to load data directly into your Instructional Oracle account ( If you do not have an Instructional Oracle account and would like to have one, go to Princeton University Instructional Oracle Account Facility ) .

This tool is built on top of the Oracle database tool SQL*Loader and so it important to be familar with Oracle SQL*Loader Concepts. This demonstration is not intended to be a complete and exhaustive tutorial on Oracle SQL Loader tool. If you need assistance with your data loading please contact request-WWW.html

The Secure SQL Loader requires that the user has two world-readable files in your CIT Unix home directory. One file is the data file and contains the actual data to be loaded. The name of the data file must end with the .dat extention. The other file is the control file and the the contents of this control determine the target table and the rules for loading the data. The name of the control file must end with the .ctl extension. In this demo, the account is jkchu ( Note the home directory is /u/jkchu ), the target table is temp_table , the datafile is mydatafile.dat, and the control file is mycontrolfile.ctl. Use the ls command, to verify that the files are world-readable ( indicated by the three "r" in the file permissions).

phoenix.Princeton.EDU% ls -ldt /u/jkchu/mydatafile.dat
-rw-r--r--   1 jkchu          48 Jul 20 09:59 /u/jkchu/mydatafile.dat
phoenix.Princeton.EDU% ls -ldt /u/jkchu/mycontrolfile.ctl
-rw-r--r--   1 jkchu          78 Jun 28 13:58 /u/jkchu/mycontrolfile.ctl

SQL> describe temp_table;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER
 TEXT                                     VARCHAR2(2000)

So the table temp_table has two columns, "ID" and "TEXT". The first column is a number and the second column is text. Here are the contents of the control file mycontrolfile.dat

phoenix.Princeton.EDU% cat /u/jkchu/mycontrolfile.ctl
LOAD DATA
TRUNCATE
INTO TABLE TEMP_TABLE
FIELDS TERMINATED BY ","
(id,
text
)

The command INTO TABLE TEMP_TABLE means the target table is TEMP_TABLE.
The command TRUNCATE means remove all the rows in the target table.
The command FIELDS TERMINATED BY "," means the data values are separated by commas.
The order is the data is the column id followed by the column text.

Here are the contents of the data file mydatafile.dat Note that data delimiter is the "," character as specified in the control file.

phoenix.Princeton.EDU% cat /u/jkchu/mydatafile.dat
1,Line 1
2,Line 2
3,Line 3
a,line a
4,Line 4
5,

Note that there are two "bad" lines of data. The line with starting with "a," is bad because it is not numeric. The line starting with "5," is bad because there is no second value ( this situation can be "corrected" by the appropriate statement in the control file ). Using the Secure SQL Loader I specify jkchu as the netid, the appropriate Oracle password, mydatafile.dat as the data file, and mycontrolfile.ctlas the the control file. I use the default values for the rest of the parameters to the Secure SQL Loader. Next I click on the button labeled Execute SQL Loader. Here are the results. There were four records loaded, two records are labeled as bad and were not loaded, and no records were discarded.

SQL*Loader: Release 8.0.5.0.0 - Production on Thu Jul 20 10:23:49 2000

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 6

All Done

SQL*Loader: Release 8.0.5.0.0 - Production on Thu Jul 20 10:23:49 2000

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Control File:   /u/jkchu/mycontrolfile.ctl
Data File:      /u/jkchu/mydatafile.dat
  Bad File:     /tmp/ldr_15613.bad
  Discard File: /tmp/ldr_15613.discard 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table TEMP_TABLE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,       CHARACTER            
TEXT                                 NEXT     *   ,       CHARACTER            

Record 6: Rejected - Error on table TEMP_TABLE, column TEXT.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TEMP_TABLE, column ID.
ORA-01722: invalid number


Table TEMP_TABLE:
  4 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  65532 bytes(127 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Thu Jul 20 10:23:49 2000
Run ended on Thu Jul 20 10:23:50 2000

Elapsed time was:     00:00:00.61
CPU time was:         00:00:00.12    

5,
a,line a