CONTEXT Query Application, 4 of 4

Web Application Sample Code

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

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 => 'search_table_gen_index',
                     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

  -- This part of the script allows a person
  -- to enter data on an HTML form.
%>

  <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>