CS 422 - Database Programming I

Oracle 2 Day Developer Guide, Chapter 9: Developing a Simple Oracle Database Application

Objectives:

This lesson discusses triggers, program code that runs when specific events occur. Objectives important to this lesson:

  1. About the Application
  2. Creating the Schemas for the Application
  3. Granting Privileges to the Schemas
  4. Creating the Schema Objects and Loading the Data
  5. Creating the employees_pkg Package
  6. Creating the admin_pkg Package
Concepts:
About the Application

The text provides a longer chapter on creating an application in Oracle. It begins by defining the two groups of users the application will have, and the typical tasks each group will perform. The text also outlines the objects the application will use:

  • four tables
  • four editioning views
  • two triggers
  • two sequences that generate primary keys
  • two packages for the two user groups
Creating the Schemas for the Application

This application also uses five schemas, which can be imagined as users who have specific rights to part of the database. On page 9-4, the text explains how we should create the five schemas, but first we should delete them and their objects if they exist. Read the list of schemas on page 9-4, and substitute each in the command given at the top of page 9-5 in step 2. This will delete any existing schemas with the names we are using. Then create the five schemas, using the appropriate command from the two version shown in step 3.

Granting Privileges to the Schemas

Follow the instructions in the next section to grant privileges to each schema. Note that the privileges are all generic in this step. No objects have been created to allow privileges to be granted to them yet.

Creating the Schema Objects and Loading the Data

The text has detailed steps on the next several pages to create the tables, editioning views, triggers, and sequences. This is on pages 9-8 through 9-13. Loading data is covered on pages 9-13 through 9-15. You also create a foreign key constraint and grant several specific privileges on pages 9-15-and 9-16.

Creating the employees_pkg Package

The real work in the chapter begins on page 9-16, with the creation of the first package. To make the code easier to understand, the text has us create four synonyms for four tables. The tutorial continues with the creation of a package specification, which is a declaration of the package and the five procedures that are included in the package. At the bottom on page 9-17, the text shows us how to create the package body, which is the actual code for the package and its procedures.

This is followed by a tutorial on how the package works, followed by two commands on page 9-22 to grant execute privileges for this package to two schemas. The text then demonstrates how these users can use the package.

Creating the admin_pkg Package

The text goes through a similar sequence to create the second package, beginning on page 9-23. This continues through the end of the chapter on page 9-28.

 

Week 9 Assignment: 2 Day Developer Guide, Developing a Simple Oracle Database Application

  • Carry out the tutorial assignments in chapter 9 of the Oracle Developer pdf. Make some notes about each section in the space provided on Blackboard for this assignment. A few screen captures of your outputs would be nice.