Exercise Oracle package and package body

To practice the exercise you need the schema. You can found the schema from the post http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html and before practice these exercises you must first run those sqls so that you can get tables and data to practice. After you know the answer of each step please post your PL/SQL inside comment section so that other can check and understand it and you will be more clear about it.

Scenario/Summary

This week we are going to take the three program units that were created in lab 3 and combine them into a single functioning unit called a package. Packages allow use to group procedures and functions that deal with a common process together.
For the lab you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will be running tests identical to those run in lab 3 once your package has been created. Spool your output and name your files with your last name plus lab 4 and give the file a text (txt) extension. For example, if you last name was Johnson then the file would be named johnson_lab4.txt. Submit both the spooled output files AND the script file for grading of the lab.

L A B S T E P

Step 1: Creating the Package Specifications

Before you begin there are several things you will want to do to get ready for the lab.

1. Refresh your database tables by running the movierental.sql script. This will restore all of the data back to where it was in the beginning for the course.
2. Drop the two procedures and the function created in lab 3. BE sure that you have your script file from lab 3 so you can copy the procedure and function code from it.

Now you are ready to create you package specification. Your package name should be MM_RENTALS_PKG and it will contain the two procedures and the one function that were created in lab 3. Remember that for the specification you only need to list the procedure and function header data (CREATE statement with parameters).
Test your package specification by running the script in SQL*Plus. If you have any errors then debug them and once you have a clean compile then move on to step 2.

Step 2: Creating the Package Body

Creating the package body should be simple since you already have the code for the two procedures and the function, and you know it works. Remember that the name for the package body must match the name of the specifications, and that the procedure and function header in the body must match that of the specification exactly.
Once you have created the body then run the script in your SQL*Plus session. Once you have a clean compile then move on to step three to do your testing.

Step 3: Testing the Package

To test your package you will need to run the same exact tests you did in lab 3. The following outlines what you will test for:
Testing the first procedure -

1. No movie for the id supplied (use 13, 10, and 2 for the parameters).
2. No member for the id supplied (use 10, 20, and 2 for the parameters).
3. No payment method for the id supplied (use 10, 10 and 7 for the parameters).
4. A successful rental (use 5, 10 and 2 for the parameters).
5. No movie available for the id supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for id 5 you will get this exception.

Testing the second procedure -

1. No rental for the id supplied (use 20 for the parameter).
2. A successful rental return (use 1 for the parameter).
3. Try to return the same rental in step 2.

Testing the function -

1. Test for a movie in stock using movie id 11.
2. Test for a movie not in stock using movie id 5 (from your tests of the second procedure above the quantity should be 0).
3. Test for an invalid movie id using movie id 20.

IMPORTANT: Remember that all of your testing needs to be saved in a spool session so that it can be submitted to the Drop Box for grading.

Step 4: Determining Dependencies

Having created a package that contains program units to support the movie rental process is a major step in customizing the new database. As application modifications are made in the future however we need to be able to identify all object dependencies to test changes. For this step in the lab you are to use either data dictionary views or the dependency tree utility found in Doc Sharing (utldtree.sql file) to compile a list of dependencies for all the More Movies database objects. Remember that an object is anything that was created using the CREATE statement. Present your finding in a separate word document in a tabular format as in the following sample. Each dependency type should be listed as either direct or indirect.
NOTE: If using the utldtree.sql utility for this step then be sure to read the instructions in the comment area of the file. In executing the DEPTREE_FILL procedure that will be created by the script you will need to supply your schema name (user id) for the middle parameter of the parameter list in the execute command when your call the procedure.

Object Name	Dependent Object	Dependency Type