Creating Packages   «Prev 

Making the Oracle Package Body - Exercise

Create an Oracle Package

Objective: Combine two procedures and a function into a package

Exercise Scoring

This exercise is worth 30 points. You will receive points for correct syntax of the package and for good documentation explaining your use of parameters and variables.

Background Overview

You are working with the House-O-Pets database and have created a procedure and a function. You have decided that it will be more efficient for all of these objects to be stored and used as a package. Your goal is to take the PL/SQL code that creates
  1. the procedure and
  2. the function
and convert it so that it creates a single package that contains both sub-programs. The function should be a private sub-program within the package.

Download Files

Download the package_exercise.sql file from the following link for the source code of the procedure and functions to be included in your package.


Using the downloaded file and your own text editor, create the two commands needed to generate the package specification and package body. Create your
  1. own name for the package,
  2. procedure,
  3. function,
  4. and all parameters.
Document your code by explaining how you used parameters and variables in the package.

Submitting your Exercise

Copy the two SQL commands you have created and your explanations for the parameters and variables you used into the text box shown here. Click the Submit button to see the results to you.