C
SQL*Plus Sample Code

This appendix describes the sample SQL*Plus scripts provided by ConText. The scripts illustrate how to use SQL*Plus to build simple queries and generate linguistic output using ConText linguistics.

The scripts are divided into two functional areas: CTXPLUS (performing ad-hoc queries) and CTXLING (generating linguistic output).

The following topics are covered in this chapter:

Setting Up the ConText Sample Applications

Before you can use either CTXPLUS or CTXLING, as well as the Oracle Forms sample application distributed with the ConText Workbench, you must create the required demonstration objects by preforming the following setup tasks.

Note:

The files required for performing the setup tasks are located in the demo directory for ConText. For example, in a UNIX environment, the files are named demo.dmp and demoinst.sql and are located in $ORACLE_HOME/ctx/demo/install.

For the exact location and name of the setup files, see the Oracle8 installation documentation specific to your operating system.

Import the export file into the predefined ConText user CTXDEMO's schema.

For example:

IMP ctxdemo/ctxdemo FILE=demo.dmp TABLES=articles

Importing the export file creates an ARTICLES table for CTXDEMO and populates ARTICLES.TEXT with the text of the articles used in the samples.

Start one or more ConText Server with the DDL (D) and Linguistics (L) personalities.
Log in to SQL*Plus as the demo user and run the install script.

For example:

@demoinst

The script creates the policies, preferences, views, and results tables used by the samples and creates a text index for the ARTICLES table. It also creates the tables required for highlighting and CTX_LING.

Note:

If you want to use CTXLING, you must also run the genling.sql script, located in the ctxling subdirectory in the demo directory.

For more information, see "Using CTXLING" in this chapter.

Overview of CTXPLUS

The CTXPLUS sample code consists of the following SQL scripts:

Script Description

query1.sql Performs a one-step query using the input query expression and returns a hitlist, sorted by score, to the standard output.

query2.sql Performs a two-step query using the input query expression and returns a hitlist, sorted by score, to the standard output.

queryc.sql Performs an in-memory query using the input query expression and returns an unsorted hitlist to standard output

querys.sql Performs an in-memory query using the input query expression and returns a hitlist, sorted by score, to the standard output.

storeqry.sql Performs a query and stores the results as a system SQE. The results of the SQE can then be used in a query (one-step, two-step, or in-memory).

showsqe.sql Returns a list of all the system SQEs that have been stored for a policy. Note that this script is not currently implemented.

view.sql Selects a document based on the input textkey and returns the text of the document to the standard output.

Script	Description
query1.sql	Performs a one-step query using the input query expression and returns a hitlist, sorted by score, to the standard output.
query2.sql	Performs a two-step query using the input query expression and returns a hitlist, sorted by score, to the standard output.
queryc.sql	Performs an in-memory query using the input query expression and returns an unsorted hitlist to standard output
querys.sql	Performs an in-memory query using the input query expression and returns a hitlist, sorted by score, to the standard output.
storeqry.sql	Performs a query and stores the results as a system SQE. The results of the SQE can then be used in a query (one-step, two-step, or in-memory).
showsqe.sql	Returns a list of all the system SQEs that have been stored for a policy. Note that this script is not currently implemented.
view.sql	Selects a document based on the input textkey and returns the text of the document to the standard output.

See Also:

For more information about the location of the scripts, see the Oracle8 installation documentation specific to you operating system.

Concepts

The ConText concepts illustrated in this sample code are:

query expression syntax
one-step queries
two-step queries
two-step queries (sorted and unsorted)
stored query expressions

Using CTXPLUS

To use the CTXPLUS sample SQL scripts:

Ensure that one or more ConText servers are running with the Query (Q) personality.
Log in to SQL*Plus as the owner of the demonstration objects (usually CTXDEMO).
To initiate a query, run one of the query scripts (query1, query2, queryc, or querys). The scripts prompt you to enter a query expression.

For example:

@query1
Enter value for query_terms: coffee|tea

The script then returns a hitlist of the documents in the ARTICLES table that satisfy the query expression you enter. The hitlist consists of a score, ID, author, and title.

