Oracle Text Reference
Release 9.0.1

Part Number A90121-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to beginning of chapter Go to next page

Loading Examples, 3 of 4

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:

Creating the Table

This example loads to a table articles_formatted created as follows:

CREATE TABLE articles_formatted ( 
  AUTHOR       VARCHAR2(30), 
  FORMAT       VARCHAR2(30), 
  TITLE        VARCHAR2(256), 
  TEXT         BLOB 

The article_id column is the primary key. 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 loader2.dat into the articles_formatted table. Each line in loader2.dat holds a comma separated list of fields to be loaded.

-- load file example
load data
INFILE 'loader2.dat'
INTO TABLE articles_formatted
(article_id SEQUENCE (MAX,1),
 author CHAR(30),
 pub_date SYSDATE,
 ext_fname FILLER CHAR(80),

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,

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index