A
CONTEXT Query Application

This appendix describes how to build a simple web search application using the CONTEXT index type. The following topic is covered:

Web Query Application Overview

A common use of Oracle Text is to index HTML files on web sites and provide search capabilities to users. The sample application in this Appendix indexes a set of HTML files stored in the database and uses a web server connected to Oracle to provide the search service.

There are two versions of this application. One that uses PL/SQL Server Pages (PSP) and one that uses Java Server Pages (JSP). This appendix describes both.

You can view and download both the PSP and JSP application code at the Oracle Technology Network web site:

http://otn.oracle.com/products/text

The PSP Web Application

This application is based on PL/SQL server pages. Figure A-1 illustrates how the browser calls the PSP stored procedure on Oracle9i via a web server.

Figure A-1

Text description of ccapp012.gif follows

Text description of the illustration ccapp012.gif

Web Application Prerequisites

This application has the following requirements:

Your Oracle database (version 8.1.6 or higher) is up and running.
You have the Oracle PL/SQL gateway running
You have a web server such as Apache up and running and correctly configured to send requests to the Oracle9i server.

Building the Web Application

This section describes how to build the PSP web application.

Step 1 Create your Text Table

You must create a text table to store your html files. This example creates a table called search_table as follows:

create table search_table (tk numeric primary key, title varchar2(2000), text 
clob);

Step 2 Load HTML Documents into Table Using SQL*Loader

You must load the text table with the HTML files. This example uses the control file loader.ctl to load the files named in loader.dat. The SQL*Loader command is as follows:

% sqlldr userid=scott/tiger control=loader.ctl

Step 3 Create the CONTEXT index

Index the HTML files by creating a CONTEXT index on the text column as follows. Since we are indexing HTML, this example uses the NULL_FILTER preference type for no filtering and uses the HTML_SECTION_GROUP type:

create index idx_search_table on search_table(text)
  indextype is ctxsys.context parameters
  ('filter ctxsys.null_filter section group CTXSYS.HTML_SECTION_GROUP');

Step 4 Compile search_htmlservices Package in Oracle9i

The application must present selected documents to the user. To do so, Oracle must read the documents from the CLOB in search_table and output the result for viewing, This is done by calling procedures in the search_htmlservices package. The file search_htmlservices.sql must be compiled. You can do this at the SQL*Plus prompt:

SQL> @search_htmlservices.sql

Package created.

Step 5 Compile the search_html PSP page with loadpsp

The search page is invoked by calling search_html.psp from a browser. You compile search_html in Oracle9i with the loadpsp command-line program:

% loadpsp -replace -user scott/tiger search_html.psp
"search_html.psp": procedure "search_html" created.

See Also:

Oracle9i Application Developer's Guide - Fundamentals for more information about using PSP.

Step 6 Configure Your Web Server

You must configure your web server to accept client PSP requests as a URL. Your web server forwards these requests to the Oracle9i server and returns server output to the browser. Refer to Figure A-1.

You can use the Oracle WebDB 2.x web listener or Oracle iAS which includes the Apache web server. See your web server documentation for more information.

Step 7 Issue Query from Browser

You can access the query application from a browser using a URL. You configure the URL with your web server. An example URL might look like:

http://mymachine:7777/mypath/search_html

The application displays a query entry box in your browser and returns the query results as a list of HTML links. See Figure A-2, "Screen shot of Web Query Application".

Figure A-2 Screen shot of Web Query Application

Text description of the illustration webapp.gif

PSP Sample Code

This section lists the code used to build the example web application. It includes the following files:

loader.ctl
loader.dat
search_htmlservices.sql
search_html.psp

See Also:
http://otn.oracle.com/products/text/

loader.ctl

LOAD DATA 
        INFILE 'loader.dat'
        INTO TABLE search_table 
        REPLACE 
        FIELDS TERMINATED BY ';'
        (tk             INTEGER,
         title          CHAR,
         text_file      FILLER CHAR,
         text           LOBFILE(text_file) TERMINATED BY EOF)

loader.dat

