The Big Shop
PSP Sample Application
Table of Contents
Suggested Additional Exercises
Introduction
The purpose of this sample application is to give an introduction to how to use PL/SQL Server Pages. It is very loosely based around an online store where customers can
- register
- login
- view and track past and present orders
- place new orders
- amend their details
It should be noted that to facilitate the learning process, the site has been made as simple as possible. Amongst other things, there are no transactions for buying goods, the login only verifies that the customer id exists, so a pretty good guess would enable someone to login, and once an order is placed, nothing happens to it other than a record is written to the orders table.
Installation
As already mentioned in the chapter PSPs require Oracle 8i release 2 (8.1.6) and a PL/SQL gateway. This installation assumes that these have been installed and configured and that you have
- a username and password that can be used in the loadpsp utility
- a url, a connection to a database via a PL/SQL gateway from iAS , that can be used to access the loaded PSPs
Step 1 - Get the files
The files required should be downloaded and extracted into a local directory on your machine.
Step 2 - Create the database
Connect to the database using sql*plus and the username and password which will hold the application.
Run the cr_db.sql script as you would any other database script by typing @"<script_home>cr_db.sql" at the SQL prompt
Check the cr_db.log file for errors by locating this file in the <script_home> directory
Step 3 - Load the sample data
Connect to the database using sql*plus and the username and password for the schema, that will hold the application.
Check the ld_data.err for errors
Step 4 - Load the PSPs
Run the loadpsp utility for each of the provided PSPs from the command line, making sure that you are in the directory which contains the PSP files.
As follows:
- loadpsp -replace -user username/password[@db] error.psp commit_cust.psp
- loadpsp -replace -user username/password[@db] error.psp commit_order.psp
- loadpsp -replace -user username/password[@db] error.psp cust_dets.psp
- loadpsp -replace -user username/password[@db] error.psp home.psp
- loadpsp -replace -user username/password[@db] error.psp order_items.psp
- loadpsp -replace -user username/password[@db] error.psp orders.psp
- loadpsp -replace -user username/password[@db] error.psp place_order.psp
- loadpsp -replace -user username/password[@db] error.psp track.psp
- loadpsp -replace -user username/password[@db] error.psp welcome.psp
After each, check the screen to ensure that the procedures have been successfully created.
Step 5 - Try to access the site
Use the url http://host/dad/pls/home, the variables -host and -dad will be determined individually, depending on your system setup. There is information on setting up a DAD (Database Access Descriptor) in Chapter 8 of the book.
If this is successful, you will see the following screen:
Suggested Additional Exercises
There are no answers for the following exercises, they are ideas as to what you could do next. I must stress that they are only suggestions as I am sure that you will find a better way of solving them.
Improve Error Handling
Currently all errors are reported through the single error.psp. This page does nothing more than displays the text for the Oracle error message. This may be satisfactory for some scenarios but more likely than not this will prove to be unusable.
Try to improve the error handling for the application.
One way to achieve this could be to trap the errors and display a specific message based on the error raised, another way could be to write the error to a table and then use the error.psp to read from this table.
Enable the Ordering of Multiple Items
The user is told that there is a problem with stock levels and shipping and so they can only order one item. This has been fixed and the page now has to allow multiple ordering of multiple items.
One option for this may be to have an “add to shopping basket” link with a running total being displayed on the ordering screen. Then an additional 'buy' button could be implemented to indicate the user wants to close their order.