Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 7 Instructions

without comments

Lab #7: 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.
  • Learn how to write autonomous stored functions and procedures.
  • Learn how to write object table functions.
  • Learn how to use transaction control language (TCL) commands to manage transaction scope.
  • Learn how to call and test stored functions and procedures.

Business Scenario

Web and client-server application developers do not want to manage each insert, update, and delete from an application. That’s why backend application developers build stored functions, procedures, and objects. These stored programs hide the complexity of tables and provide a single interface to write to collections of related tables.

This lab lets you create an insert_contact procedure. A web form could call the insert_contact procedure to insert data into a series of table from a single submit button. The insert_contact procedure becomes a standard application interface (API) to manage an all or nothing insert to four tables (the member, contact, address, and telephone tables) for a new contact/customer in the video store model. The insert_contact API also lets you submit the same values for a second new contact/customer by validating an existing entry in the member table before inserting into the remaining three tables.

More or less, the insert_contact procedure allows you to create a new member account tied to a single contact/customer or a member account tied to two or more contact/customer. Technically, you would also insert into the street_address table with this video store model but we’ve simplified the exercise to only achieve the core learning objective. It teaches you how to write a backend stored procedure across four related tables.

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 has four parts.

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.

You can find the Video store create_video_store.sql script in the following directory on the Linux VM distributed for class:


The zero step walks you through setting up the video store environment. Click on the Instruction Details link to see the setup instructions.

  1. [0 points] The nested instructions let you make your test cases automatic because you can run the create_video_store.sql script at the beginning of your lab solution.

While you don’t need this to accomplish the first part, you will need to include it at the beginning of your lab. That because the CREATE OR REPLACE syntax only lets you replace an object of the same type. The third part of the lab converts the insert_contact procedure to a function. You need this at the beginning to create the initial procedure during iterative testing.

  FOR i IN (SELECT uo.object_type
            ,      uo.object_name
            FROM   user_objects uo
            WHERE  uo.object_name = 'INSERT_CONTACT') LOOP
    EXECUTE IMMEDIATE 'DROP ' || i.object_type || ' ' || i.object_name;

Lab 7 has four parts. You create an insert_contact definer rights procedure and test case in the first step. In the second step, you modify the definer rights insert_contact procedure to be invoker rights procedure. In step 3, you create an autonomous definer rights insert_contact function by modifying the insert_contact procedure. Finally in step 4, you create a contact_obj SQL data type, a contact_tab SQL collection data type, and a get_contact object table function.

The web page lets you open each part by leveraging folding. You click on the Instruction Details to see the instructions for each step.

  1. [10 points] Create an insert_contact procedure that writes an all or nothing procedure. The procedure inserts into the member, contact, address, and telephone tables, which means you use transaction control language (TCL). TCL principles require you put the database in a transactional state, which applies to any Oracle session/connection by default. A TCL lets you commit after a successful insert into all tables, but TCL requires that you roll back all SQL DML statements with only a single failure. The roll back should limit its scope to the current procedure, which means it rolls back only to the local save point. Failures should only occur when an insert into any one of the four tables fails.

  1. [5 points] Modify the insert_contact definer rights procedure into an autonomous insert_contact invoker rights procedure. You need to add a precompiled instruction, or PRAGMA, to your procedure to make it an autonomous transaction (refer to page 374 in the Oracle Database 12c PL/SQL Programming textbook for an autonomous transaction precompiled instruction example). The change between a definer rights to invoker rights program will have no impact on running the procedure because you’re working in a single database schema. Like the prior insert_contact procedure, this procedure requires you to use transaction control language (TCL).

  1. [5 points] Modify the insert_contact invoker rights procedure into an autonomous insert_contact definer rights function that returns a number. The insert_contact function should return a zero when successful and a 1 when unsuccessful. The change between a procedure and a function means you now return a value from calling the function.
  1. [5 points] This step requires that you create a get_contact object table function, which requires a contact_obj SQL object type and a contact_tab SQL collection type (page 318). After you define the SQL object type and collection type, you can create the get_contact object table function (like the get_full_titles example on pages 318-319).

    In this assignment, your object table function isn’t parameterized, and should return a complete list of persons from the contact table. It should return the names in a first, middle, and last name format with a single white space between each element of the contact’s full name.

Please submit the lab as one script file with all steps.

Written by michaelmclaughlin

October 27th, 2016 at 10:03 pm

Posted in