1;   Sun finds glitch in new UltraSparc III chip;0-1003-200-5507959.html
2;   Redback announces loss, layoffs ;0-1004-200-5424681.html
3;   Cisco dumps acquired optical technology ;0-1004-200-5510096.html
4;   Microsoft to revise Passport privacy ;0-1005-200-5508903.html
5;   Tech stocks fall on earnings concerns;0-1007-200-5506210.html
6;   CNET.com - News - Investor - News - Story   ;0-9900-1028-5510548-0.html
7;   Chicago Tribune JUSTICES HEAR ARGUMENTS ;0_2669_SAV-0103290318_FF.html
8;   Massive new effort to combat African AIDS is planned  ;WEST04.html
9;   U.S. Had Biggest Growth in 1990s ;census_2000.html
10;   Congress Discusses Napster Issues ;congress_napster.html
11;   Washington And China Face Off in Spy Plane Drama ;crash_china_dc_35.html
12;   American Arrive To Study in Cuba ;cuba_us_medical_students_1.html
13;   Hubble Spots Most-Distant Supernova ;distant_supernova.html
14;   Survey: U.S. Has 90 Percent Chance of Recession;economy_forecast_dc_1.html
15;   House Votes To Repeal Estate Tax ;estate_tax.html
16;   EU Condemns Bush on Global Warming ;eu_global_warming.html
17;   Foot-and-Mouth Vaccinations on Hold ;foot_and_mouth.html
18;   Foot-and-Mouth Vaccinations on Hold ;foot_and_mouth_7.html
19;   Cancer Research Project Links Millions of PCs ;health_cancer_dc_1.html
20;   Company Says Early HIV Vaccine Data Are Promising ;hiv.html
21;   Yahoo! Sports: SOW - Maradona Faces New Paternity Suit ;maradona.html
22;   Israel, Palestinians Hold High-Level Talks ;mideast_leadall_dc.html
23;   Evidence Mounts Against Milosevic ;milosevic_slain_rivals.html
24;   Philippines Files Charges Against Estrada ;philippines_estrada_dc.html
25;   Power Woes Affecting Calif. Economy ;power_woes.html
26;   Dissidents Ask UN Rights Body to Condemn China ;rights_china_dc_2.html
27;   South Africa to Act on Basis HIV Causes AIDS ;safrica_aids_dc_1.html
28;   Shaggy Found Inspiration For Success In Jamaica ;shaggy_found.html
29;   Solar Flare Eruptions Likely ;solar_flare.html
30;   Plane Crash Kills Sudanese Officers ;sudan_plane_crash.html
31;   SOUNDSCAN REPORT: Recipe for An Aspiring Top Ten;urban_groove_1.html

search_htmlservices.sql

set define off

create or replace package search_htmlServices as

  procedure showHTMLDoc (p_id in numeric);

  procedure showDoc  (p_id in numeric, p_query in varchar2);


end;
/
show errors;

create or replace package body search_htmlServices as

  procedure showHTMLDoc (p_id in numeric) is
    v_clob_selected   CLOB;
    v_read_amount     integer;
    v_read_offset     integer;
    v_buffer          varchar2(32767);
   begin


     select text into v_clob_selected from search_table where tk = p_id;
     v_read_amount := 32767;
     v_read_offset := 1;
   begin
    loop
      dbms_lob.read(v_clob_selected,v_read_amount,v_read_offset,v_buffer);
      htp.print(v_buffer);
      v_read_offset := v_read_offset + v_read_amount;
      v_read_amount := 32767;
    end loop;
   exception
   when no_data_found then
     null;
   end;
 end showHTMLDoc;


procedure showDoc (p_id in numeric, p_query in varchar2) is

 v_clob_selected   CLOB;
 v_read_amount     integer;
 v_read_offset     integer;
 v_buffer          varchar2(32767);
 v_query           varchar(2000);
 v_cursor          integer;

 begin
   htp.p('<html><title>HTML version with highlighted terms</title>');
   htp.p('<body bgcolor="#ffffff">');
   htp.p('<b>HTML version with highlighted terms</b>');

   begin
     ctx_doc.markup (index_name => 'idx_search_table',
                     textkey    => p_id,
                     text_query => p_query,
                     restab     => v_clob_selected,
                     starttag   => '<i><font color=red>',
                     endtag     => '</font></i>');

     v_read_amount := 32767;
     v_read_offset := 1;
     begin
      loop
        dbms_lob.read(v_clob_selected,v_read_amount,v_read_offset,v_buffer);
        htp.print(v_buffer);
        v_read_offset := v_read_offset + v_read_amount;
        v_read_amount := 32767;
      end loop;
     exception
      when no_data_found then
         null;
     end;

     exception
      when others then
        null; --showHTMLdoc(p_id);
   end;
end showDoc;
end;
/
show errors


set define on

search_html.psp

<%@ plsql procedure="search_html" %>
<%@ plsql parameter="query" default="null" %>
<%! v_results numeric := 0; %>

<html>
<head>
  <title>search_html Search </title>
</head>
<body>

<%

If query is null Then
%>

  <center>
    <form method=post action="search_html">
     <b>Search for: </b>
     <input type=text name="query" size=30>&nbsp;
     <input type=submit value=Search>
  </center>
<hr>

<% 
  Else
