CIT325: Lab 8 Instructions
Lab #8: Oracle Assignment
Objectives
The lab is designed to teach you how to write stored procedures that insert data into several tables while maintaining transaction control across the set of insert statements. You will write an insert_contact
procedure that should achieve these objectives:
- Learn how to write stored functions and procedures inside packages.
- Learn how to write overloaded functions and procedures.
- Learn how to call and test overloaded functions and procedures.
Business Scenario
Application programming interfaces (API) can be composed of separate stored functions and procedures but they are best organized into packages. A package is defined by two components: one is a package specification and the other is a package body. A package specification publishes the signatures of functions and procedures. A package body may implement functions and procedures defined by the package specification.
A package specifications may hold more than just function and procedure definitions. A package specification may also hold PL/SQL record types and variables. You can access these types and variables inside and outside of the package where they are defined.
A package body may hold types, variables, functions, and procedures. Any types, variables, functions, and procedures defined in the package body are local to the package body you can only access them from other functions and procedures inside the package body. The package body must provide implementations for all functions and procedures defined in the package specification.
Overloading functions and procedures is the true advantage of packages. Overloaded functions and procedures have parameter lists that differ. The parameter lists must have a different number of parameters in the parameter list or a different order of the data types of the parameter lists.
Overloading lets us create functions and procedures that serve different purposes. For example, you can create a procedure that accepts values from an end-user application or from a batch programming application.
Batch programs are run by administrators. The administrator would know the system_user_id
that should be used as the created_by
and last_updated_by
audit column value. Knowing the correct system_user_id
value, the administrator call the batch version of the API. The batch version accepts a system_user_id
value whereas the interactive web version would accept a user’s name.
Help Section
The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.
The lab builds on work done in the prior Lab 7. Specifically, you will use the insert_contact
procedure that you developed for batch work. The insert_contact
procedure supports manually adding customers to your system. It was a necessary procedure until the development team completed writing the end-user web forms.
You are now tasked with writing a batch version of the procedure that uses the insert_contact
procedure name. The difference between your original one for the web interface is that the batch version will accept a pv_user_id
value instead of a pv_user_name
value. The pv_user_id
value will use a NUMBER
data type, while the pv_user_name
will continue to use a VARCHAR2
value. The difference between the two signatures (or parameter lists) of the insert_contact
procedures makes them distinct, and effectively creates an overloaded insert_contact
procedure.
You need to move the insert_contact
procedure into a stored package to overload it. One version will have all the same parameters as the insert_contact
procedure you wrote in the prior lab. The new version of the procedure will take a system_user_id
value in lieu of the system_user_name
value. You should be able to drop the cursor that queries the system_user
table with the system_user_name
value to find the system_user_id
value.
Important Note: The batch version of the insert_contact
procedure also needs to support the pv_user_id
parameter as an optional value, which means we need to add an anonymous account to the system_user
table and use a negative one as it’s surrogate key value in the system_user_id
column.
Instructional Material →
The following code shows you how to log data in a table. It shows you how to create the logging table and a stored procedure that will write to the table.
- How to write a re-runnable script that creates the
grandma
andtweetie_bird
tables./* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Conditionally drop a table and sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq;
- How to write a
two_table
package specification that holds the definition of thewarner_brother
function and procedure. They both takes a list of two parameters.1 2 3 4 5 6 7 8 9
CREATE OR REPLACE PACKAGE twotable IS PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ); FUNCTION warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) RETURN NUMBER; END twotable; /
- How to write a
two_table
package specification that holds the implementation of thewarner_brother
function and procedure. Thewarner_brother
function and procedure take a list of two parameters that insert rows into thegrandma
andtweetie_bird
tables.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
CREATE OR REPLACE PACKAGE BODY twotable IS PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* Use a cursor, which will not raise an exception at runtime. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the ID # when row found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* Check for existing grandma row. */ lv_grandma_id := get_grandma_id(pv_grandma_house); IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END warner_brother; FUNCTION warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) RETURN NUMBER IS /* Declare a local variable for an existing grandma_id and return variable. */ lv_grandma_id NUMBER; lv_retval NUMBER := 1; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* Use a cursor, which will not raise an exception at runtime. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the ID # when row found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* Check for existing grandma row. */ lv_grandma_id := get_grandma_id(pv_grandma_house); IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; /* Return the value. */ lv_retval := 0; /* Return the value. */ RETURN lv_retval; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END warner_brother; END twotable; /
- How to write an anonymous block test case for the
warner_brother
function and procedure.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
DECLARE /* Declare a control variable. */ lv_retval NUMBER := 1; BEGIN /* Test the warner_brother procedure. */ twotable.warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Cage'); /* Test the warner_brother function. */ IF twotable.warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Tree House' ) = 0 THEN dbms_output.put_line('[warner_brother] function was successful'); END IF; END; /
It prints the following:
[warner_brother] function was successful.
- How to query the results from two calls to the
warner_brother
procedure.COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A20 HEADING "Grandma House" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A20 HEADING "Tweetie Bird House" SELECT * FROM grandma NATURAL JOIN tweetie_bird;
It should return the following results:
Grandma Tweetie ID # Grandma House Bird ID Tweetie Bird House -------- -------------------- -------- -------------------- 1 Yellow House 1 Cage 1 Yellow House 2 Tree House
Lab Description
The lab is premised on your running the create_video_store.sql
script from the Oracle Database 12c PL/SQL Programming book. The script creates a set of tables for the video web store.
Your lab 7 script should run the following two scripts and perform the pre-steps found in Lab 7:
@/home/student/Data/cit325/lib/cleanup_oracle.sql @/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/create_video_store.sql |
Or, you should run your apply_plsql_lab7.sql
script to create the model:
@/home/student/Data/cit325/lab7/apply_plsql_lab7.sql |
- [5 points] Create a
contact_package
package specification that holds overloadedinsert_contact
procedures. One procedure supports batch programs with a user’s name and another supports authenticated web forms with a user’s ID; where the ID is a value from thesystem_user_id
column of thesystem_user
table.
Instruction Details →
The insert_contact
procedures requires formal parameter lists. The procedures should include all the values that you need to insert or discover to insert into the member
, contact
, address
, and telephone
tables.
You can describe the package specification like this:
DESC contact_package |
It should display the following:
PROCEDURE INSERT_CONTACT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- PV_FIRST_NAME VARCHAR2 IN PV_MIDDLE_NAME VARCHAR2 IN PV_LAST_NAME VARCHAR2 IN PV_CONTACT_TYPE VARCHAR2 IN PV_ACCOUNT_NUMBER VARCHAR2 IN PV_MEMBER_TYPE VARCHAR2 IN PV_CREDIT_CARD_NUMBER VARCHAR2 IN PV_CREDIT_CARD_TYPE VARCHAR2 IN PV_CITY VARCHAR2 IN PV_STATE_PROVINCE VARCHAR2 IN PV_POSTAL_CODE VARCHAR2 IN PV_ADDRESS_TYPE VARCHAR2 IN PV_COUNTRY_CODE VARCHAR2 IN PV_AREA_CODE VARCHAR2 IN PV_TELEPHONE_NUMBER VARCHAR2 IN PV_TELEPHONE_TYPE VARCHAR2 IN PV_USER_NAME VARCHAR2 IN PROCEDURE INSERT_CONTACT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- PV_FIRST_NAME VARCHAR2 IN PV_MIDDLE_NAME VARCHAR2 IN PV_LAST_NAME VARCHAR2 IN PV_CONTACT_TYPE VARCHAR2 IN PV_ACCOUNT_NUMBER VARCHAR2 IN PV_MEMBER_TYPE VARCHAR2 IN PV_CREDIT_CARD_NUMBER VARCHAR2 IN PV_CREDIT_CARD_TYPE VARCHAR2 IN PV_CITY VARCHAR2 IN PV_STATE_PROVINCE VARCHAR2 IN PV_POSTAL_CODE VARCHAR2 IN PV_ADDRESS_TYPE VARCHAR2 IN PV_COUNTRY_CODE VARCHAR2 IN PV_AREA_CODE VARCHAR2 IN PV_TELEPHONE_NUMBER VARCHAR2 IN PV_TELEPHONE_TYPE VARCHAR2 IN PV_USER_ID NUMBER IN DEFAULT |
Test Case
You do not need to write a test case after you successfully describe the package.
- [15 points] Create a
contact_package
package body that implements twoinsert_contact
procedures. They do the following:- One procedure supports web forms with a
pv_user_name
parameter. That version of the procedure converts thepv_user_name
parameter to validsystem_user_id
column. - One procedure supports batch programs with an optional
pv_user_id
parameter. You should insert a-1
value for thecreated_by
andlast_updated_by
columns when a null value is passed to thepv_user_id
parameter. Alternatively, you insert a valid numeric value found in thesystem_user_id
column of thesystem_user
table. - Both procedures need to support individual and group memberships. The prior lab only supported individual memberships. You need to wrap the
INSERT
statement into themember
table with some logic, likeIF
-block built around a single fetch cursor:- You create an
lv_member_id
variable with aNUMBER
data type. - In the declaration block, you create a
get_member
dynamic cursor against themember
table that looks for a row by checking whether themember.account_number
equalspv_account_number
parameter value. - In the execution block, you open the
get_member
dynamic cursor by using thepv_account_number
variable; and you attempt to fetch a row from themember
table into thelv_member_id
local variable.(If you do not find a row in themember
table, insert a row in themember
table; however, if you find a row in themember
table, skip inserting a row into themember
table.
- You create an
- One procedure supports web forms with a
Instruction Details →
You need to insert three new users, as qualified below (manually enter the system_user_id
as numeric literals rather than calling the system_user_s1
sequence):
Table Name: SYSTEM_USER | |||||
---|---|---|---|---|---|
Column Name | Column Value #1 | Column Value #2 | Column Value #3 | ||
SYSTEM_USER_ID | 6 | 7 | -1 | ||
SYSTEM_USER_NAME | BONDSB | OWENSR | ANONYMOUS | ||
SYSTEM_USER_GROUP_ID | 1 | 1 | 1 | ||
SYSTEM_USER_TYPE | DBA | DBA | DBA | ||
FIRST_NAME | Barry | Wardell | |||
MIDDLE_NAME | Lamar | Stephen | |||
LAST_NAME | Bonds | Curry | |||
CREATED_BY | System Administrator | System Administrator | System Administrator | ||
CREATION_DATE | Today’s date | Today’s date | Today’s date | ||
LAST_UPDATED_BY | System Administrator | System Administrator | System Administrator | ||
LAST_UPDATE_DATE | Today’s date | Today’s date | Today’s date |
You can confirm the inserts with the following query:
COL system_user_id FORMAT 9999 HEADING "System|User ID" COL system_user_name FORMAT A12 HEADING "System|User Name" COL first_name FORMAT A10 HEADING "First|Name" COL middle_initial FORMAT A2 HEADING "MI" COL last_name FORMAT A10 HeADING "Last|Name" SELECT system_user_id , system_user_name , first_name , middle_initial , last_name FROM system_user WHERE last_name IN ('Bonds','Curry') OR system_user_name = 'ANONYMOUS'; |
It prints the following:
System System First Last User ID User Name Name MI Name ------- ------------ ---------- -- ---------- 6 BONDSB Barry L Bonds 7 CURRYW Wardell S Curry -1 ANONYMOUS |
The insert_contact
procedure requires a formal parameter list. The procedure should include all the values that you need to insert or discover to insert into the member
, contact
, address
, and telephone
tables.
The for loop can’t effectively support the absence of a row. You need to write a simple loop when determining whether the row exists in the member
table; and the simple loop should only insert a row into the member
table when the %NOTFOUND
condition is true (check Chapter 3 for the Simple Loop Statements section on pages 88 through 91 on simple loops).
The list of formal parameters is listed in the table below:
Procedure Name: INSERT_CONTACT | |||||
---|---|---|---|---|---|
Parameter Name | Parameter Value #1 | Parameter Value #2 | Parameter Value #3 | ||
PV_FIRST_NAME | Charlie | Peppermint | Sally | ||
PV_MIDDLE_NAME | |||||
PV_LAST_NAME | Brown | Patty | Brown | ||
PV_CONTACT_TYPE | CUSTOMER | CUSTOMER | CUSTOMER | ||
PV_ACCOUNT_NUMBER | SLC-000011 | SLC-000011 | SLC-000011 | ||
PV_MEMBER_TYPE | GROUP | GROUP | GROUP | ||
PV_CREDIT_CARD_NUMBER | 8888-6666-8888-4444 | 8888-6666-8888-4444 | 8888-6666-8888-4444 | ||
PV_CREDIT_CARD_TYPE | VISA_CARD | VISA_CARD | VISA_CARD | ||
PV_CITY | Lehi | Lehi | Lehi | ||
PV_STATE_PROVINCE | Utah | Utah | Utah | ||
PV_POSTAL_CODE | 84043 | 84043 | 84043 | ||
PV_ADDRESS_TYPE | HOME | HOME | HOME | ||
PV_COUNTRY_CODE | 001 | 001 | 001 | ||
PV_AREA_CODE | 207 | 207 | 207 | ||
PV_TELEPHONE_NUMBER | 877-4321 | 877-4321 | 877-4321 | ||
PV_TELEPHONE_TYPE | HOME | HOME | HOME | ||
PV_USER_NAME | DBA 3 | ||||
PV_USER_ID | 6 |
After you successfully compile the package specification and body, you can create an anonymous block to test the procedures inside the package. You should note that the describe command only describes the contents of the package specification.
Test Case
You write an anonymous block PL/SQL program to test the overloaded insert_contact
procedures. You call the insert_contact
procedure with the information provided in the foregoing table that lists the insert_contact
parameter values. You should note that you will be making three calls to the overloaded insert_contact
procedures. Two will use the pv_user_name
parameter and one will use the pv_user_id
parameter.
You should remember that the exception handler can suppress raised errors, and you should only add it to the insert_contact
procedure after everything works during your test case. For example, it might suppress an error message like this:
BEGIN * ERROR AT line 1: ORA-01400: cannot INSERT NULL INTO ("STUDENT"."MEMBER"."CREDIT_CARD_TYPE") ORA-06512: AT "STUDENT.INSERT_CONTACT", line 88 ORA-06512: AT line 3 |
This type of error would indicate that you may have mapped an incorrect variable in the INSERT
statement to the member
table. You may receive similar error messages when the dynamic cursor fails to return a value from the common_lookup
table.
You would test this by calling the package_name.procedure_name in an anonymous block program. You may use either named or positional notation to make the call but named notation is recommended. A small prototype is:
BEGIN contact_package.insert_contact( pv_first_name => 'some_string' , pv_middle_name => NULL ... ); END; / |
After you call the overloaded insert_contact
procedures three times, you should be able to run the following verification query:
COL full_name FORMAT A24 COL account_number FORMAT A10 HEADING "ACCOUNT|NUMBER" COL address FORMAT A22 COL telephone FORMAT A14 SELECT c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name||' ' ELSE ' ' END || c.last_name AS full_name , m.account_number , a.city || ', ' || a.state_province AS address , '(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name IN ('Brown','Patty'); |
It should print the following because Charlie Brown and his sister Sally Brown share their account with Peppermint Patty:
ACCOUNT FULL_NAME NUMBER ADDRESS TELEPHONE ------------------------ ---------- ---------------------- -------------- Charlie Brown SLC-000011 Lehi, Utah (207) 877-4321 Peppermint Patty SLC-000011 Lehi, Utah (207) 877-4321 Sally Brown SLC-000011 Lehi, Utah (207) 877-4321 |
- [5 points] Recreate the
contact_package
package body by converting theinsert_contact
procedures into overloaded functions. The overloaded functions should produce the same results as the overloaded procedures.
Instruction Details →
The insert_contact
function requires a formal parameter list. The function should include all the values that you need to insert or discover to insert into the member
, contact
, address
, and telephone
tables. It should return a zero if successful and a one if unsuccessful.
The contact_package
specification changes as follows when you convert the overloaded insert_contact
procedures to functions:
FUNCTION INSERT_CONTACT RETURNS NUMBER Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- PV_FIRST_NAME VARCHAR2 IN PV_MIDDLE_NAME VARCHAR2 IN PV_LAST_NAME VARCHAR2 IN PV_CONTACT_TYPE VARCHAR2 IN PV_ACCOUNT_NUMBER VARCHAR2 IN PV_MEMBER_TYPE VARCHAR2 IN PV_CREDIT_CARD_NUMBER VARCHAR2 IN PV_CREDIT_CARD_TYPE VARCHAR2 IN PV_CITY VARCHAR2 IN PV_STATE_PROVINCE VARCHAR2 IN PV_POSTAL_CODE VARCHAR2 IN PV_ADDRESS_TYPE VARCHAR2 IN PV_COUNTRY_CODE VARCHAR2 IN PV_AREA_CODE VARCHAR2 IN PV_TELEPHONE_NUMBER VARCHAR2 IN PV_TELEPHONE_TYPE VARCHAR2 IN PV_USER_NAME VARCHAR2 IN FUNCTION INSERT_CONTACT RETURNS NUMBER Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- PV_FIRST_NAME VARCHAR2 IN PV_MIDDLE_NAME VARCHAR2 IN PV_LAST_NAME VARCHAR2 IN PV_CONTACT_TYPE VARCHAR2 IN PV_ACCOUNT_NUMBER VARCHAR2 IN PV_MEMBER_TYPE VARCHAR2 IN PV_CREDIT_CARD_NUMBER VARCHAR2 IN PV_CREDIT_CARD_TYPE VARCHAR2 IN PV_CITY VARCHAR2 IN PV_STATE_PROVINCE VARCHAR2 IN PV_POSTAL_CODE VARCHAR2 IN PV_ADDRESS_TYPE VARCHAR2 IN PV_COUNTRY_CODE VARCHAR2 IN PV_AREA_CODE VARCHAR2 IN PV_TELEPHONE_NUMBER VARCHAR2 IN PV_TELEPHONE_TYPE VARCHAR2 IN PV_USER_ID NUMBER IN DEFAULT |
The list of formal parameters is listed in the table below:
Function Name: INSERT_CONTACT | |||||
---|---|---|---|---|---|
Parameter Name | Parameter Value #1 | Parameter Value #2 | Parameter Value #3 | ||
PV_FIRST_NAME | Shirley | Keith | Laurie | ||
PV_MIDDLE_NAME | |||||
PV_LAST_NAME | Partridge | Partridge | Partridge | ||
PV_CONTACT_TYPE | CUSTOMER | CUSTOMER | CUSTOMER | ||
PV_ACCOUNT_NUMBER | SLC-000012 | SLC-000012 | SLC-000012 | ||
PV_MEMBER_TYPE | GROUP | GROUP | GROUP | ||
PV_CREDIT_CARD_NUMBER | 8888-6666-8888-4444 | 8888-6666-8888-4444 | 8888-6666-8888-4444 | ||
PV_CREDIT_CARD_TYPE | VISA_CARD | VISA_CARD | VISA_CARD | ||
PV_CITY | Lehi | Lehi | Lehi | ||
PV_STATE_PROVINCE | Utah | Utah | Utah | ||
PV_POSTAL_CODE | 84043 | 84043 | 84043 | ||
PV_ADDRESS_TYPE | HOME | HOME | HOME | ||
PV_COUNTRY_CODE | 001 | 001 | 001 | ||
PV_AREA_CODE | 207 | 207 | 207 | ||
PV_TELEPHONE_NUMBER | 877-4321 | 877-4321 | 877-4321 | ||
PV_TELEPHONE_TYPE | HOME | HOME | HOME | ||
PV_USER_NAME | DBA 3 | ||||
PV_USER_ID | 6 | -1 |
After you successfully compile the package specification and body, you can create an anonymous block to test the functions inside the package. You should note that the describe command only describes the contents of the package specification.
Test Case
You write an anonymous block PL/SQL program to test the overloaded insert_contact
functions. You call the insert_contact
function with the information provided in the foregoing table that lists the insert_contact
parameter values. You should note that you will be making three calls to the overloaded insert_contact
functions. The three anonymous block programs should do the following:
- One will use the
pv_user_name
parameter. - One will use the
pv_user_id
parameter with a valid numeric value. - One will not provide a call parameter to the
pv_user_id
parameter.
You should remember that the exception handler can suppress raised errors, and you should only add it to the insert_contact
procedure after everything works during your test case. For example, it might suppress an error message like this:
BEGIN * ERROR AT line 1: ORA-01400: cannot INSERT NULL INTO ("STUDENT"."MEMBER"."CREDIT_CARD_TYPE") ORA-06512: AT "STUDENT.INSERT_CONTACT", line 88 ORA-06512: AT line 3 |
This type of error would indicate that you may have mapped an incorrect variable in the INSERT
statement to the member
table. You may receive similar error messages when the dynamic cursor fails to return a value from the common_lookup
table.
You would test this by calling the package_name.function_name inside an if-block of an anonymous block program. You may use either named or positional notation to make the call but named notation is recommended. A small prototype is:
BEGIN IF contact_package.insert_contact( pv_first_name => 'some_string' , pv_middle_name => NULL ... ) = 1 THEN dbms_output.put_line('Insert function succeeds.'); END IF; END; / |
After you call the overloaded insert_contact
function three times, you should be able to run the following verification query:
COL full_name FORMAT A18 HEADING "Full Name" COL created_by FORMAT 9999 HEADING "System|User ID" COL account_number FORMAT A12 HEADING "Account|Number" COL address FORMAT A16 HEADING "Address" COL telephone FORMAT A16 HEADING "Telephone" SELECT c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name||' ' ELSE ' ' END || c.last_name AS full_name , c.created_by , m.account_number , a.city || ', ' || a.state_province AS address , '(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name = 'Partridge'; |
It should print Shirley, Keith, and Laurie Partridge:
System Account Full Name User ID Number Address Telephone ------------------ ------- ------------ ---------------- ---------------- Shirley Partridge 4 SLC-000012 Lehi, Utah (207) 877-4321 Keith Partridge 6 SLC-000012 Lehi, Utah (207) 877-4321 Laurie Partridge -1 SLC-000012 Lehi, Utah (207) 877-4321 |