loadpsp

The loadpsp utility loads a PSP (PL/SQL Server Page) file from the operating system into the database. The loaded PSP can then be accessed from a URL to display database content on a web page.

PL/SQL Server Pages (PSP) are Oracle's answer to the more common Java Server pages. It provides a convenient way for developers to display database content on web pages. By simply accessing a URL that points to a PSP, the result set of the data is displayed on the web page in the HTML format the developer has specified.

The PL/SQL Web Toolkit ships as part of the database in Oracle 9i. The file below (Parmlist.psp) will serve as the example in which the web page will display the parameter names and values from the v$parameter table.

This simple PSP accesses the name and value from the v$paramater table and formats them in very basic HTML. Additional logic is added to check for null parameter values, in which case null will be displayed.

< parmlist.psp

<%@ page language="PL/SQL" %>

<%@ plsql procedure="ParmListPSP" %>

<HTML>

<BODY>

This is a list of current database parameters:

<% FOR parm IN (select name, value from v$parameter

order by name)

LOOP

if parm.value is null then

parm.value := 'null';

end if;

<TR>

</TR>

<% END LOOP; %>

</BODY>

</HTML>

The PSP file can be loaded from the DOS or UNIX prompt using loadpsp:

C:\oracle9i\bin> loadpsp -replace -user scott/tiger Parmlist.psp

"Parmlist.psp": procedure "ParmListPSP" created.

The procedure is now queried from the data dictionary:

SQL> select text from user_source where name = 'PARMLISTPSP' order by line;

TEXT

----------------------------------------------------------------

PROCEDURE ParmListPSP AS

BEGIN NULL;

htp.prn('

');

htp.prn('

<HTML>

<BODY>

This is a list of current database parameters:

');

FOR parm IN (select name, value from v$parameter

order by name)

LOOP

if parm.value is null then

parm.value := 'null';

end if;

htp.prn('

<TR>

<TD>');

htp.prn( parm.name );

htp.prn('</TD>

<TD>');

htp.prn( parm.value );

htp.prn('</TD>

</TR>

');

END LOOP;

htp.prn('

</BODY>

</HTML>

');

END;

37 rows selected.

The code generated by loadpsp looks different than the source that was submitted. The PSP executable has added some extra code into the body of the PSP code. The added code is mainly calls to the htp package. This package generates the HTML tags for the web page.

Executing the PSP from SQL*Plus

The PSP can be executed from SQL*Plus, and using the owa_util package, the HTML output will be displayed. When accessing the owa_util package from SQL*Plus, package variables need to be initialized first, otherwise an error will occur on the first execution. Subsequent executions will work fine, just not the first one. To get around this limitation of the owa utilities, add the following code to the login.sql script located in the $ORACLE_HOME/sqlplus/admin directory.

DECLARE
name_arr OWA.VC_ARR;
value_arr OWA.VC_ARR;
BEGIN
OWA.INIT_CGI_ENV(0, NAME_ARR, VALUE_ARR);
END;
/

Now the PSP execution will be successful on the first attempt:

SQL> set serveroutput on

SQL> execute parmlistpsp;

PL/SQL procedure successfully completed.

SQL> execute owa_util.showpage;

<HTML>

<BODY>

This is a

list of current database parameters:

<TR>

<TD>O7_DICTIONARY_ACCESSIBILITY</TD>

<TD>FALSE</TD>

</TR>

<TR>

<TD>active_instance_count</TD>

</TR>

<TR>

<TD>aq_tm_processes</TD>

</TR>7

<TR>

<TD>archive_lag_target</TD>

...

Notice from the output above that the text is pure HTML. Oracle executed our query and formatted the HTML including the data returned from the query. The next step is to access this HTML from a web browser.

Executing the PSP through the Browser

The Oracle9i database includes Apache and JServ as part of its standard installation. The default port is 7778, and the URL to access the main page on the local host machine is:

http://localhost:7778/

NOTE: On some installations no port is required http://localhost.

The localhost in the URL above should be replaced with the server name or the IP address of the remote box containing the database to access the web server on another Oracle machine. The Oracle HTTP Server web page is displayed with many options.

Choose the mod_plsql configuration menu or access the URL directly: http://localhost:7778/pls/simpledad/admin_/gateway.htm?schema=sample

From the Gateway Configuration Menu, the Gateway Database Access Descriptor Settings is the desired option in order to load the page displayed below.

The Data Access Descriptor (DAD) information is specified on this screen. A DAD simply specifies a username and password to use as the connection to the database. This username and password will be used for anyone that specifies this DAD in their URL specification in the browser. It also supports other optional parameters.

From this screen, a DAD named “test” was created that simply pointed to the local database. Now the PSP is loaded in the database and ready to be accessed.

Notice the parts of the URL (http://localhost:7778/pls/test/ParmListPSP) in Figure 8.5:

· The base URL - http://localhost:7778

· PLS - Fixed text indicating the desired service – PL/SQL.

· DAD – The data access descriptor that was created, in this case “test”.

· PSP Name – The name of the PSP that was loaded into the database.

Anyone can enter the above URL and the results of the PSP will be displayed in a browser. This simple PSP example queried the database and displayed the instance parameters from the v$parameter view in the web page. The sky is the limit for the developer, since any data within Oracle can be easily displayed in a web browser.

Loadpsp Command Line Options

The loadpsp executable contains very few command line options. The replace option allows the existing procedures in the database to be overlaid regardless of the compilation success. This serves the same purpose as CREATE OR REPLACE when creating PL/SQL objects.

PSPs make it very easy for developers to build Web applications utilizing Oracle built-in technology. PSPs offer the capability of specifying input parameters in HTML and including them in the queries sent to the database. Given the popularity of web-based applications, Oracle developers could utilize PSPs as the foundation for any web solution.