7-1.

What SQL syntax is introduced in Oracle8 to create nested table types?

7-2.

True or false?

7-3.

Fixit Later is the System Architect at Simplistic Procurement Systems, and he has executed the following DDL to create a new system:

SQL> CREATE TYPE detail_t AS OBJECT ( detail_no PLS_INTEGER,
  2                                   detail_desc VARCHAR2(30),
  3                                   detail_price NUMBER );
  4  /

Type created.

SQL> CREATE TYPE order_detail_t AS TABLE OF detail_t;
  2  /

Type created.

SQL> CREATE TABLE orders
  2  (order_no     PLS_INTEGER,
  3   order_desc   VARCHAR2(30),
  4   order_details order_detail_t )
  5  NESTED TABLE order_details STORE AS order_detail_store;

Table created.

Answer the following questions about Mr. Later’s design:

7-4.

Knowitall Andthensome has been given the task of creating data structures to record questions and answers for a series of tests given to students. The name of each test, as well as the questions and correct answers, must be stored. How might this be done using a nested table?

7-5.

Which of the following statements regarding the use of nested table types as nested tables are true and which are false?

7-6.

How do you declare a variable for a nested table type?

7-7.

What is a default constructor?

7-8.

Declare and initialize to NULL a variable for each of the following datatypes:

CREATE TYPE a_blob_t IS TABLE OF BLOB;
/
CREATE TYPE a_number_t IS TABLE OF NUMBER(10);
/
CREATE TYPE a_detail_t IS OBJECT ( detail_no    NUMBER(10),
                                   some_blobs   a_blob_t,
                                   some_numbers a_number_t );
/

7-9.

True or false?

7-10.

When these commands are executed in SQL*Plus or a similar interface, what is the output?

CREATE TYPE temperature_t AS TABLE OF NUMBER;
/

CREATE TABLE hourly_temperatures
(temperature_date DATE,
 temperatures temperature_t)
NESTED TABLE temperatures STORE AS temperature_store;

SELECT COUNT(*)
  FROM hourly_temperatures
/

SELECT COUNT(*)
  FROM temperature_store
/

7-11.

Assuming the same types and declarations shown in 7-10, what happens when a user issues the following SELECT statement?

SELECT COUNT(*)
  FROM temperature_store
/

7-12.

Knowitall Andthensome passed his MCSE exam by answering question number 1 as 10, question 2 as 20, question 3 as 30, question 4 as 40, and question 5 as 50. Given the following type definitions, create a single SQL statement that inserts Knowitall’s responses into the student_answers table:

CREATE TYPE answer_t AS OBJECT ( question_no NUMBER(10),
                                 answer_no   NUMBER(10));

CREATE TYPE answer_list_t AS TABLE OF answer_t;

CREATE TABLE student_answers (
   student_id INTEGER,
   exam_date DATE,
   exam_id INTEGER,
   student_answer_values answer_list_t)
NESTED TABLE student_answer_values
STORE AS student_answer_store;

7-13.

Now that there is an entry in the student_answers, what syntax is required to query all fields in the student_answers table? All fields in the nested table?

7-14.

Continuing with the student_answers example, what SQL syntax is required to add another answer?

7-15.

What is the SQL syntax to delete the entry just added?

7-16.

An Oracle error is lurking within this code snippet. Can you spot and fix it?

BEGIN
  /*
    || Insert an exam entry
  */
  INSERT INTO student_answers
     (student_id,
      exam_date,
      exam_id,
      student_answer_values)
  VALUES
     (100,
      SYSDATE,
      100,
      NULL);
  /*
    || Insert a single answer for the entry just created
  */
  INSERT INTO THE ( SELECT student_answer_values
                      FROM student_answers
                     WHERE student_id = 100
                       AND TRUNC(exam_date) = TRUNC(SYSDATE)
                       AND exam_id = 100 )
  VALUES (answer_t(1,10));
END;

7-17.

Identify each of the following nested table methods:

7-18.

True or false (assume x is a nested table variable)?

7-19.

Supply the missing datatype for v_answers:

DECLARE
  -- cursor to finally get some answers
  CURSOR curs_get_answers IS
     SELECT student_answer_values
       FROM student_answers;
  v_answers <data type required>;