%>

   <p>
   <%!
      color varchar2(6) := 'ffffff';
   %>

   <center>
     <form method=post action="search_html">
      <b>Search for:</b>
      <input type=text name="query" size=30 value="<%= query %>">
      <input type=submit value=Search>
     </form>
   </center>
   <hr>
   <p>

   <%
     -- select statement 
    for doc in (
                select /*+ FIRST_ROWS */ rowid, tk, title, score(1) scr
                from search_table
                where contains(text, query,1) >0
                order by score(1) desc
               ) 
         loop
           v_results := v_results + 1;
           if v_results = 1 then

   %>

             <center>
              <table border="0">
                <tr bgcolor="#6699CC">
                  <th>Score</th>
                  <th>Title</th>
                </tr>

  <%      end if; %>
          <tr bgcolor="#<%= color %>">
           <td> <%= doc.scr %>% </td>
           <td> <%= doc.title %>
           [<a href="search_htmlServices.showHTMLDoc?p_id=<%= doc.tk 
%>">HTML</a>]
           [<a href="search_htmlServices.showDoc?p_id=<%= doc.tk %>&p_query=<%= 
query %>">Highlight</a>]
           </td>
         </tr>

   <%
          if (color = 'ffffff') then
               color := 'eeeeee';
             else
               color := 'ffffff';
          end if;

     end loop; 
   %>

    </table>
   </center>

<% 
  end if;
%>
</body></html>

The JSP Web Application

This section describes the JSP web application.

Web Application Prerequisites

This application has the following requirements:

Your Oracle database (version 8.1.6 or higher) is up and running.
You have a web server such as Apache up and running and correctly configured to send requests to the Oracle9i server.

JSP Sample Code: search_html.jsp

<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %>
<jsp:useBean id="name" class="oracle.jsp.jml.JmlString" scope="request" >
<jsp:setProperty name="name" property="value" param="query" />
</jsp:useBean>


<%
  String connStr="jdbc:oracle:thin:@localhost:1521:betadev";

  java.util.Properties info = new java.util.Properties();

  Connection conn = null;
  ResultSet  rset = null;
  Statement  stmt = null;


    if (name.isEmpty()) { %>

      <html>
       <title>search1 Search</title>
       <body>
       <center>
         <form method=post>
         Search for:
         <input type=text name=query size=30>
         <input type=submit value="Search">
         </form>
       </center>
       <hr>
     </body>
     </html>

   <%
   }
   else {
   %>

    <html>
       <title>Search</title>
       <body>
       <center>
         <form method=post action="search_html.jsp">
         Search for:
         <input type=text name="query" value=<%= name.getValue() %> size=30>
         <input type=submit value="Search">
         </form>
       </center>

   <%
     try {

       DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver() );
       info.put ("user", "ctxdemo");
       info.put ("password","ctxdemo");
       conn = DriverManager.getConnection(connStr,info);

       stmt = conn.createStatement();
       String theQuery =   request.getParameter("query");

       String myQuery = "select /*+ FIRST_ROWS */ rowid, tk, title,  score(1) 
scr from search_table where contains(text, '"+theQuery+"',1 ) > 0 order by 
score(1) desc";
       rset = stmt.executeQuery(myQuery);

       String color = "ffffff";
       int myTk = 0;
       String myTitle = null;
       int myScore = 0;
       int items = 0;
       while (rset.next()) {
         myTk = (int)rset.getInt(2);
         myTitle = (String)rset.getString(3);
         myScore = (int)rset.getInt(4);
         items++;

         if (items == 1) {
    %>

            <center>
               <table border="0">
                  <tr bgcolor="#6699CC">
                    <th>Score</th>
                    <th>Title</th>
                  </tr>
    <%   } %> 

         <tr bgcolor="#<%= color %>">
           <td> <%= myScore %>%</td>
           <td> <%= myTitle %>
           </td>
         </tr>

   <%
         if (color.compareTo("ffffff") == 0)
               color = "eeeeee";
             else
               color = "ffffff";


       }
     } catch (SQLException e) {
     %>
        <b>Error: </b> <%= e %><p>
     <%
     } finally {
       if (conn != null) conn.close();
       if (stmt != null) stmt.close();
       if (rset != null) rset.close();
     }
     %>
     </table>
     </center>
     </body></html>
     <%
   }

%>

A CONTEXT Query Application

Web Query Application Overview

The PSP Web Application

Figure A-1

Web Application Prerequisites

Building the Web Application

Step 1 Create your Text Table

Step 2 Load HTML Documents into Table Using SQL*Loader

Step 3 Create the CONTEXT index

Step 4 Compile search_htmlservices Package in Oracle9i

Step 5 Compile the search_html PSP page with loadpsp

Step 6 Configure Your Web Server

Step 7 Issue Query from Browser

Figure A-2 Screen shot of Web Query Application

PSP Sample Code

loader.ctl

loader.dat

search_htmlservices.sql

search_html.psp

The JSP Web Application

Web Application Prerequisites

JSP Sample Code: search_html.jsp

A
CONTEXT Query Application