For this exercise, you will create a table and populate it with hundreds of unique rows of test data. These rows of data will based upon 10 rows of data in a table called BBT_USERS_TEMP. You will create this table by copying 10 rows from the table you created in project 5 (problem 2) using create table from an existing table technique.

Step 1 Create the BBT_USERS_TEMP table(10). Source table-10rows

Step 2 - Create PHONE_USERS table (10)destination table-lots of rows

Create a PHONE_USERS table to store the results of your procedure. The table is defined as follows:

SQL> desc phone_users 

 Name Null? Type 

 ------------------------------- -------- ---- 

 TELEPHONE_NUMBER NOT NULL VARCHAR2(80) 

 FIRST_NAME VARCHAR2(80) 

 LAST_NAME VARCHAR2(80) 

 KEYMAP_LASTNAME CHAR(4) 

 PASSWORD VARCHAR2(80) 

The column TELEPHONE_NUMBER should be defined as the primary key. Also, create a unique constraint on LAST_NAME.

Step 3 - create function(20)

Function name: KEYMAP Input Parameter: VARCHAR, any string which will usually be a person's last name Output value: VARCHAR, a 4 character string which represents their telephone extension. For purposes of this lab, the telephone extension will be based on the first four characters of their last name, 2 = ABC, 3 = DEF, 4 = GHI, 5=JKL, 6=MNO, 7=PQRS, 8=TUV, 9=WXYZ just like the letters on a telephone keypad. For names shorter than 4 characters, use zeros in those character positions. The Oracle function TRANSLATE will be helpful. (look into translate function)

Step 4 - create procedure (35)

You are to write a PL/SQL procedure that will iterate n times where n is the parameter you pass to your procedure. For each iteration of your procedure you will copy the rows of data from BBT_USERS_TEMP table into your PHONE_USERS table. As you insert this data you should perform the following transformations:

Each TELEPHONE_NUMBER should be unique. Make it is unique any way you want as long as it looks like a telephone number in the format (999) 999-9999. (10)

LAST_NAME should be LAST_NAME with 4 digits appended to the end. These digits will be 0001 through 000n where n is the iteration. (10)

KEYMAP_LASTNAME should be computed. It will be the telephone keymap for the 1st four letters of the last name. Use the function you wrote in step 2. (5)

All other columns will be copied exactly as they appear in the BBT_USERS_TEMP table. (10)

Extra columns in BBT_USERS_TEMP should be disregarded.

Step 5 - Create package (20)

Create a package which contains both your function and your procedure.

Step 6 Run the programs in the package to test. (5)