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