| melchman's guide to: | SQL*Loader |
|---|
| Prerequisites: | Using Oracle for Undergraduate Students |
|---|---|
| O'Reilly's Guide: Oracle Database Administration, Chapter 16 | |
| Sources: | Oracle 8i Utilities Reference, Chpater 5-6 |
What is SQL*Loader?
It is a utility program that loads data from a file into an Oracle table. The file should be a text file. The file format can varyand these variations need to be explained to SQL*Loader.
SQL*Loader is a command line program (sqlldr) and it takes parameters in Keyword=Value pairs. Typing SQLLDR at the prompt will get you a list of parameters. The program does not work well without parameters and an input file.
How do I import my text file?
- Get your text data file.
- Build your control file.
- Execute sqlldr with the parameters
- Inspect the output
sqlldr username=myname control=my.ctl
Data File
The data file may already exist as output from another utility. Be sure it is a text file. Carefully and completely understand the format of the file. There are 2 basic file formats: Delimited and Zoned. The format of each field and record should be consistent and follow the rules or the record will not be imported.
Zoned
2384 Col. Jack O'Neill SGC 1983-04-15
8492 Cpt. Samantha Carter SGC 1996-12-01
9637 Daniel Jackson, Ph. D. P3X79 1998-06-29
5387 Teal'k SGC 1999-08-17
1938 Gen. George Hammond SGC 1965-06-01
Delimited
2384,"Col. Jack O'Neill",SGC,1983-04-15
8492,"Cpt. Samantha Carter",SGC,1996-12-01
9637,"Daniel Jackson, Ph. D.",P3X79,1998-06-29
5387,"Teal'k",SGC,1999-08-17
1938,"Gen. George Hammond",SGC,1965-06-01
| Advantages and Faults | |
|---|---|
| Zoned | Delimited |
| fixed length fields | variable length fields and records |
| Position defines field data | Field data separated by comma (or other Delimiter) |
| No Enclosure needed | Enclosures allow the delimiter to be present in field data |
| large files with wasted whitespace | smaller files |
| Errors caused by bad field length and Tab characters | Errors caused by unenclosed delimiters |
Delimited File Basics
LOAD DATA
INFILE 'MYDATA.DAT'
INSERT INTO TABLE employee
FIELDS TERMINATED BY ','
id, name, location, hired)
This control file would import the delimited file above (if it was called MYDATA.DAT) into the employee table. It expects 4 fields of various types separated by commas.
Our first bit of trouble comes with the name field. Daniel Jackson, Ph. D. contains a comma (the delimiter) and will cause us trouble unless we enclose it. The enclosure is enable by adding to the TERMINATED BY clause:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
This sets the enclosure delimiter to the double quote. The OPTIONALLY keyword tells SQL*Loader not to expect the quotes, but to heed them when they are found. If your data source and export utility use a single quote or some other character specify that character with this clause. The double quote allows us to accept Jack O'Neill without difficulty therefore I prefer using it.
Zoned File Basics
LOAD DATA
INFILE 'MYDATA.DAT'
INSERT INTO TABLE employee
id POSITION(1:4),
name POSITION(6-30),
location POSITION(32:36),
hired POSITION(38:47))
This control file would import the Zoned file above (if it was called MYDATA.DAT) into the employee table. It expects 4 fields of various types.
We avoid the trouble with Daniel Jackson, Ph. D. and Jack O'Neill. Trouble may occur if those apparent spaces includ a Tab character. The tab character will only count as one position thought it may appear to be 4, 5 or more! This will cuase the following field to shift and be imported improperly or skipped entirely.
Additional Output
Additional file can be generated by SQL*Loader to help diagnose and repair imports that don't go according to plan. These are extremely helpful with large files and porrly formatted files.
BADFILE
Records with formatting errors are pushed to the BAD file. They are pushed in the same format as the original in the data (INFILE) file. THis should allow correction and reimporting.
Specify the BADFILE in the same way as the INFILE or simply add BADFILE to the control file and it will create a new file with the same name as the INFILE except the extension is changed to .BAD. In our example the file would be called MYDATA.BAD.
A BADFILE is not created if no record produced errors. Running the same import twice causes the BADFILE to be overwritten.
Record may be pushed to the BADFILE for generating Oracle errors. The format of the record may be fine, but inserting the record caused a datatype or contraint violation and could not be inserted.
Tables with Records
The INSERT in the control file means the record will be inserted into an empty table. If the table is not empty, other keywords are used.
SQL*Loader can add rows to a table if the INSERT is changed to APPEND. The user needs the SELECT privilege to use the APPEND option.
REPLACE will delete each row in the table prior to the import. These deletes will fire and triggers on the table. After the rows are deleted and triggers fired the table is loaded as thought the INSERT option was in effect. The user will need DELETE privileges.
To avoid the delete triggers use the TRUNCATE option. Disable the table constraints before using this option. The user will need DELETE ANY TABLE privileges.