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

Please upload your files using the online turnin server. Note that file uploading will be disabled automatically after 7:40PM, so please turn in your work on time.


[Schema] Download the following scripts:
Run the hr8_main.sql script, which will create and populate a number of tables as shown in Figure 1.

Human Resource Schema
Figure 1. Human Resource (HR) Schema

You will use these tables to complete the exercises for this lab. If you want to remove the tables from your schema, simply run the script hr8_drop.sql.

[JDBC] (5pt) The job of an employee may change due to a number of reasons. For example, a sales representative may be promoted to sales manager, or an engineer may be transfered from one department to another. For this exercise, write a Java program to update the database when an employee changes to a new job. In particular, the program takes the following input:
With the input, your program should perform 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. For this exercise, you may use EmployeeJDBC-template.java as a template for your program.

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 machines. So please complete this exercise and show the instructor that your program works correctly before you proceed to the next section.

[Stored Procedures and Triggers] Implement the employee job change using stored procedures and triggers. In particular,
[Embedded SQL] (extra credit 5pt) Implement the employee job change using SQLJ. Note that like in the JDBC exercise, all the operations must be implemented on the application side, e.g. you cannot rely on the stored procedures and triggers you developed in the previous exercise.