To view an article, run the view.sql script and give it an article ID. The article ID is the value displayed in the ID column in the hitlist generated by the query scripts.

For example:

@view 14

The script then returns the text for the document with the article ID you specified.

To create a stored query expression (SQE), run the storeqry.sql script. The scripts prompt you to enter a name for the SQE and a query expression.

For example:

@storeqry
Enter query name: test_sqe
Enter value for query_terms: coffee|tea

Note:

The script does not return the results of the query to the standard output.

To view the SQEs for the demonstration user, use the CTX_USER_SQES view.

For example:

select pol_name, query_name, query_name
from ctx_user_sqes;

CTXPLUS Examples

The following examples execute the query1.sql, query2.sql, and querys.sql scripts using the query terms California and politics and various logical operators (OR, ACCUMULATE, and AND).

These examples illustrate how one-step, two-step, and (sorted) in-memory queries produce the same results and how the operators in a query expression affect the rows and scores returned by a query:

Single Term Queries

@query2
Enter value for query_terms: California

SCR  ID AUTHOR          TITLE
---- -- --------------- ----------------------------
100  17 Nolo Richards   REVIEW & OUTLOOK (Editorial):
                        California Smashup
50   18 Nolo Richards   State Farm and California
30   25 David Shribman  In the Wilderness: Democrats'
                        Troubles In Winning
20   49 Nolo Richards   California High Court Is
                        Asked to Lift Block Of In
10   16 Heidi Waleson   LEISURE & ARTS: Cynthia
                        Phelps: Violist in Vogue 
@query1
Enter value for query_terms: politics

SCR  ID AUTHOR          TITLE
---- -- --------------- ----------------------------
20   25 David Shribman  In the Wilderness: Democrats'
                        Troubles In Winning
10   13 Frederick C. Kl LEISURE & ARTS -- Sports:
                        Mediocrity's the Word Ar

Multiple Term Query Using OR

@querys

Enter value for query_terms: politics|California
SCR  ID AUTHOR          TITLE
---- -- --------------- ----------------------------
100  17 Nolo Richards   REVIEW & OUTLOOK (Editorial):
                        California Smashup
50   18 Nolo Richards   State Farm and California
30   25 David Shribman  In the Wilderness: Democrats'
                        Troubles In Winning
20   49 Nolo Richards   California High Court Is Asked
                        to Lift Block Of In
10   13 Frederick C.    Kl LEISURE & ARTS -- Sports:
                        Mediocrity's the Word Ar
10   16 Heidi Waleson   LEISURE & ARTS: Cynthia
                        Phelps: Violist in Vogue

Multiple Term Query Using ACCUMULATE

@query1

Enter value for query_terms: politics,California
SCR  ID AUTHOR          TITLE
---- -- --------------- ----------------------------
100  17 Nolo Richards   REVIEW & OUTLOOK (Editorial):
                        California Smashup
50   18 Nolo Richards   State Farm and California
50   25 David Shribman  In the Wilderness: Democrats'
                        Troubles In Winning
20   49 Nolo Richards   California High Court Is Asked
                        to Lift Block Of In
10   13 Frederick C.    Kl LEISURE & ARTS -- Sports:
                        Mediocrity's the Word Ar
10   16 Heidi Waleson   LEISURE & ARTS: Cynthia
                       Phelps: Violist in Vogue

Multiple Term Queries Using AND

@query2
Enter value for query_terms: politics&California
SCR  ID AUTHOR          TITLE
---- -- --------------- ----------------------------
20   25 David Shribman  In the Wilderness: Democrats'
                        Troubles In Winning

Overview of CTXLING

The CTXLING demo is a set of simple, related SQL*Plus scripts. Two of the scripts automate and track linguistic extraction on the demonstration documents. The remaining scripts can be used to query this linguistic output.

The CTXLING sample code consists of the following SQL scripts:

Script Description

genling.sql

Requests theme and Gist generation for each of the documents in the ARTICLES table.

status.sql

Shows the status of the theme and Gist generation initiated by genling.sql.

gist.sql

Displays the Gists for a document.

