Modifying Nested Tables and Varrays - Quiz Explanation

The answers you selected are indicated below, along with text that explains the correct answers.
 
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.
  Answer C is correct. When selecting a parent record (from the SALE_HEADER table), you must select the column name associated with the nested table, not the nested table's object name, so answer C is correct. Answer A is incorrect because there is an error within the DML statement. Answer B is incorrect, because the error within the DML statement is a syntax error and not a data error. Answer D is incorrect because the error within the DML statement is a syntax error and not a data error.

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.
  Answer D is correct, because it specifies both the errors within the DML statement. Answer A is incorrect because it does not mention the error about the syntax of specifying new data for a varray. Answer C is incorrect because there are errors within the DML statement. Answer D is incorrect because Oracle allows updating of a varray. Oracle does not allow updating of a single element within a varray.

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.
  Answer B is correct The WHERE clause for the nested table must have a nested table column name so answer B is correct. Oracle allows the deletion of nested table records, so answer A is incorrect. Answer C is incorrect because the error to be detected within this statement is a syntax error and not a data error. Answer D is incorrect because this statement has 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.
  Answer A is correct. When a variable is defined, it must be based on the object type. However, when you select from an object table, you must select the column name associated with a varray instead of the varray object name. Hence, answer A is correct. Answer B is incorrect because this statement has errors. Oracle allows the extension of varrays, so answer C is incorrect. Answer D is incorrect because the error to be detected within this statement is a syntax error and not a data error.