Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 8 Instructions

without comments

Lab #8: Oracle Assignment


The lab is designed to teach you how to write stored procedures that insert data into several tables while maintaining transaction control across the set of insert statements. You will write an insert_contact procedure that should achieve these objectives:

  • Learn how to write stored functions and procedures inside packages.
  • Learn how to write overloaded functions and procedures.
  • Learn how to call and test overloaded functions and procedures.

Business Scenario

Application programming interfaces (API) can be composed of separate stored functions and procedures but they are best organized into packages. A package is defined by two components: one is a package specification and the other is a package body. A package specification publishes the signatures of functions and procedures. A package body may implement functions and procedures defined by the package specification.

A package specifications may hold more than just function and procedure definitions. A package specification may also hold PL/SQL record types and variables. You can access these types and variables inside and outside of the package where they are defined.

A package body may hold types, variables, functions, and procedures. Any types, variables, functions, and procedures defined in the package body are local to the package body you can only access them from other functions and procedures inside the package body. The package body must provide implementations for all functions and procedures defined in the package specification.

Overloading functions and procedures is the true advantage of packages. Overloaded functions and procedures have parameter lists that differ. The parameter lists must have a different number of parameters in the parameter list or a different order of the data types of the parameter lists.

Overloading lets us create functions and procedures that serve different purposes. For example, you can create a procedure that accepts values from an end-user application or from a batch programming application.

Batch programs are run by administrators. The administrator would know the system_user_id that should be used as the created_by and last_updated_by audit column value. Knowing the correct system_user_id value, the administrator call the batch version of the API. The batch version accepts a system_user_id value whereas the interactive web version would accept a user’s name.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

The lab builds on work done in the prior Lab 7. Specifically, you will use the insert_contact procedure that you developed for batch work. The insert_contact procedure supports manually adding customers to your system. It was a necessary procedure until the development team completed writing the end-user web forms.

You are now tasked with writing a batch version of the procedure that uses the insert_contact procedure name. The difference between your original one for the web interface is that the batch version will accept a pv_user_id value instead of a pv_user_name value. The pv_user_id value will use a NUMBER data type, while the pv_user_name will continue to use a VARCHAR2 value. The difference between the two signatures (or parameter lists) of the insert_contact procedures makes them distinct, and effectively creates an overloaded insert_contact procedure.

You need to move the insert_contact procedure into a stored package to overload it. One version will have all the same parameters as the insert_contact procedure you wrote in the prior lab. The new version of the procedure will take a system_user_id value in lieu of the system_user_name value. You should be able to drop the cursor that queries the system_user table with the system_user_name value to find the system_user_id value.

Important Note: The batch version of the insert_contact procedure also needs to support the pv_user_id parameter as an optional value, which means we need to add an anonymous account to the system_user table and use a negative one as it’s surrogate key value in the system_user_id column.

Lab Description

The lab is premised on your running the create_video_store.sql script from the Oracle Database 12c PL/SQL Programming book. The script creates a set of tables for the video web store.

Your lab 7 script should run the following two scripts and perform the pre-steps found in Lab 7:


Or, you should run your apply_plsql_lab7.sql script to create the model:


  1. [5 points] Create a contact_package package specification that holds overloaded insert_contact procedures. One procedure supports batch programs with a user’s name and another supports authenticated web forms with a user’s ID; where the ID is a value from the system_user_id column of the system_user table.

  1. [15 points] Create a contact_package package body that implements two insert_contact procedures. They do the following:

    • One procedure supports web forms with a pv_user_name parameter. That version of the procedure converts the pv_user_name parameter to valid system_user_id column.
    • One procedure supports batch programs with an optional pv_user_id parameter. You should insert a -1 value for the created_by and last_updated_by columns when a null value is passed to the pv_user_id parameter. Alternatively, you insert a valid numeric value found in the system_user_id column of the system_user table.
    • Both procedures need to support individual and group memberships. The prior lab only supported individual memberships. You need to wrap the INSERT statement into the member table with some logic, like IF-block built around a single fetch cursor:

      • You create an lv_member_id variable with a NUMBER data type.
      • You create a get_member dynamic cursor against the member table that looks for a row by checking whether the member.account_number equals pv_account_number parameter value.
      • You open the get_member dynamic cursor by using the pv_account_number variable.
      • You attempt to fetch a row from the member table into the lv_member_id local variable.
      • If you do not find a row in the member table, insert a row in the member table; however, if you find a row in the member table, skip inserting a row into the member table.

  1. [5 points] Recreate the contact_package package body by converting the insert_contact procedures into overloaded functions. The overloaded functions should produce the same results as the overloaded procedures.

Written by michaelmclaughlin

November 1st, 2016 at 8:47 pm

Posted in