SECTION 6 LESSON 1 1. The following code declares a PL/SQL record with the same structure as a row of the departments table. True or False? DECLARE v_dept_rec departments%ROWTYPE; ... Mark for Review (1) Points True (*) False Correct 2. Which of the following will successfully create a record type containing two fields, and a record variable of that type? Mark for Review (1) Points TYPE person_type IS RECORD (l_name VARCHAR2(20), gender CHAR(1)); person_rec TYPE person_type; TYPE person_type IS RECORD (l_name VARCHAR2(20), gender CHAR(1)); person_rec person_type; (*) TYPE person_type IS (l_name VARCHAR2(20), gender CHAR(1)); person_rec person_type; TYPE person_type IS (l_name VARCHAR2(20), gender CHAR(1)); person_rec TYPE person_type; Correct 3. Which of the following statements about user-defined PL/SQL records is NOT true? Review (1) Points It is not the same as a row in a database table It can be used as an OUT parameter in a package procedure It can be a component of another PL/SQL record It must contain one or more components, componen ts, but all the components must have scalar datatypes (*) It can be defined as NOT NULL Correct
Mark for
LESSON 2 1. Which of these PL/SQL data structures can NOT store a collection? (1) Points A PL/SQL record (*) An INDEX BY table of records An INDEX BY table indexed by PLS_INTEGER An INDEX BY table indexed by BINARY_INTEGER Incorrect. Refer to Section 11. 2. Which of these PL/SQL data structures could store a complete copy of the employees table, i.e., 20 complete table rows? (1) Points A record An INDEX BY table of records (*) An INDEX BY table An explicit cursor based on SELECT * FROM employees; Incorrect. Refer to Section 11. 3. To declare an INDEX BY table, we must first declare a type and then declare a collection variable of that type. True or False? (1) Points True (*) False Incorrect. Refer to Section 11. 4. What is the largest number of elements (i.e., records) that an INDEX BY table of records can contain? (1) Points 100 4096 32767 Many millions of records because a BINARY_INTEGER or PLS_INTEGER can have a very large value (*) None of the above Incorrect. Refer to Section 11. 5. Which of the following successfully declares an INDEX BY table of records which could be used to store copies of complete rows from the departments table? (1) Points DECLARE TYPE t_depttab IS TABLE OF departments%TYPE INDEX BY BINARY_INTEGER; DECLARE TYPE t_depttab IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
(*) DECLARE TYPE t_depttab IS INDEX BY TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER; DECLARE TYPE t_depttab IS TABLE OF departments%ROWTYPE INDEX BY NUMBER; Correct 6. Which of the following methods can be used to reference elements of an INDEX BY table? (Choose three.) Mark for Review (1) Points (Choose all correct answers) EXISTS (*) FIRST (*) COUNT (*) PREVIOUS DROP Incorrect. Refer to Section 11.
SECTION 7 LESSON 1 1. Only one exception can be raised automatically during one execution of a PL/SQL block. True or False? Mark for Review (1) Points True (*) False Correct 2. Examine the following code. Why does this exception handler not follow good practice guidelines? (Choose two.) DECLARE v_dept_name departments.department_name%TYPE; BEGIN SELECT department_name INTO v_dept_name FROM departments WHERE department_id = 75; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A select returned more than one row'); END; Mark for Review (1) Points (Choose all correct answers)
You should not use DBMS_OUTPUT.PUT_LINE in an exception handler. department_id 75 does not exist in the departments table. The exception handler should test for the named exception NO_DATA_FOUND. (*) The exception handler should COMMIT the transaction. The exception section should include a WHEN TOO_MANY_ROWS exception handler. (*) Correct 3. Which of the following EXCEPTION sections are constructed correctly? (Choose three.) Mark for Review (1) Points (Choose all correct answers) EXCEPTION WHEN NO_DATA_FOUND THEN statement_1; WHEN OTHERS THEN statement_2; END; (*) EXCEPTION WHEN TOO_MANY_ROWS THEN statement_1; END; (*) EXCEPTION WHEN NO_DATA_FOUND THEN statement_1; WHEN NO_DATA_FOUND THEN statement_2; WHEN OTHERS THEN statement_3; END; EXCEPTION WHEN OTHERS THEN statement_1; END; (*) EXCEPTION WHEN OTHERS THEN statement_1; WHEN NO_DATA_FOUND THEN statement_2; END; Correct 4. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block? Mark for Review (1) Points An attempt is made to divide by zero A SELECT statement returns no rows Any other kind of exception that can occur within the block All of the above (*) None of the above Correct 5. Which of the following are NOT good practice guidelines for exception handling? (Choose two.) Mark for Review (1) Points (Choose all correct answers)
Test your code with different combinations of da ta to see what potential errors can happen. Use an exception handler whenever there is any possibility of an error occurring. Include a WHEN OTHERS handler as the first handler in the exception section. (*) Allow exceptions to propagate back to the calling environment. (*) Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. Correct 6. Which of the following is NOT an advantage of including an exception handler in a PL/SQL block? Mark for Review (1) Points Prevents errors from occurring (*) Code is more readable because error-handling routines can be written in the same block in which the error occurred Prevents errors from being propagated back to the calling environment Avoids costly and time-consuming correction of mistakes Incorrect. Refer to Section 7 Lesson 1. 7. The following EXCEPTION section is constructed correctly. True or False? EXCEPTION WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND THEN statement_1; statement_2; WHEN OTHERS THEN statement_3; END; Mark for Review (1) Points True (*) False Correct 8. Which of the following best describes a PL/SQL exception? Mark for Review (1) Points A user enters an invalid password while trying to log on to the database. An error occurs during the execution of the block, which disrupts the normal operation of the program. (*) A compile-time error occurs because the PL/SQL code references a non-existent table. The programmer forgets to declare a cursor while writing the PL/SQL code. Correct
LESSON 2
1. Which kind of error can NOT be handled by PL/SQL? (1) Points
Mark for Review
Syntax errors (*) Predefined Oracle Server errors Non-predefined Oracle Server errors User-defined errors Correct 2. There are no employees whose salary is less than 2000. Which exception handlers would successfully trap the exception which will be raised when the following code is executed? (Choose two.) DECLARE v_mynum NUMBER := 10; v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE salary < 2000; v_mynum := v_mynum / v_count; EXCEPTION ... END; Mark for Review (1) Points (Choose all correct answers) NO_DATA_FOUND ZERO_DIVIDE (*) SQL%ROWCOUNT = 0 OTHERS (*) OTHER Correct 3. An ORA-1400 exception is raised if an attempt is made to insert a null value into a NOT NULL column. DEPARTMENT_ID is the primary key of the DEPARTMENTS table. What will happen when the following code is executed? DECLARE e_not_null EXCEPTION; BEGIN PRAGMA EXCEPTION_INIT(e_not_null, -1400); INSERT INTO departments (department_id, department_name) VALUES(null, 'Marketing'); EXCEPTION WHEN e_not_null THEN DBMS_OUTPUT.PUT_LINE('Cannot be null'); END; Mark for Review (1) Points The exception will be raised and "Cannot be null" will be displayed. The code will not execute because the syntax of PRAGMA EXCEPTION_INIT is wrong.
The code will not execute because PRAGMA EXCEPTION_INIT must be coded in the DECLARE section. (*) The code will not execute because the syntax of the INSERT statement is wrong. Correct 4. Which type(s) of exception MUST be explicitly raised by the PL/SQL programmer?
Mark for
Review (1) Points User-defined exceptions. (*) Predefined Oracle server errors such as TOO_MANY_ROWS. Non-predefined Oracle server errors such as ORA-01203. All of the above. Correct 5. Which of the following is NOT a predefined Oracle Server error? (1) Points NO_DATA_FOUND
Mark for Review
TOO_MANY_ROWS e_sal_too_high EXCEPTION; (*) ZERO_DIVIDE DUP_VAL_ON_INDEX Correct 6. Which of the following best describes a predefined Oracle Server error? Mark for Review (1) Points Has a standard Oracle error number but must be declared and named by the PL/SQL programmer Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*) Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT Is not raised automatically but must be declared and raised explicitly by the PL/SQL programmer Correct 7. How would you trap Oracle Server exception ORA-01403: no data found? (1) Points WHEN NO DATA FOUND THEN ...
Mark for Review
WHEN ORA-01403 THEN ... WHEN NO_DATA_FOUND THEN ... (*) WHEN SQL%ROWCOUNT=0 THEN ... Correct 8. Which one of the following events would implicitly raise an exception? (1) Points The PL/SQL programmer mis-spells the word BEGIN as BEGAN. A database constraint is violated. (*)
Mark for Review
A SELECT statement returns exactly one row. An UPDATE statement modifies no rows. Correct 9. Examine the following code. At Line A, you want to raise an exception if the employee's manager_id is null. What kind of exception is this? DECLARE v_mgr_id employees.manager_id%TYPE; BEGIN SELECT manager_id INTO v_mgr_id FROM employees WHERE employee_id = 100; IF v_mgr_id IS NULL THEN -- Line A END IF; ... Mark for Review (1) Points A predefined Oracle Server exception A constraint violation A non-predefined Oracle server exception A user-defined exception (*) A NO_DATA_FOUND exception Correct 10. What is the correct syntax to associate an exception named EXCEPNAME with the non predefined Oracle Server error ORA-02292? Mark for Review (1) Points PRAGMA EXCEPTION_INIT (excepname, -2292) (*) RAISE_APPLICATION_ERROR (-2292, excepname); SQLCODE (-2292, excepname); WHEN (-2292, excepname) THEN ... Correct 11. Examine the following code. The UPDATE statement will raise an ORA-02291 exception. BEGIN UPDATE employees SET department_id = 45; EXCEPTION WHEN OTHERS THEN INSERT INTO error_log_table VALUES (SQLCODE); END; What will happen when this code is executed? Mark for Review (1) Points
The code will execute and insert error number 02291 into error_log_table. The code will fail because SQLCODE has not been declared. The code will fail because we access error message numbers by using SQLERRNUM, not SQLCODE. The code will fail because we cannot use functions like SQLCODE directly in a SQL statement. (*) Correct 12. A PL/SQL block executes and an Oracle Server exception is raised. Which of the following contains the text message associated with the exception? (1) Points SQLCODE
Mark for Review
SQLERRM (*) SQL%MESSAGE SQL_MESSAGE_TEXT Correct
LESSON 3
1. The following line of code is correct. True or False? RAISE_APPLICATION_ERROR(-21001,'My error message'); (1) Points True
Mark for Review
False (*) Correct 2. You want to display your own error message to the user. What is the correct syntax to do this? Mark for Review (1) Points RAISE_APPLICATION_ERROR(20001, 'My own message'); RAISE_APPLICATION_ERROR('My own message', -20001); RAISE application_error; RAISE_APPLICATION_ERROR (-20001, 'My own message'); (*) Correct 3. What is wrong with the following code? BEGIN UPDATE employees SET salary = 20000 WHERE job_id = 'CLERK'; IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; -- Line A END IF; EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee was updated'); END; Mark for Review (1) Points You cannot use SQL%ROWCOUNT in conditional control statements such as IF or CASE. NO_DATA_FOUND has not been DECLAREd Line A should be: HANDLE NO_DATA_FOUND You cannot explicitly raise predefined Oracle Server errors such as NO_DATA_FOUND. Nothing is wrong, the code will execute correctly. (*) Correct 4. What will be displayed when the following code is executed? DECLARE e_myexcep EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Message 1'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message 2'); EXCEPTION WHEN e_myexcep THEN DBMS_OUTPUT.PUT_LINE('Message 3'); RAISE e_myexcep; DBMS_OUTPUT.PUT_LINE('Message 4'); END; Mark for Review (1) Points Message 1 Message 3 Message 1 Message 3 Message 4 Message 1 Message 2 Message 3 Message 4 The code will not execute because it contains at least one syntax error. The code will execute but will return an unhandled exception to the calling environment. (*) Correct 5. What is the datatype of a user-defined exception? (1) Points BOOLEAN VARCHAR2
Mark for Review
EXCEPTION (*) NUMBER None of the above Correct 6. What is a user-defined exception? Mark for Review (1) Points A predefined Oracle server exception such as NO_DATA_FOUND. An exception which has a predefined Oracle error number but no predefined name. An exception handler which the user (the programmer) includes in the EXCEPTION section. An exception which is not raised automatically by the Oracle server, but must be declared and raised explicitly by the PL/SQL programmer. (*) Correct 7. The following three steps must be performed to use a user-defined exception: - Raise the exception Handle the exception - Declare the exception In what sequence must these steps be performed? Review (1) Points Raise, Handle, Declare Handle, Raise, Declare Declare, Raise, Handle (*) The steps can be performed in any order. Correct 8. How are user-defined exceptions raised ? (1) Points By PRAGMA EXCEPTION_INIT
Mark for Review
By DECLARE e_my_excep EXCEPTION; By RAISE exception_name; (*) None of the above. They are raised automatically by the Oracle server. Correct
LESSON 4
1. What will be displayed when the following code is executed? <> DECLARE v_myvar NUMBER; BEGIN v_myvar := 25; DECLARE v_myvar NUMBER := 100; BEGIN
Mark for
outer.v_myvar := 30; v_myvar := v_myvar / 0; outer.v_myvar := 35; END; v_myvar := 40; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(v_myvar); END; Mark for Review (1) Points 25 30 (*) 35 40 100 Correct 2. There are three employees in department 90. What will be displayed when this code is executed? DECLARE v_last_name employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Message 1'); BEGIN SELECT last_name INTO v_last_name FROM employees WHERE department_id = 90; DBMS_OUTPUT.PUT_LINE('Message 2'); END; DBMS_OUTPUT.PUT_LINE('Message 3'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Message 4'); END; Mark for Review (1) Points Message 1 Message 3 Message 4 Message 1 Message 4 (*) Message 1 An unhandled exception will be propagated back to the calling environment. None of the above
Correct 3. Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False? Mark for Review (1) Points True False (*) Correct 4. What will happen when the following code is executed? DECLARE e_excep1 EXCEPTION; e_excep2 EXCEPTION; BEGIN RAISE e_excep1; EXCEPTION WHEN e_excep1 THEN BEGIN RAISE e_excep2; END; END; Mark for Review (1) Points It will fail to compile because you cannot have a subblock inside an exception section. It will fail to compile because e_excep1 is out of scope in the subblock. It will fail to compile because you cannot declare more than one exception in the same block. It will compile successfully and return an unhandled e_excep2 to the calling environment. (*) Correct 5. What will happen when the following code is executed? DECLARE e_outer_excep EXCEPTION; BEGIN DECLARE e_inner_excep EXCEPTION; BEGIN RAISE e_outer_excep; END; EXCEPTION WHEN e_outer_excep THEN DBMS_OUTPUT.PUT_LINE('Outer raised'); WHEN e_inner_excep THEN DBMS_OUTPUT.PUT_LINE('Inner raised'); END; Mark for Review (1) Points The code will execute successfully and 'Outer Raised' will be displayed. The code will propagate the e_outer_excep back to the calling environment (Application Express).
The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*) The code will fail to compile because e_inner_excep was declared but never RAISEd. Correct 6. Predefined Oracle Server exceptions such as NO_DATA_FOUND can be raised automatically in inner blocks and handled in outer blocks. True or False? (1) Points True (*) False Correct
Mark for Review
