SQL*Loader Example

The following example shows how to use SQL*Loader to load mixed format documents from the operating system to a BLOB column. The example has two steps:

• create the table

• issue the SQL*Loader command that reads control file and loads data into table

  See Also: For a complete discussion on using SQL*Loader, see Oracle8i Utilities

Creating the Table

This example loads to a table articles_formatted created as follows:

CREATE TABLE articles_formatted ( 
  ARTICLE_ID   NUMBER PRIMARY KEY , 
  AUTHOR       VARCHAR2(30), 
  FORMAT       VARCHAR2(30), 
  PUB_DATE     DATE, 
  TITLE        VARCHAR2(256), 
  TEXT         BLOB 
);

The article_id column is the primary key, which is required in a Text table. Documents are loaded in the text column, which is of type BLOB.

Issuing the SQL*Loader Command

The following command starts the loader, which reads the control file LOADER1.DAT:

sqlldr userid=demo/demo control=loader1.dat log=loader.log

Example Control File: loader1.dat

This SQL*Loader control file defines the columns to be loaded and instructs the loader to load the data line by line from loader1.dat into the articles_formatted table. Each line in loader1.dat holds a comma separated list of fields to be loaded.

-- load file example
load data
INFILE 'loader2.dat'
INTO TABLE articles_formatted
APPEND
FIELDS TERMINATED BY ','
(article_id SEQUENCE (MAX,1),
 author CHAR(30),
 format,
 pub_date SYSDATE,
 title,
 ext_fname FILLER CHAR(80),
 text LOBFILE(ext_fname) TERMINATED BY EOF)

This control file instructs the loader to load data from loader2.dat to the articles_formatted table in the following way:

1. The ordinal position of the line describing the document fields in loader2.dat is written to the article_id column.

2. The first field on the line is written to author column.

3. The second field on the line is written to the format column.

4. The current date given by SYSDATE is written to the pub_date column.

5. The title of the document, which is the third field on the line, is written to the title column.

6. The name of each document to be loaded is read into the ext_fname temporary variable, and the actual document is loaded in the text BLOB column:

Example Data File: loader2.dat

This file contains the data to be loaded into each row of the table, articles_formatted.

Each line contains a comma separated list of the fields to be loaded in articles_formatted. The last field of every line names the file to be loaded in to the text column:

Ben Kanobi, plaintext,Kawasaki news article,../sample_docs/kawasaki.txt,
Joe Bloggs, plaintext,Java plug-in,../sample_docs/javaplugin.txt,
John Hancock, plaintext,Declaration of Independence,../sample_docs/indep.txt,
M. S. Developer, Word7,Newsletter example,../sample_docs/newsletter.doc,
M. S. Developer, Word7,Resume example,../sample_docs/resume.doc,
X. L. Developer, Excel7,Common example,../sample_docs/common.xls,
X. L. Developer, Excel7,Complex example,../sample_docs/solvsamp.xls,
Pow R. Point, Powerpoint7,Generic presentation,../sample_docs/generic.ppt,
Pow R. Point, Powerpoint7,Meeting presentation,../sample_docs/meeting.ppt,
Java Man, PDF,Java Beans paper,../sample_docs/j_bean.pdf,
Java Man, PDF,Java on the server paper,../sample_docs/j_svr.pdf,
Ora Webmaster, HTML,Oracle home page,../sample_docs/oramnu97.html,
Ora Webmaster, HTML,Oracle Company Overview,../sample_docs/oraoverview.html,
John Constable, GIF,Laurence J. Ellison : portrait,../sample_docs/larry.gif,
Alan Greenspan, GIF,Oracle revenues : Graph,../sample_docs/oragraph97.gif,
Giorgio Armani, GIF,Oracle Revenues : Trend,../sample_docs/oratrend.gif,