themes.sql

Displays the themes for a document.

similar.sql

Displays documents with similar themes for the input document

Script	Description
genling.sql	Requests theme and Gist generation for each of the documents in the ARTICLES table.
status.sql	Shows the status of the theme and Gist generation initiated by genling.sql.
gist.sql	Displays the Gists for a document.
themes.sql	Displays the themes for a document.
similar.sql	Displays documents with similar themes for the input document

See Also:

For more information about the location of the scripts, see the Oracle8 installation documentation specifc to your operating system.

Concepts

The ConText concepts illustrated in this sample code are:

generating linguistic output using the Linguistic Services
document theme viewing
document Gist viewing

Using CTXLING

To use the CTXLING sample SQL scripts:

Ensure that one or more ConText servers with the Linguistic (L) personality are running.
Log in to SQL*Plus as the owner of the demonstration objects (usually CTXDEMO).
To generate linguistic output, run genling.sql:

@genling
Clearing theme table...
Clearing article table...
Initializing ling_tracking table
Creating ling. callback function LING_COMP_CALLBACK...
Submitting all articles for linguistic extraction...
All articles submitted.

The script generates Gist and theme information for each document in the ARTICLES table and stores the information in the linguistic output tables created by demoinst.sql.

The linguistic generation runs in the background. While this is happening, you can use status.sql to check on the progress:

For example:

@status
Linguistic Requests left: 36
Request Errors....

The extraction is complete when there are 0 Linguistic Requests left.

To view the themes or Gists of an article, run the appropriate script and give it an article ID.

For example:

@gist 40
Points of View
01 GENERIC ..
15 production
16 purchases
which point of view gist to print: 15

The script then returns the themes or Gists for the document with the article ID you specified.

To select articles with the same themes as an article, run the similar.sql script and give it an article ID.

For example:

@similar 14

The script then returns a list of the articles with the same themes as the article ID you specified.

CTXLING Examples

The following examples illustrate using themes.sql, gist.sql, and similar.sql to view the linguistic output generated by genling.sql.

Theme Viewing

@themes 40 
Commodities: Coffee Futures Prices Decline on News That
U.S. Might Not Participate in New International Pact
by John Valentine 

T#  THEME                                   WEIGHT
--- --------------------------------------- ------
01  United States                           11
02  commerce and trade                      10
03  coffee                                  10 
...

Gist Viewing

@gist 40

Points of View 

01 GENERIC ...
15 production
16 purchases

Which point of view gist to print: 15

Commodities: Coffee Futures Prices Decline on News That
U.S. Might Not Participate in New International Pact
by John Valentine 

Consuming and producing nations appear to be poles apart
in their positions. Producing countries proposed a quota
that would incorporate the sales of
...

Theme Comparison Viewing

@similar 40

Commodities: Coffee Futures Prices Decline on News That
U.S. Might Not Participate in New International Pact
by John Valentine

Article Themes

01 United States
02 commerce and trade
03 coffee
..
14 production
15 purchases

Which theme to query: 15

Other articles with this theme

ID  WT  AUTHOR          TITLE
--- --- --------------- --------------------------------
1   8   William Power   OTC Focus: Composite Index Falls
33  7   Alex Kaufmann   Your Money Matters: How to Take
5   7   George Anders   Shades of U.S. Steel: J.P.
30  6   Michael Siconol Mutual Funds: ...And Find Out if
47  6   Nolo Richards   Ponce Federal Bank Is in Talks
45  5   Nolo Richards   Farley Wins Round In His Bid to
35  2   Alix M.         Freedma Supermarkets Push Private-Label

C SQL*Plus Sample Code

Setting Up the ConText Sample Applications

Overview of CTXPLUS

Concepts

Using CTXPLUS

CTXPLUS Examples

Single Term Queries

Multiple Term Query Using OR

Multiple Term Query Using ACCUMULATE

Multiple Term Queries Using AND

Overview of CTXLING

Concepts

Using CTXLING

CTXLING Examples

Theme Viewing

Gist Viewing

Theme Comparison Viewing

C
SQL*Plus Sample Code