Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 8 Instructions

without comments

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 and tweetie_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 the warner_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 the warner_brother function and procedure. The warner_brother function and procedure take a list of two parameters that insert rows into the grandma and tweetie_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

  1. [5 points] Create a contact_package package specification that holds overloaded insert_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 the system_user_id column of the system_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.

  1. [15 points] Create a contact_package package body that implements two insert_contact procedures. They do the following:

    • One procedure supports web forms with a pv_user_name parameter. That version of the procedure converts the pv_user_name parameter to valid system_user_id column.
    • One procedure supports batch programs with an optional pv_user_id parameter. You should insert a -1 value for the created_by and last_updated_by columns when a null value is passed to the pv_user_id parameter. Alternatively, you insert a valid numeric value found in the system_user_id column of the system_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 the member table with some logic, like IF-block built around a single fetch cursor:

      • You create an lv_member_id variable with a NUMBER data type.
      • In the declaration block, you create a get_member dynamic cursor against the member table that looks for a row by checking whether the member.account_number equals pv_account_number parameter value.
      • In the execution block, you open the get_member dynamic cursor by using the pv_account_number variable; and you attempt to fetch a row from the member table into the lv_member_id local variable.(If you do not find a row in the member table, insert a row in the member table; however, if you find a row in the member table, skip inserting a row into the member table.

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

  1. [5 points] Recreate the contact_package package body by converting the insert_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

Written by michaelmclaughlin

November 1st, 2016 at 8:47 pm

Posted in