|Oracle Text Reference
Part Number A96518-01
This appendix provides examples of how to load text into a text column. It also describes the structure of
ctxload import files:
A simple way to populate a text table is to create a table with two columns,
TABLE and then use the
INSERT statement to load the data. This example makes the
id column the primary key, which is optional. The
text column is
To populate the
text column, use the
INSERT statement as follows:
insert into docs values(1, 'this is the text of the first document'); insert into docs values(12, 'this is the text of the second document');
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:
For a complete discussion on using SQL*Loader, see Oracle9i Database Utilities
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 );
article_id column is the primary key. Documents are loaded in the
text column, which is of type
The following command starts the loader, which reads the control file
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 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:
loader2.datis written to the
SYSDATEis written to the
ext_fnametemporary variable, and the actual document is loaded in the
This file contains the data to be loaded into each row of the table,
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,
ctxloadThesaurus Import File
The import file must use the following format for entries in the thesaurus:
phrase BT broader_term NT narrower_term1 NT narrower_term2 . . . NT narrower_termN BTG broader_term NTG narrower_term1 NTG narrower_term2 . . . NTG narrower_termN BTP broader_term NTP narrower_term1 NTP narrower_term2 . . . NTP narrower_termN BTI broader_term NTI narrower_term1 NTI narrower_term2 . . . NTI narrower_termN SYN synonym1 SYN synonym2 . . . SYN synonymN USE synonym1 or SEE synonym1 or PT synonym1 RT related_term1 RT related_term2 . . . RT related_termN SN text language_key: term
is a word or phrase that is defined as having synonyms, broader terms, narrower terms, and/or related terms.
In compliance with ISO-2788 standards, a TT marker can be placed before a phrase to indicate that the phrase is the top term in a hierarchy; however, the TT marker is not required. In fact, ctxload ignores TT markers during import.
A top term is identified as any phrase that does not have a broader term (BT, BTG, BTP, or BTI).
The thesaurus query operators (
are the markers that indicate broader_termN is a broader (generic|partitive|instance) term for phrase.
broader_termN is a word or phrase that conceptually provides a more general description or category for phrase. For example, the word elephant could have a broader term of land mammal.
are the markers that indicate narrower_termN is a narrower (generic|partitive|instance) term for phrase.
If phrase does not have a broader (generic|partitive|instance) term, but has one or more narrower (generic|partitive|instance) terms, phrase is created as a top term in the respective hierarchy (in an Oracle Text thesaurus, the BT/NT, BTG/NTG, BTP/NTP, and BTI/NTI hierarchies are separate structures).
narrower_termN is a word or phrase that conceptually provides a more specific description for phrase. For example, the word elephant could have a narrower terms of indian elephant and african elephant.
is a marker that indicates phrase and synonymN are synonyms within a synonym ring.
synonymN is a word or phrase that has the same meaning for phrase. For example, the word dog could have a synonym of canine.
Synonym rings are not defined explicitly in Oracle Text thesauri. They are created by the transitive nature of synonyms.
are markers that indicate phrase and synonym1 are synonyms within a synonym ring (similar to SYN).
The markers USE, SEE or PT also indicate synonym1 is the preferred term for the synonym ring. Any of these markers can be used to define the preferred term for a synonym ring.
is the marker that indicates related_termN is a related term for phrase.
related_termN is a word or phrase that has a meaning related to, but not necessarily synonymous with phrase. For example, the word dog could have a related term of wolf.
Related terms are not transitive. If a phrase has two or more related terms, the terms are related only to the parent phrase and not to each other.
is the marker that indicates the following text is a scope note (i.e. comment) for the preceding entry.
term is the translation of phrase into the language specified by language_key.
In compliance with thesauri standards, the load file supports formatting hierarchies (BT/NT, BTG/NTG, BTP, NTP, BTI/NTI) by indenting the terms under the top term and using NT (or NTG, NTP, NTI) markers that include the level for the term:
phrase NT1 narrower_term1 NT2 narrower_term1.1 NT2 narrower_term1.2 NT3 narrower_term1.2.1 NT3 narrower_term1.2.2 NT1 narrower_term2 . . . NT1 narrower_termN
Using this method, the entire branch for a top term can be represented hierarchically in the load file.
The following conditions apply to the structure of the entries in the import file:
cranes (birds), cranes (lifting equipment)
Example of Incorrect SN usage:
VIEW CAMERAS SN Cameras with through-the lens focusing and a range of movements of the lens plane relative to the film plane
Example of Correct SN usage:
The following conditions apply to the relationships defined for the entries in the import file:
Example of incorrect RT usage:
Example of correct RT usage:
This section provides three examples of correctly formatted thesaurus import files.
cat SYN feline NT domestic cat NT wild cat BT mammal mammal BT animal domestic cat NT Persian cat NT Siamese cat wild cat NT tiger tiger NT Bengal tiger dog BT mammal NT domestic dog NT wild dog SYN canine domestic dog NT German Shepard wild dog NT Dingo
animal NT1 mammal NT2 cat NT3 domestic cat NT4 Persian cat NT4 Siamese cat NT3 wild cat NT4 tiger NT5 Bengal tiger NT2 dog NT3 domestic dog NT4 German Shepard NT3 wild dog NT4 Dingo cat SYN feline dog SYN canine