DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

Description

DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

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 your 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, debug them and once you have a clean compile, 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 3 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 Dropbox 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.