Database Tutorial

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;

  /* 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 warner_brother procedure that takes a list of parameters that inserts 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

  CREATE OR REPLACE 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; /

  CREATE OR REPLACE 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; /

• How to write an anonymous block test case for the warner_brother procedure.

  BEGIN warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Cage'); warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Tree House'); END; /

  BEGIN warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Cage'); warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Tree House'); END; /

• 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;

  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

  Grandma Tweetie ID # Grandma House Bird ID Tweetie Bird House -------- -------------------- -------- -------------------- 1 Yellow House 1 Cage 1 Yellow House 2 Tree House

• How to refactor the warner_brother procedure into an autonomous transaction.

  CREATE OR REPLACE 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;   /* Provide a pre-compiler instruction. */ PRAGMA autonomous_transaction;   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; /

  CREATE OR REPLACE 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; /* Provide a pre-compiler instruction. */ PRAGMA autonomous_transaction; 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; /

If you want to test your code from your /home/student/Data/lib directory, you need to make some changes to the create_video_store.sql script because it uses a relative directory reference to subordinate scripts. You need to change the following line numbers in the create_video_store.sql program.

Line 3215:

@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/update_price_function.sql

Lines 3244 & 3245:

@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/contact_insert.sql
@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/contact_insert_10g.sql

Line 3258:

@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/transaction_procedure.sql

Lines 3532 & 3533:

@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/insert_rental.sql
@@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/insert_rental_items.sql

After you’ve run the create_video_store.sql script, the system_user table needs to be fixed. There are four DBA names in the system_user_name and the column should be unique.

Putting these into your Lab 7 solution will simplify your iterative testing as you work on the lab. You can fix the table by first validating it’s state with this query:

SELECT system_user_id
,      system_user_name
FROM   system_user
WHERE  system_user_name = 'DBA';

It should return:

SYSTEM_USER_ID SYSTEM_USER_NAME
-------------- --------------------
	     2 DBA
	     3 DBA
	     4 DBA
	     5 DBA

The next UPDATE statement should be inserted to ensure your iterative test cases all start at the same point, or common data state. It re-introduces the error if its been fixed. Putting the UPDATE statement in your code avoids concatenating multiple numbers to the DBA name.

UPDATE system_user
SET    system_user_name = 'DBA'
WHERE  system_user_name LIKE 'DBA%';

A small anonymous block PL/SQL program lets you fix this mistake:

DECLARE
  /* Create a local counter variable. */
  lv_counter  NUMBER := 2;
 
  /* Create a collection of two-character strings. */
  TYPE numbers IS TABLE OF NUMBER;
 
  /* Create a variable of the roman_numbers collection. */
  lv_numbers  NUMBERS := numbers(1,2,3,4);
 
BEGIN
  /* Update the system_user names to make them unique. */
  FOR i IN 1..lv_numbers.COUNT LOOP
    /* Update the system_user table. */
    UPDATE system_user
    SET    system_user_name = system_user_name || ' ' || lv_numbers(i)
    WHERE  system_user_id = lv_counter;
 
    /* Increment the counter. */
    lv_counter := lv_counter + 1;
  END LOOP;
END;
/

It should update four rows, and you can verify the update with the following query:

SELECT system_user_id
,      system_user_name
FROM   system_user
WHERE  system_user_name LIKE 'DBA%';

It should return the following before you begin your Lab 7 solution:

SYSTEM_USER_ID SYSTEM_USER_NAME
-------------- --------------------
	     2 DBA 1
	     3 DBA 2
	     4 DBA 3
	     5 DBA 4

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 list of formal parameters is listed in the table below:

You should first inspect the contact_insert procedure provided to you for setting up the database, which is found here:

/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/contact_insert.SQL

If you take the general advise to write the structures first and put NULL; statements inside the blocks, you can test the general structure by using the DESCRIBE command at the SQL*Plus command line. You can describe the procedure like this:

DESC insert_contact

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

The list of parameters in the procedure should also include all common_lookup_type values. The contact_type, member_type, credit_card_type, address_type, and telephone_type columns store foreign key values found as primary key values in the common_lookup_id column of the common_lookup table. You use these _type values in the WHERE clause of a query to discover the common_lookup_id values that need to be copied as foreign key values into the member_type, credit_card_type, contact_type, address_type, and telephone_type columns.

