Lab 2. PL/SQL and JDBC
CS422, Summer 2006

Please upload your solutions to CSNS. Note that file uploading will be disabled automatically after 7:50PM, so please turn in your work on time.


[Schema] Create the following schema in your Oracle database, and populate it with enough data to test the programs and procedures you develop in the rest of the lab:

products( product_id, category, description, price, quantity )
customers( customer_id, first_name, last_name, address )
orders( order_id, customer_id, date_ordered, date_shipped )
order_details( order_id, product_id, quantity )

[JDBC] (5pt) For this exercise, write a Java program for canceling an order. The program takes an order id as input and performs the following operations:

All the operations should be performed as part of a transaction, and if any error occurs during the transaction, all operations should be properly rolled back, and the program outputs an error message.

One of the purposes of this exercise is to make sure that you can compile and run Java programs using Oracle JDBC driver on the classroom computers. So please complete this exercise before you proceed to the next section.

[Stored Procedures] (5pt) Implement the order cancellation using a PL/SQL stored procedure (a function, actually). The function takes an order id as parameter, and performs the order cancellation as described in the previous exercise. The function return -1 if the order is already shipped, or 0 if the order is canceled successfully.

[Embedded SQL] (extra credit 5pt) Implement the order cancellation using SQLJ. Like in the JDBC exercise, all the operations must be implemented on the application side, i.e. you cannot rely on the stored procedure you developed in the previous exercise.