Section 6: Exceptions Handling
6.01. Handling Exceptions
1. Which of the following are NOT good practice guidelines for exception handling? (Choose two.)
♦Test your code with different combinations of data 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.
2. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?
♦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
3. Which of the following best describes a PL/SQL exception?
♦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.
4. The following EXCEPTION section is constructed correctly. True or False?
WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND
THEN statement_1;
statement_2;
WHEN OTHERS
THEN statement_3;
END;
♦False
5. Only one exception can be raised automatically during one execution of a PL/SQL block. True or False?
♦True (*)
♦False
6. Which of the following EXCEPTION sections are constructed correctly? (Choose three.)
♦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;
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;
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. (*)
♦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
6.02. Trapping Oracle Server Exceptions
♦TOO_MANY_ROWS
♦e_sal_too_high EXCEPTION; (*)
♦ZERO_DIVIDE
♦DUP_VAL_ON_INDEX
♦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.
3. 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.)
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;
♦ZERO_DIVIDE (*)
♦SQL%ROWCOUNT = 0
♦OTHERS (*)
♦OTHER
4. Examine the following code. The UPDATE statement will raise an ORA-02291 exception.
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?
♦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. (*)
5. What is the correct syntax to associate an exception named EXCEPNAME with the non-predefined Oracle Server error ORA-02292?
♦PRAGMA EXCEPTION_INIT (newname, -2292) (*)
♦RAISE_APPLICATION_ERROR (-2292, excepname);
♦SQLCODE (-2292, excepname);
♦WHEN (-2292, excepname) THEN ?
6. Which kind of error can NOT be handled by PL/SQL?
♦Syntax errors (*)
♦Predefined Oracle Server errors
♦Non-predefined Oracle Server errors
♦User-defined errors
7. 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?
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;
…
♦A predefined Oracle Server exception
♦A constraint violation
♦A non-predefined Oracle server exception
♦A user-defined exception (*)
♦A NO_DATA_FOUND exception
8. 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?
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;
♦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.
9. Which one of the following events would implicitly raise an exception?
♦The PL/SQL programmer mis-spells the word BEGIN as BEGAN.
♦A database constraint is violated. (*)
♦A SELECT statement returns exactly one row.
♦An UPDATE statement modifies no rows.
10. Which of the following best describes a predefined Oracle Server error?
♦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
11. How would you trap Oracle Server exception ORA-01403: no data found?
♦WHEN NO DATA FOUND THEN …
♦WHEN ORA-01403 THEN …
♦WHEN NO_DATA_FOUND THEN … (*)
♦WHEN SQL%ROWCOUNT=0 THEN …
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?
♦SQLCODE
♦SQLERRM (*)
♦SQL%MESSAGE
♦SQL_MESSAGE_TEXT
6.03. Trapping User-Defined Exceptions
1. What is a user-defined exception?
♦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. (*)
2. 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?
♦Raise, Handle, Declare
♦Handle, Raise, Declare
♦Declare, Raise, Handle (*)
♦The steps can be performed in any order.
3. You want to display your own error message to the user. What is the correct syntax to do this?
♦RAISE_APPLICATION_ERROR(20001, ‘My own message’);
♦RAISE_APPLICATION_ERROR(‘My own message’, -20001);
♦RAISE application_error;
♦RAISE_APPLICATION_ERROR (-20001, ‘My own message’) (*)
4. How are user-defined exceptions raised ?
♦By PRAGMA EXCEPTION_INIT
♦By DECLARE e_my_excep EXCEPTION;
♦By RAISE exception_name; (*)
♦None of the above. They are raised automatically by the Oracle server.
5. What will be displayed when the following code is executed?
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;
Message 3
Message 3
Message 4
Message 2
Message 3
Message 4
♦The code will execute but will return an unhandled exception to the calling environment.(*)
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;
♦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. (*)
7. What is the datatype of a user-defined exception?
♦BOOLEAN
♦VARCHAR2
♦EXCEPTION (*)
♦NUMBER
♦None of the above
8. The following line of code is correct. True or False?
RAISE_APPLICATION_ERROR(-21001,’My error message’);
♦True
♦False (*)
6.04. Recognizing the Scope of Variables
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 25;
DECLARE
v_myvar NUMBER := 100;
BEGIN
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;
♦30 (*)
♦35
♦40
♦100
2. Three blocks (outer, middle and inner) are nested inside each other. A variable is declared in the middle block. What is the scope of this variable?
♦Middle block only
♦Middle and inner blocks (*)
♦All three blocks
♦Middle and outer block only
♦None of the above
3. Why will the following code not execute correctly?
v_var1 NUMBER;
BEGIN
DECLARE
v_var2 NUMBER;
BEGIN
v_var1 := ’20’;
END;
v_var2 := 30;
END;
♦v_var1 is a NUMBER variable and cannot be assigned a character string value.
♦v_var2 cannot be referenced in the outer block. (*)
♦The block labels are missing.
♦Nothing is wrong, the code will execute correctly
4. Examine the following code. What is the scope and visibility of the outer block’s v_myvar?
v_myvar VARCHAR2(20);
BEGIN
DECLARE
v_myvar VARCHAR2(20);
BEGIN
…
END:
…
END;
♦It is in scope and visible in both blocks.
♦It is visible in both blocks but in scope only in the outer block.
♦It is in scope in both blocks but visible only in the outer block. (*)
♦False
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;
♦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.
7. In a pair of nested blocks, block labels such as <<label_name>> must be used for both blocks or neither block. True or False?
♦True
♦False (*)
8. Which of the following will display the value ‘Smith’?
<<outer>>
♦DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
<<outer>>
♦DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(<<outer>>.v_name);
END;
END
<<outer>>
♦DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(outer.v_name);
END;
END;(*)
<<outer>>
♦DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
<<inner>>
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
e_excep1 EXCEPTION;
e_excep2 EXCEPTION;
BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN BEGIN
RAISE e_excep2; END;
END;
♦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. (*)
10. Explicit cursors are variables and follow the same scoping and visibility rules as other types of variable. True or False?
♦True (*)
♦False
11. There are three employees in department 90. What will be displayed when this code is executed?
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;
Message 3
Message 4
Message 4(*)
♦False (*)
InfoCad Blog 