PL/SQL   «Prev  Next»

Modifying Nested Tables and Varrays - Quiz

Each question is worth one point. Select the best answer or answers for each question.
 

1. What errors, if any, exist in the following INSERT statement? 
INSERT INTO TABLE (SELECT s.detail_table FROM
SALE_HEADER S 
  WHERE s.sale_id = 350) 
SELECT REF(P), 1, 55
FROM PRODUCT_OBJ_TABLE P
WHERE product_id = 200;

Please select the best answer.
  A. This DML statement is syntactically correct. It does not contain any errors.
  B. The DML statement is incorrect, because there is no record within the SALE_HEADER table with a sale_id of 350.
  C. The error within the DML statement is in the selection criteria for the SALE_HEADER table. You must select the detail_nest table, instead of the detail_table table.
  D. The error within the DML statement is in the selection criteria from the PRODUCT_OBJ_TABLE table; there is no record with a product_id of 200.

2. What errors, if any, exist in the following UPDATE statement? 
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array = ('1-800-234-5678', '1-888-987-6543')
WHERE cust_id = 25;

Please select the best answer.
  A. The only error is within the SET clause. The column name must be used instead of the array object name.
  B. The statement is syntactically correct. It does not contain any errors.
  C. Oracle does not allow you to update a varray, so this statement is incorrect.
  D. There are two errors. One is within the SET clause: The column name must be used instead of the array object name. The other is that data must be specified when specifying the new values for the varray, the varray column name, and, within parentheses.

3. What errors, if any, exist in the following statement? 
DELETE TABLE(SELECT d.detail_nest
FROM SALE_HEADER d 
  WHERE d.sale_id = 350) p 
WHERE d.sale_id = 20;

Please select the best answer.
  A. Oracle does not allow deletion of the records from a nested table, so this statement is incorrect.
  B. The second WHERE clause within this statement is incorrect because you must specify a column name from the nested table and not from the parent table.
  C. There is no record within the SALE_HEADER table with a sale_id of 350, so this statement is incorrect.
  D. This statement is syntactically correct. It does not contain any errors.

4. What errors, if any, exist in this PL/SQL block? 
DECLARE
var_phone_array phone_array;
BEGIN
 SELECT phone_array into var_phone_array 
 FROM CUSTOMER_OBJ_TABLE 
 WHERE cust_id = 266;
 var_phone_array.extend;
 var_phone_array (var_phone_array.last) := '1-800-336-6199’';
 UPDATE CUSTOMER_OBJ_TABLE
 SET phone_array = var_phone_array
 WHERE cust_id = 26;
 COMMIT;
END;

Please select the best answer.
  A. The error is within the SELECT statement. Instead of the name of the varray object type, the column name within the CUSTOMER_OBJ_TABLE table associated with the varray must be used.
  B. This statement is syntactically correct.
  C. Oracle does not allow extension of varrays, so this statement is incorrect.
  D. There is no record within the CUSTOMER_OBJ_TABLE table with a cust_id of 266, so this statement is incorrect.

Correct answers:

Your Score: 0