Text search using PSP
Introduction
This document describes how to build a text search sample application using
PSP. We assume that the reader knows how to work with interMedia Text.
To develop and deploy PL/SQL Server Pages, you need the Oracle server at
version 8.1.6 or later, together with a PL/SQL web gateway.
What is PSP?
PL/SQL Server Pages (PSP) is the Oracle's PL/SQL scripting solution for
server-side web application development. It enables PL/SQL users to develop
web pages with dynamic content by embedding PL/SQL scripts in HTML pages,
which are executed when the pages are requested by Web clients.
PSP pages are HTML pages where PL/SQL scripts are embedded and are
separated from the HTML content. This separation of application logic
(embedded PL/SQL scripts) from the layout logic (HTML) makes the
development and maintenance of PSP pages easy.
Building a text search PSP
The usual way of writing web-based applications in PL/SQL is to use the
htp package to manipulate the construction of the HTML output. The
following code snipet shows how to displays the results of text search
query from PL/SQL.
...
for c1 in (select id, title, descr, ctxsys.score(1) scr
from docs
where ctxsys.contains(content,v_query,1) > 0
order by ctxsys.score(1) desc)
loop
htp.p('<table cellpadding=0 cellspacing=0 border=0>');
htp.p('<tr>');
htp.p('<td><b>Precision score</b>: ');
htp.p('<font color="#ff0000">'||c1.scr||'%</font>, <b>');
htp.p('</td>');
htp.p('<td>'||c1.id||'</td>');
htp.p('<td>'||c1.title||'</td>');
htp.p('<td>'||c1.descr||'</td>');
htp.p('</tr></table>');
end loop;
...
In the above sample code, the developer has to write the HTML output code
with the program variables as an output string.
In PSP the idea is to separate logic from layout.
It is a mix of
HTML (the static part of the page) and PL/SQL
(the dynamic content part). We can include any PL/SQL statment within
<% and %>.
For more information about PSP syntax please refer to the Oracle8i
Application Developer's Guide.
Let's create a simple table. From sqlplus ...
create table docs
(id number primary key,
text varchar2(2000));
Let's insert some values
insert into docs (id,text)
values(1,'This is a technical overview of the improvements found in interMedia Text, version 8.1.6. ');
insert into docs(id,text)
values(2,'This is intended for an audience familiar
with version 8.1.5. If you are new to interMedia Text, please start with Oracle8i interMedia Text 8.1.5 - Technical Overview ');
insert into docs(id,text)
values(3,'The improvements in structured document support include indexing and searching attribute text, nested within for sophisticated
queries, doctype-limited tag detection, dynamic add section for existing indexes, and a new AUTO sectioner which requires no
section pre-definition ');
insert into docs(id,text)
values(4,'Multi-lingual databases can now store documents of different languages in a single column, thanks to the new MULTI lexer');
insert into docs(id,text)
values(5,'Other interesting features include limited parallel indexing, more flexible stem and fuzzy operators, and in-memory document
services ');
Now we create a text index
create index docs_index on docs(text) indextype is ctxsys.context;
The file for a PSP must have the extension .psp (in this case is sample.psp).
<%@ plsql parameter="query" default="null" %>
<html>
<head>
<title>PSP Sample</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="sample">
<P>Search for <INPUT TYPE=TExt SIZE=50 MAXLENGTH=50 NAME="query">
<INPUT TYPE=SUBMIT VALUE="Search">
</FORM>
<% Else
%>
<h2> PSP Sample</h2>
<p>
<%!
color varchar2(6) := 'ffffff';
%>
<center>
<FORM METHOD=POST ACTION="sample">
<P>Search for
<INPUT TYPE=TExt SIZE=50 MAXLENGTH=30 NAME="query" VALUE=<%= query %>>
<INPUT TYPE=SUBMIT VALUE="Search">
</FORM>
<p>
<table border="1" cellpadding="4" cellspacing="0">
<tr bgcolor="#6699CC">
<th>Doc ID</th>
<th>Text</th>
</tr>
<%
for c in (select id, text
from docs where contains(text,query) >0
) loop
%>
<tr bgcolor="#<%= color %>">
<td> <%= c.id %> </td>
<td> <%= c.text %> </td>
</tr>
<%
if (color = 'ffffff') then
color := 'eeeeee';
else
color := 'ffffff';
end if;
end loop;
%>
</table></center>
<% End if;%>
<hr>
</body>
</html>
Installation
To install a PSP in the server we need to load it into the database as a
stored procedure. Each .psp file corresponds to one stored procedure. The
pages are compiled and loaded in one step using the loadpsp utility.
loadpsp -replace -user oalonso/oalonso sample.psp
One the PSP was compiled without errors, we can run it by retrieving an
URL through a web browser. For example:
http://mymachine.name/sample
You can also specify a different port number and DAD. For example, if we are
using the WebDB listener:
http://oalonso-sun.us.oracle.com:8080/oalonso2/sample
Last modified: Tue Aug 29 08:58:50 PDT
| 
Bookmark