Building the PSP Web Application

To create PSP web application:

Create your text tables.

You must create text tables with the CREATE TABLE command to store your HTML files. These examples create the output_table, gist_table, and theme_table tables:

CREATE TABLE output_table (query_id NUMBER, document CLOB); CREATE TABLE gist_table (query_id NUMBER, pov VARCHAR2(80), gist CLOB); CREATE TABLE theme_table (query_id NUMBER, theme VARCHAR2(2000), weight NUMBER);

Load HTML documents into the table by using SQL*Loader.

You must load the text tables with the HTML files. This example uses the loader.ctl control file to load the files named in loader.dat. The SQL*Loader statement is as follows:
```
% sqlldr userid=scott/password control=loader.ctl
```
Create the CONTEXT index.

Index the HTML files by creating a CONTEXT index on the text column, as shown here. Because you are indexing HTML, this example uses the NULL_FILTER preference type for no filtering, and it uses the HTML_SECTION_GROUP type, as follows:
```
create index idx_search_table on search_table(text) indextype is ctxsys.context parameters ('filter ctxsys.null_filter section group CTXSYS.HTML_SECTION_GROUP');
```
Compile the search_htmlservices package in Oracle Database.

The application must present selected documents to the user. To do so, Oracle Database must read the documents from the character large object (CLOB) in search_table and output the result for viewing. To do that, call procedures in the search_htmlservices package. Compile the file search_htmlservices.sql file at the SQL*Plus prompt as follows:
```
SQL> @search_htmlservices.sql Package created.
```
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 Oracle Database with the loadpsp command-line program as follows:
```
% loadpsp -replace -user scott/password search_html.psp
```
The output appears as:
```
"search_html.psp": procedure "search_html" created.
```

See Also: Oracle Database 11g Release 2 (11.2) of Oracle Database Development Guide for more information about using PSP

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 Oracle Database and returns server output to the browser. See Figure A-3.

You can use the Oracle WebDB web listener or Oracle Application Server, which includes the Apache web server.
Enter the query from a browser.

You can access the query application from a browser by using a URL. You configure the URL with your web server. An example URL might look like the following:
```
http://server.example.com: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, as shown in Figure A-1 and Figure A-2.