Assignment 1: Creating a Package

Follow the steps to create a package containing a procedure and a function pertaining to basket information. (Note: The first time you compile the package body doesn't give you practice with compilation error messages.)

1. Start Notepad, and open the Assignment07-01.txt file in the Chapter07 folder.

2. Review the package code, and then copy it.

3. In SQL Developer, paste the copied code to build the package.

4. Review the compilation errors and identify the related coding error.

5. Edit the package to correct the error and compile the package.

Assignment 2: Using Program Units in a Package

In this assignment, you use program units in a package created to store basket information. The package contains a function that returns the recipient's name and a procedure that retrieves the shopper ID and order date for a basket.

1. In SQL Developer, create the ORDER_INFO_PKG package, using the Assignment07-02.txt file in the Chapter07 folder. Review the code to become familiar with the two program units in the package.

2. Create an anonymous block that calls both the packaged procedure and function with basket ID 12 to test these program units. Use DBMS_OUTPUT statements to display values returned from the program units to verify the data.

3. Also, test the packaged function by using it in a SELECT clause on the BB_BASKET table. Use a WHERE clause to select only the basket 12 row.

Assignment 3: Creating a Package with Private Program Units

In this assignment, you modify a package to make program units private. The Brewbean's programming group decided that the SHIP_NAME_PF function in the ORDER_INFO_PKG package should be used only from inside the package. Follow these steps to make this modification:

1. In Notepad, open the Assignment07-03.txt file in the Chapter07 folder, and review the package code.

2. Modify the package code to add to the BASKET_INFO_PP procedure so that it also returns the name an order is shipped by using the SHIP_NAME_PF function. Make the necessary changes to make the SHIP_NAME_PF function private.

3. Create the package by using the modified code.

4. Create and run an anonymous block that calls the BASKET_INFO_PP procedure and displays the shopper ID, order date, and shipped-to name to check the values returned. Use DBMS_OUTPUT statements to display the values.

Assignment 4: Using Packaged Variables

In this assignment, you create a package that uses packaged variables to assist in the user logon process. When a returning shopper logs on, the username and password entered need to be verified against the database. In addition, two values need to be stored in packaged variables for reference during the user session: the shopper ID and the first three digits of the shopper's zip code (used for regional advertisements displayed on the site).

1. Create a function that accepts a username and password as arguments and verifies these values against the database for a match. If a match is found, return the value Y. Set the value of the variable holding the return value to N. Include a NO_DATA_FOUND exception handler to display a message that the logon values are invalid.

2. Use an anonymous block to test the procedure, using the username gma1 and the password goofy.

3. Now place the function in a package, and add code to create and populate the packaged variables specified earlier. Name the package LOGIN_PKG.

4. Use an anonymous block to test the packaged procedure, using the username gma1 and the password goofy to verify that the procedure works correctly.

Assignment 5: Overloading Packaged Procedures

In this assignment, you create packaged procedures to retrieve shopper information. Brewbean's is adding an application page where customer service agents can retrieve shopper information by using shopper ID or last name. Create a package named SHOP_QUERY_PKG containing overloaded procedures to perform these lookups. They should return the shopper's name, city, state, phone number, and e-mail address. Test the package twice. First, call the procedure with shopper ID 23, and then call it with the last name Ratman. Both test values refer to the same shopper, so they should return the same shopper information.

Assignment 6: Creating a Package with Only a Specification

In this assignment, you create a package consisting of only a specification. The Brewbean's lead programmer has noticed that only a few states require Internet sales tax, and the rates don't change often. Create a package named TAX_RATE_PKG to hold the following tax rates in packaged variables for reference: pv_tax_nc = .035, pv_tax_tx = .05, and pv_tax_tn = .02. Code the variables to prevent the rates from being modified. Use an anonymous block with DBMS_OUTPUT statements to display the value of each packaged variable.

Assignment 7: Using a Cursor in a Package

In this assignment, you work with the sales tax computation because the Brewbean's lead programmer expects the rates and states applying the tax to undergo some changes. The tax rates are currently stored in packaged variables but need to be more dynamic to handle the expected changes. The lead programmer has asked you to develop a package that holds the tax rates by state in a packaged cursor. The BB_TAX table is updated as needed to reflect which states are applying sales tax and at what rates. This package should contain a function that can receive a two-character state abbreviation (the shopper's state) as an argument, and it must be able to find a match in the cursor and return the correct tax rate. Use an anonymous block to test the function with the state value NC.

Assignment 8: Using a One-Time-Only Procedure in a Package

The Brewbean's application currently contains a package used in the shopper logon process. However, one of the developers wants to be able to reference the time users log on to determine when the session should be timed out and entries rolled back. Modify the LOGIN_PKG package (in the Assignment07-08.txt file in the Chapter07 folder). Use a one-time-only procedure to populate a packaged variable with the date and time of user logons. Use an anonymous block to verify that the one-time-only procedure works and populates the packaged variable.