Unfortunately, users don’t remember surrogate key values too readily. That’s why you must include the pv_user_name in the list of parameters. You use the pv_user_name parameter to find rows that have an equivalent system_user_name value and return the system_user_id value as a foreign key value in the created_by and last_updated_by columns. You can write a SELECT-INTO cursor to retrieve the correct system_user_id since the list of system_user_name values should be unique (the values should be SYSADMIN, DBA 1, DBA 2, DBA 3, or DBA 4).

You should write a dynamic SQL cursor that takes three parameters to return the common_lookup_id values into the program scope:

• cv_table_name
• cv_column_name
• cv_lookup_type

The dynamic SQL cursor should return the correct common_lookup_id value for each of the referenced tables. You can find examples of dynamic SQL cursors on page 85, and a complete section on pages 196 and 197 in the Oracle Database 12c PL/SQL Programming textbook.

You can use for-loops to access the dynamic cursors. You will need one for-loop that accesses each of the type columns and a local variable. There is an example of calling a dynamic cursor on page 197 of the Oracle Database 12c PL/SQL Programming textbook. It looks like this:

13
14
15
16

FOR i IN c(lv_lowend, lv_highend) LOOP
  item := i;
  dbms_output.put_line('Title ['||item.title||']');
END LOOP;

You access a field element, or member of the SELECT-List, with the following type of assignment (shown on page 189 in the textbook):

13
14
15
16

FOR i IN c(lv_lowend, lv_highend) LOOP
  lv_item_type := i.item_type;
  dbms_output.put_line('Title ['||i.item_title||']');
END LOOP;

You should use a SELECT-INTO statement to access the system_user_id column of the system_user table. The WHERE clause uses pv_user_name value to find the correct row in the system_user table, and it should select only one row because the system_user_name values should be unique values. A SELECT-INTO cursor returns a value when only one row is returned and throws an exception when two or more rows are returned (see explicit cursors on pages 185 through 188).

1
2
3
4

SELECT column_name
INTO   local_variable
FROM   table_name
WHERE  column_name = 'uniquely qualifying string';

There is no timestamp parameter for the insert_contact procedure. You can declare a local variable as a constant by assigning it the sysdate value inside the declaration section of the procedure.

After you’ve defined all the elements of your program, the execution section should include the for-loops and the SELECT-INTO cursor before you set a save point. Then, you should insert into the member, contact, address, and telephone tables. You should use the member_s1, contact_s1, address_s1, and telephone_s1 sequence to populate the primary key columns with a call to the pseudo .NEXTVAL value. By inserting the table from the least dependent to the most dependent, you can use the same sequences as foreign key values. You use the pseudo .CURRVAL values to populate foreign key values.

You commit the inserts when all of them succeed, and you should roll back all transactions that have completed successfully when one fails. The roll back requires an exception block that catches any exception. However, you should only add the exception handler when you’ve confirmed that your test case works. If you add the exception block first, it can suppress errors in your INSERT statements and make it harder to debug your code. The exception handler should roll back to the save point you set before the series of INSERT statements in your procedure.

Test Case

You write an anonymous block PL/SQL program to test the insert_contact procedure. You call it with the information provided in the table that lists the insert_contact parameter values.

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.

After you call the insert_contact procedure, 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 = 'Xavier';

It should print the following:

			 ACCOUNT
FULL_NAME		 NUMBER     ADDRESS		   TELEPHONE
------------------------ ---------- ---------------------- --------------
Charles Francis Xavier   SLC-000008 Milbridge, Maine	   (207) 111-1234

The insert_contact procedure the same formal parameter list. The procedure should include all the elements that you used to insert or discover to insert into the member, contact, address, and telephone tables. The list of formal parameters is listed in the table below:

If you take the general advise to write the structures first and put NULL; statements inside the blocks, you can test the general structure by using the DESCRIBE command at the SQL*Plus command line. You can describe the procedure like this:

DESC insert_contact

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

Test Case

You can use the same anonymous block PL/SQL program to test the insert_contact invoker rights procedure. You call it with the information provided in the table that lists the insert_contact parameter values. After you call the insert_contact procedure, 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 = 'Haggerty';