BEGIN
  -- I want some answers!
  OPEN curs_get_answers;
  FETCH curs_get_answers INTO v_answers;
  CLOSE curs_get_answers;

  -- display the last value if the first
  -- exists?
  IF v_answers.EXISTS(v_answers.FIRST) THEN
    DBMS_OUTPUT.PUT_LINE(v_answers.LAST);
  END IF;

  -- add an element
  v_answers.EXTEND;
END;

7-20.

This code does not execute because of an error lurking within. What line causes the failure and why?

DECLARE
  v_answers_a answer_list_t := answer_list_t();
  v_answers_b answer_list_t := answer_list_t();
  v_answers_c answer_list_t;
  v_answers_d answer_list_t := answer_list_t();

BEGIN
  v_answers_a.EXTEND;
  v_answers_a(1) := answer_t(1,10);

  v_answers_b.EXTEND;
  v_answers_b(1) := answer_t(1,10);

  v_answers_c.EXTEND;
  v_answers_c(1) := answer_t(1,10);

  v_answers_d.EXTEND;
  v_answers_d(1) := answer_t(1,10);
END;

7-21.

What line in this code causes an Oracle error?

DECLARE
  -- declare a collection with no null
  -- answers allowed
  v_no_null_answers answer_list_t NOT NULL;

BEGIN
  -- add an answer
  v_no_null_answers.EXTEND;
  v_no_null_answers(1) := answer_t(1,10);

  -- add a NULL answer
  v_no_null_answers.EXTEND;
  v_no_null_answers(2) := NULL;
END;

7-22.

What can be done to ensure that no NULL values are inserted into a user-defined type?

7-23.

Other than programmatic validation, how can you ensure that entries in a nested table are unique within their corresponding outer-table record?

7-24.

What is the output from this block?

DECLARE
  /*
    || Simple program to demonstrate the
    || interaction of EXTEND, TRIM and DELETE
  */
  v_answers answer_list_t := answer_list_t();
  v_element PLS_INTEGER;

BEGIN
  v_answers.EXTEND;-- line 1
  v_answers(1) := answer_t(1,10);-- line 2
  v_answers.EXTEND(3);-- line 3
  v_answers.EXTEND(10,1);-- line 4

  v_answers.DELETE(1,3);-- line 5
  v_answers.DELETE(7);-- line 6

  v_answers.TRIM(2);-- line 7
  v_answers.TRIM;-- line 8

  DBMS_OUTPUT.PUT_LINE('First Element Is     ' ||
     v_answers.FIRST);
  DBMS_OUTPUT.PUT_LINE('Last Element Is      ' ||
     v_answers.LAST);
  DBMS_OUTPUT.PUT_LINE('Count Of Elements Is ' ||
     v_answers.COUNT);

  v_element := v_answers.FIRST;
  LOOP
    EXIT WHEN v_element IS NULL;
    DBMS_OUTPUT.PUT_LINE(
       'Element ' || v_element ||
       ' = ' || v_answers(v_element).question_no);
    v_element := v_answers.NEXT(v_element);
  END LOOP;

END;

7-25.

An Oracle error arises from within this block. What error message is displayed, and what line causes the error?

DECLARE
  v_answers answer_list_t := answer_list_t();  -- line 1

BEGIN
  v_answers.EXTEND(10);              -- line 2
  v_answers.DELETE(1,99);            -- line 3
  v_answers.TRIM(11);                -- line 4
END;

7-26.

What is the output of this block?

DECLARE
  v_answers answer_list_t := answer_list_t();
BEGIN
  v_answers.EXTEND(10);-- line 1
  v_answers.DELETE(4,5);-- line 2
  v_answers.TRIM(v_answers.COUNT);-- line 3
  DBMS_OUTPUT.PUT_LINE('Remaining Elements = ' ||
     v_answers.COUNT);
END;

7-27.

Using the test_answers and student_answers tables created in previous exercises, write a function to calculate the percentage of questions a particular student got correct for a particular test on a particular day. Use the following function header:

CREATE OR REPLACE FUNCTION calculate_score (
   p_student_id student_answers.student_id%TYPE,
   p_test_id test_answers.test_id%TYPE,
   p_exam_date DATE)
   RETURN NUMBER

7-28.

Write a function that returns TRUE if duplicate answers are found within a passed answer list. Use the following function header:

CREATE OR REPLACE FUNCTION duplicates_exist (
   p_answer_list answer_list_t)
   RETURN BOOLEAN

7-29.

