CIT325: Lab 7 Instructions
Lab #7: 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.
- Learn how to write autonomous stored functions and procedures.
- Learn how to write object table functions.
- Learn how to use transaction control language (TCL) commands to manage transaction scope.
- Learn how to call and test stored functions and procedures.
Business Scenario
Web and client-server application developers do not want to manage each insert, update, and delete from an application. That’s why backend application developers build stored functions, procedures, and objects. These stored programs hide the complexity of tables and provide a single interface to write to collections of related tables.
This lab lets you create an insert_contact
procedure. A web form could call the insert_contact
procedure to insert data into a series of table from a single submit button. The insert_contact
procedure becomes a standard application interface (API) to manage an all or nothing insert to four tables (the member
, contact
, address
, and telephone
tables) for a new contact/customer in the video store model. The insert_contact
API also lets you submit the same values for a second new contact/customer by validating an existing entry in the member
table before inserting into the remaining three tables.
More or less, the insert_contact
procedure allows you to create a new member account tied to a single contact/customer or a member account tied to two or more contact/customer. Technically, you would also insert into the street_address table with this video store model but we’ve simplified the exercise to only achieve the core learning objective. It teaches you how to write a backend stored procedure across four related tables.
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.
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
warner_brother
procedure that takes a list of parameters that inserts 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
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; /
- 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
- 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; /
The lab has four parts.
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.
You can find the Video store create_video_store.sql
script in the following directory on the Linux VM distributed for class:
/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction |
The zero step walks you through setting up the video store environment. Click on the Instruction Details link to see the setup instructions.
- [0 points] The nested instructions let you make your test cases automatic because you can run the
create_video_store.sql
script at the beginning of your lab solution.
Instruction Details →
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 |
While you don’t need this to accomplish the first part, you will need to include it at the beginning of your lab. That because the CREATE OR REPLACE
syntax only lets you replace an object of the same type. The third part of the lab converts the insert_contact
procedure to a function. You need this at the beginning to create the initial procedure during iterative testing.
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; / |
Lab 7 has four parts. You create an insert_contact definer rights procedure and test case in the first step. In the second step, you modify the definer rights insert_contact
procedure to be invoker rights procedure. In step 3, you create an autonomous definer rights insert_contact
function by modifying the insert_contact
procedure. Finally in step 4, you create a contact_obj
SQL data type, a contact_tab
SQL collection data type, and a get_contact
object table function.
The web page lets you open each part by leveraging folding. You click on the Instruction Details to see the instructions for each step.
- [10 points] Create an
insert_contact
procedure that writes an all or nothing procedure. The procedure inserts into themember
,contact
,address
, andtelephone
tables, which means you use transaction control language (TCL). TCL principles require you put the database in a transactional state, which applies to any Oracle session/connection by default. A TCL lets you commit after a successful insert into all tables, but TCL requires that you roll back all SQL DML statements with only a single failure. The roll back should limit its scope to the current procedure, which means it rolls back only to the local save point. Failures should only occur when an insert into any one of the four tables fails.
Instruction Details →
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:
Procedure Name: INSERT_CONTACT | |||||
---|---|---|---|---|---|
Parameter Name | Parameter Value | ||||
PV_FIRST_NAME | Charles | ||||
PV_MIDDLE_NAME | Francis | ||||
PV_LAST_NAME | Xavier | ||||
PV_CONTACT_TYPE | CUSTOMER | ||||
PV_ACCOUNT_NUMBER | SLC-000008 | ||||
PV_MEMBER_TYPE | INDIVIDUAL | ||||
PV_CREDIT_CARD_NUMBER | 7777-6666-5555-4444 | ||||
PV_CREDIT_CARD_TYPE | DISCOVER_CARD | ||||
PV_CITY | Milbridge | ||||
PV_STATE_PROVINCE | Maine | ||||
PV_POSTAL_CODE | 04658 | ||||
PV_ADDRESS_TYPE | HOME | ||||
PV_COUNTRY_CODE | 001 | ||||
PV_AREA_CODE | 207 | ||||
PV_TELEPHONE_NUMBER | 111-1234 | ||||
PV_TELEPHONE_TYPE | HOME | ||||
PV_USER_NAME | DBA 2 |
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 |
- [5 points] Modify the
insert_contact
definer rights procedure into an autonomousinsert_contact
invoker rights procedure. You need to add a precompiled instruction, orPRAGMA
, to your procedure to make it an autonomous transaction (refer to page 374 in the Oracle Database 12c PL/SQL Programming textbook for an autonomous transaction precompiled instruction example). The change between a definer rights to invoker rights program will have no impact on running the procedure because you’re working in a single database schema. Like the priorinsert_contact
procedure, this procedure requires you to use transaction control language (TCL).
Instruction Details →
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:
Procedure Name: INSERT_CONTACT | |||||
---|---|---|---|---|---|
Parameter Name | Parameter Value | ||||
PV_FIRST_NAME | Maura | ||||
PV_MIDDLE_NAME | Jane | ||||
PV_LAST_NAME | Haggerty | ||||
PV_CONTACT_TYPE | CUSTOMER | ||||
PV_ACCOUNT_NUMBER | SLC-000009 | ||||
PV_MEMBER_TYPE | INDIVIDUAL | ||||
PV_CREDIT_CARD_NUMBER | 8888-7777-6666-5555 | ||||
PV_CREDIT_CARD_TYPE | MASTER_CARD | ||||
PV_CITY | Bangor | ||||
PV_STATE_PROVINCE | Maine | ||||
PV_POSTAL_CODE | 04401 | ||||
PV_ADDRESS_TYPE | HOME | ||||
PV_COUNTRY_CODE | 001 | ||||
PV_AREA_CODE | 207 | ||||
PV_TELEPHONE_NUMBER | 111-1234 | ||||
PV_TELEPHONE_TYPE | HOME | ||||
PV_USER_NAME | DBA 2 |
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 |
- [5 points] Modify the
insert_contact
invoker rights procedure into an autonomousinsert_contact
definer rights function that returns a number. Theinsert_contact
function should return a zero when successful and a 1 when unsuccessful. The change between a procedure and a function means you now return a value from calling the function.
Instruction Details →
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:
Procedure Name: INSERT_CONTACT | |||||
---|---|---|---|---|---|
Parameter Name | Parameter Value | ||||
PV_FIRST_NAME | Harriet | ||||
PV_MIDDLE_NAME | Mary | ||||
PV_LAST_NAME | McDonnell | ||||
PV_CONTACT_TYPE | CUSTOMER | ||||
PV_ACCOUNT_NUMBER | SLC-000010 | ||||
PV_MEMBER_TYPE | INDIVIDUAL | ||||
PV_CREDIT_CARD_NUMBER | 9999-8888-7777-6666 | ||||
PV_CREDIT_CARD_TYPE | VISA_CARD | ||||
PV_CITY | Orono | ||||
PV_STATE_PROVINCE | Maine | ||||
PV_POSTAL_CODE | 04469 | ||||
PV_ADDRESS_TYPE | HOME | ||||
PV_COUNTRY_CODE | 001 | ||||
PV_AREA_CODE | 207 | ||||
PV_TELEPHONE_NUMBER | 111-1234 | ||||
PV_TELEPHONE_TYPE | HOME | ||||
PV_USER_NAME | DBA 2 |
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 |
- [5 points] This step requires that you create a
get_contact
object table function, which requires acontact_obj
SQL object type and acontact_tab
SQL collection type (page 318). After you define the SQL object type and collection type, you can create theget_contact
object table function (like theget_full_titles
example on pages 318-319).In this assignment, your object table function isn’t parameterized, and should return a complete list of persons from the
contact
table. It should return the names in a first, middle, and last name format with a single white space between each element of the contact’s full name.
Instruction Details →
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:
-
The
contact_obj
SQL object type should have three elements. They are thefirst_name
,middle_name
, andlast_name
elements, and their size and data types should mirror the equivalent column names in the contact table. -
The
contact_tab
SQL collection type should a list of thecontact_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 |
Please submit the lab as one script file with all steps.