Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 8 Instructions

without comments

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 same 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.

Batch programs are run by administrators and they can manually lookup the system_user_id for whichever account created the data source. In fact, you sometimes want to make sure they verify that information. In other cases, a developer will create a physical external file, often in a comma-separated-values format for a bulk insert. They’ll need to put in user ID value to avoid repetitive lookup activities. There would be other tuning conventions but we’re limiting the scope to focus on overloading procedures in a package.

While a web form would authenticate the user when the user signs on to the application software, batch processing requires you provide the user friendly system_user_id value. The web form authentication program would query the system_user table, validate the user name and password, and return the system_user_id value to the calling session. That’s what you should have accomplished in lab 7. In a real world scenario, you would authenticate the user and set the session value before calling the procedure, which means you really could use the system_user_id value in both cases.

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.

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