Supply an argument to the DBMS_OUTPUT.PUT_LINE function that raises the “ORA-06533: Subscript beyond count” error:

DECLARE
   v_answers answer_list_t := answer_list_t();
BEGIN
  DBMS_OUTPUT.PUT_LINE(<your answer here>);
END;

7-30.

What are nested table locators and in what situations should they be used?

7-31.

How are nested table locators implemented?

7-32.

How can PL/SQL determine if a nested table is returning a locator?

7-33.

The questions in this section refer to the following tables and types, which implement a rudimentary purchase-order system. The ORDERS table stores a sequential order number along with a short description. The lines of the purchase order are stored in the nested table column ORDER_DETAIL. The TAX_CODES column records the tax codes that apply to the purchase order.

Write a SQL script to create the following structures:

SQL> DESCRIBE orders;
 Name                            Null?    Type
 ------------------------------- -------- ----
 ORDER_NO                        NOT NULL NUMBER
 ORDER_DESC                               VARCHAR2(30)
 ORDER_DETAIL                             ORDER_DETAIL_T
 TAX_CODES                                TAX_CODE_T

SQL> DESCRIBE order_detail_t;
 order_detail_t TABLE OF DETAIL_T
 Name                            Null?    Type
 ------------------------------- -------- ----
 DETAIL_NO                                NUMBER
 DETAIL_DESC                              VARCHAR2(30)
 DETAIL_PRICE                             NUMBER

SQL> DESCRIBE detail_t;
 Name                            Null?    Type
 ------------------------------- -------- ----
 DETAIL_NO                                NUMBER
 DETAIL_DESC                              VARCHAR2(30)
 DETAIL_PRICE                             NUMBER

SQL> DESCRIBE tax_code_t;
 tax_code_t TABLE OF TAX_CODE_DETAIL_T
 Name                            Null?    Type
 ------------------------------- -------- ----
 TAX_CODE                                 VARCHAR2(10)

SQL> DESCRIBE tax_code_detail_t;
 Name                            Null?    Type
 ------------------------------- -------- ----
 TAX_CODE                                 VARCHAR2(10)

SQL> DESCRIBE order_detail;
 Name                            Null?    Type
 ------------------------------- -------- ----
 DETAIL_NO                                NUMBER
 DETAIL_DESC                              VARCHAR2(30)
 DETAIL_PRICE                             NUMBER

SQL> DESCRIBE order_tax_codes;
 Name                            Null?    Type
 ------------------------------- -------- ----
 TAX_CODE                                 VARCHAR2(10)

7-34.

Now that we have the data structures for our system, we can create a manage_orders package to encapsulate the required DML operations. The next several exercises ask you to implement the package’s various procedures and functions. In this first exercise, write a function called create_order. The function should use the next sequential order_no to create an new entry in the order; the entry should have a blank description and no detail lines. If the p_default_taxes parameter is TRUE, insert two tax codes for the order, GST and PST (eh!). Otherwise do not include any tax codes. Use the following function header:

FUNCTION create_order (
   p_default_taxes BOOLEAN := FALSE)
RETURN NUMBER;

7-35.

Next, add an update_order procedure to change an order’s description. Use the following header:

PROCEDURE update_order (
   p_order_no ORDERS.ORDER_NO%TYPE,
   p_new_desc ORDERS.ORDER_DESC%TYPE );

7-36.

Next, add an add_details procedure to insert one or more order details for the specified order. Make sure that detail_no is unique within the details being added as well as within the order. Use the following header:

PROCEDURE add_details (
   p_order_no   ORDERS.ORDER_NO%TYPE,
   p_new_detail ORDER_DETAIL_T );

7-37.

Next, add a get_details function, which queries and returns all detail lines for the specified order. Use the following header:

FUNCTION get_details (
   p_order_no ORDERS.ORDER_NO%TYPE )
RETURN ORDER_DETAIL_T;

7-38.

Next, assume that there are only two tax codes available, PST and GST, and both are charged as 7% of every line. Add a function to calculate the total cost of an order by adding up the price of each line and adding any applicable taxes. Use the following header:

FUNCTION calculate_cost (
   p_order_no ORDERS.ORDER_NO%TYPE )
RETURN NUMBER;

7-39.

Finally, write a PL/SQL block to demonstrate the use of the new package by inserting a new order with multiple lines and then calculate its cost.