Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

PL/SQL Server Pages (PSP)

PL/SQL Server Pages (PSP) are an extension of the PL/SQL Web Toolkit, which allows PL/SQL to be used as a scripting language within HTML files, like ASP, JSP & PHP. The files containing the PL/SQL scripting are loaded and published from within the Oracle 8i or Oracle 9i database server. This article assumes the default installation of the HTTP server has taken place and the HTTP server is running.

Database Access Descriptor Configuration (DAD)

First we must set up a Database Access Descriptor (DAD) to allow the webserver to connect to the database when a specific URL is requested.

The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.

Load PSP Files

Next we load the PSP files that will actually produce the web pages. These should be loaded into, or accessable from, the SCOTT schema since this is where the DAD points to.

Since this code contains fragments of HTML it may look rather odd when displayed by the browser. Either view the source or save the source to a file to read the code.

Once the files are saved to the filesystem they can be loaded to the server from the command line using the loadpsp utility.

C:\>loadpsp -replace -user scott/tiger@W2K1 dept_insert_req.psp dept_insert_act.psp
"dept_insert_req.psp": procedure "dept_insert_req" created.
"dept_insert_act.psp": procedure "dept_insert_act" created.


During the load process each file is converted to a stored procedure which use the PL/SQL Web Toolkit routines. The name of the stored procedure is determined by a page directive.

<%@ plsql procedure="dept_insert_act" %>

Any parameters passed to the PSP file must be declared before use.

<%@ plsql parameter="p_deptno" type="NUMBER" default="NULL" %>

Each page is stored as a separate procedure, rather than a packaged procedure.

Test It

Once the DAD is configured and the PSP pages are loaded into the SCOTT schema we can test them by opening a browser and requesting the following address, where "yourServer" is the name of the Oracle server.


Enter some values into the form and press the submit button. The resulting page should show a successful completion message or an error message. In addition the contents of the DEPT table are displayed as an HTML table.

For more informationn see:

Hope this helps. Regards Tim...

Back to the Top.