ITEC 340 – Spring 2006

Homework 8

20 points

Write a stored procedure that will be called when the SUBMIT button of your camper application is clicked. The stored procedure will have a signature such as:

NewPurchase(vCamperID IN varchar2, vCategory IN varchar2, vAmount IN number, vMutationNo IN number,

vMessage OUT varchar2, vNewbalance OUT number)

A call from within an SQL program would look like:

NewSale(‘2’,’Candy’, 1.50, 878, message, newbalance)

Where:

2 is the ID of the camper associate with the sale

Candy is the category of the sale

1.50 is the amount of the sale

878 is the mutation counter of the row when it was first read (you may need something else here if you

Implemented your optimistic strategy with other than a mutation counter)

Message is the variable that will receive a message pack from the stored procedure. Messages would include:

- successful

- NSF (non sufficient funds)

- Mutation error

- unspecified error

newBalance is the remaining balance in the camper’s account after the sale

The stored procedure should do everything you had done on the client side:

Lock Resources

Check for Mutation

Insert a transaction

Update camper balance

Commit and Release locks

Do a Google on JDBC Oracle Stored Procedure Call to find the syntax for calling an Oracle stored procedure from your Java program. Replace your prior client code with code that uses your SPC. If you did not do the camper’s homework then write the stored procedure and a simple PL/SQL program that tests it and I will give you most of the credit.

Submit your revised java program to an RU08 folder on neelix by 5:00pm on Friday, April 28th.