Nethence Documentation Lab Webmail Your IP BBDock  


Those documents are obsolete, please use the Nethence Documentation instead.

HomeUnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigsPrivate

PL/SQL Server Pages (PSP) configuration with Oracle 9i
 
http://pbraun.nethence.com/oracle/pspinstall9i.html
http://pbraun.nethence.com/oracle/pspdev.html
 
 
Introduction
Note. Oracle HTTP Server (OHS) ships with:
- Oracle 9i - HTTP Server gets installed with the database software by default.
- Oracle 10g - HTTP Server can optionally be installed from the Companion CD.
- Oracle 11g - Not available with the DB anymore. Download Web Tier Utilities from the Oracle Fusion Middleware downloads.
Ref. http://www.orafaq.com/wiki/Oracle_HTTP_Server
 
We're going to use Oracle 9i in this guide.
 
 
Prerequesties
Make sure the database is started,
ps ax | grep dbname
 
Make sure the listener is tarted,
lsnrctl status
 
Oracle 9i with Oracle HTTP Server installed (runInstaller > installed products). Make sure Oracle HTTP Server is started,
cd /oracle/9.2/ora92/Apache/Apache/bin/
./apachectl start
cd ../
tail -F *
 
 
PSP configuration for SCOTT sample
Access the database HTTP server main page via a browser,
http://oracle9i:7777/
and configure a DAD (Database Access Descriptor),
Mod_plsql Configuration Menu
Gateway Database Access Descriptor Settings
Add Default (blank configuration)
DAD: scottdad
user name: SCOTT
password: TIGER
connect string: dbname (tnsnames)
authentication mode: basic
 
 
PSP with SCOTT sample
On Oracle 9i, install the SCOTT example,
. oraenv
sqlplus /nolog
connect / as sysdba
@/oracle/9.2/ora92/rdbms/admin/utlsampl.sql
 
Load this example,
cd ~/
wget http://www.oracle-base.com/articles/9i/dept_insert_req.psp
wget http://www.oracle-base.com/articles/9i/dept_insert_act.psp
loadpsp -replace \
  -user SCOTT/TIGER@dbname \
  dept_insert_req.psp \
  dept_insert_act.psp
and check,
http://oracle9i:7777/pls/SCOTTDAD/dept_insert_req
note. deptno needs to be a number
 
 
PSP configuration for HR sample
Access the database HTTP server main page via a browser,
http://oracle9i:7777/
and configure a DAD (Database Access Descriptor),
Mod_plsql Configuration Menu
Gateway Database Access Descriptor Settings
Add Default (blank configuration)
DAD: hrdad
user name: HR
password: HR
connect string: dbname (tnsnames)
authentication mode: basic
 
 
PSP with HR helloworld sample
Load this example,
wget http://www.oracle.com/technology/sample_code/products/ias/files/psp/HelloWorld/HelloWorld.zip
unzip HelloWorld.zip
cd HelloWorld/
loadpsp -replace -user HR/HR@dbname HelloWorld.psp
check,
http://oracle9i:7777/pls/hrdad/helloworld
 
 
PSP with HR simple sample
Load this example,
cat > simple.psp <<EOF9
<%@ page language="PL/SQL" >
<%@ page contentType="text/html" >
<%@ plsql procedure="show_employees" >
<%-- This example displays the last name and first name of every
employee in the hr.employees table. -->
<%!
CURSOR emp_cursor IS
SELECT last_name, first_name
FROM hr.employees
ORDER BY last_name;
>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html">
<title>List of Employees</title>
</head>
<body TEXT="#000000" BGCOLOR="#FFFFFF">
<h1>List of Employees</h1>
<table width="40%" border="1">
<tr>
<th align="left">Last Name</th>
<th align="left">First Name</th>
</tr>
<% FOR emp_record IN emp_cursor LOOP >
<tr>
<td> <%= emp_record.last_name > </td>
<td> <%= emp_record.first_name > </td>
</tr>
<% END LOOP; >
</table>
</body>
</html>
EOF9
loadpsp -replace -user HR/HR@dbname simple.psp
check,
grep procedure simple.psp
http://oracle9i:7777/pls/hrdad/show_employees
 
 
Troubbleshooting
Note. if you get this error,
ORA-28000: the account is locked
unlock the needed user,
alter user HR identified by HR account unlock;
 
 
References
http://www.oracle-base.com/articles/9i/PLSQLServerPages9i.php
http://www.oracle.com/technology/sample_code/products/ias/files/psp/index.html
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_psp.htm
http://lgl.isnetne.ch/d6ithemes/InstallationEtMaintenance/passerelledad.htm
 
 
Alternatives
http://www.total-knowledge.com/progs/mod_plsql/
http://owa.sourceforge.net/
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html
http://www.janus-software.com/fb_fyracle.html
http://www.metatranz.com/
 

Last update: Nov 13, 2010