It should print the following:

			 ACCOUNT
FULL_NAME		 NUMBER     ADDRESS		   TELEPHONE
------------------------ ---------- ---------------------- --------------
Maura Jane Haggerty      SLC-000009 Bangor, Maine          (207) 111-1234

The insert_contact function the same formal parameter list. The function should include all the elements that you used to insert or discover to insert into the member, contact, address, and telephone tables.

The CREATE OR REPLACE syntax only lets you replace an object of the same type. This lab steps converts the insert_contact procedure to a function. You need to drop the procedure before you can create the function. Alternatively, you can use the following anonymous block introduced earlier to make your testing re-runnable.

BEGIN
  FOR i IN (SELECT uo.object_type
            ,      uo.object_name
            FROM   user_objects uo
            WHERE  uo.object_name = 'INSERT_CONTACT') LOOP
    EXECUTE IMMEDIATE 'DROP ' || i.object_type || ' ' || i.object_name;
  END LOOP;
END;
/

After you drop the contact_insert procedure, you can create the contact_insert function. The list of formal parameters is listed in the table below:

If you take the general advise to write the structures first and put NULL; statements inside the blocks, you can test the general structure by using the DESCRIBE command at the SQL*Plus command line. You can describe the procedure like this:

DESC insert_contact

It should display the following:

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

Test Case

You need to modify the anonymous block PL/SQL program to test the insert_contact definer rights function because it returns a number. You call it with the information provided in the table that lists the insert_contact parameter values. However, you now need to return the variable from the function. There are two options that are available to you. You can assign the pass-by-value value to a local variable or manage it inside an if-statement comparison.

You need to write the anonymous block to evaluate the return value and print Success! when it succeeds and Failure! when it fails. After running the anonymous block, you can run the following query to verify that you’ve inserted the values from the anonymous block.

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 = 'McDonnell';

It should print the following:

			 ACCOUNT
FULL_NAME		 NUMBER     ADDRESS		   TELEPHONE
------------------------ ---------- ---------------------- --------------
Harriet Mary McDonnell   SLC-000010 Orono, Maine           (207) 111-1234

There are three key steps in accomplishing an object table function. First, you need to identify whether or not it should be parameterized, and when parameterized that you have the correct parameters. Second, you need to write the appropriate cursor inside the object table function. Third, you need to create a SQL object type and collection type that your object table function returns.

The pre-steps to building the contact_ object table function requires you to create the contact_obj object type and the contact_tab collection data types. You can do that as follows:

1. The contact_obj SQL object type should have three elements. They are the first_name, middle_name, and last_name elements, and their size and data types should mirror the equivalent column names in the contact table.
2. The contact_tab SQL collection type should a list of the contact_obj SQL object types (see the introduction to collections section on pages 218-219 if you need to recall what role a list fills).

The get_contact object table function should declare a counter variable, a collection variable (of the contact_tab list), and a non-parameterized cursor against the contact table. The execution block should use a for-loop to read the values from the cursor and translate them into elements of the list before returning the populated list from the function.

You should be able to test the function with the following query (like the example on page 319, or explanation in Appendix C on pages 958-960):

SET PAGESIZE 999
COL full_name FORMAT A24
SELECT first_name || CASE
                       WHEN middle_name IS NOT NULL
                       THEN ' ' || middle_name || ' '
                       ELSE ' '
                     END || last_name AS full_name
FROM   TABLE(get_contact);

It should return the following list:

FULL_NAME
------------------------
Randi Winn
Brian Winn
Oscar Vizquel
Doreen Vizquel
Meaghan Sweeney
Matthew Sweeney
Ian M Sweeney
Clinton Goeffrey Ward
Brandt Henry Ward
Moss Wendy Jane
Gretelz Simon Jonah
Royal Elizabeth Jane
Smith Brian Nathan
Harry Potter
Ginny Potter
James Sirius Potter
Albus Severus Potter
Lily Luna Potter
Charles Francis Xavier
Maura Jane Haggerty
Harriet Mary McDonnell
Charles Francis Xavier
Maura Jane Haggerty
Harriet Mary McDonnell