PL/SQL Section 7 Quiz

 1. What is a user-defined exception?

Tandai untuk Ditinjau

(1) Point

An exception which has a predefined Oracle error number but no predefined name.

An exception which is not raised automatically by the Oracle server, but must be declared and raised explicitly by the PL/SQL programmer. (*)

An exception handler which the user (the programmer) includes in the EXCEPTION section.

A predefined Oracle server exception such as NO_DATA_FOUND.

Incorrect. Refer to Section 7 Lesson 3.

---

2. Which of the following will successfully return a user-defined error message?

Tandai untuk Ditinjau

(1) Point

RAISE_APPLICATION_ERROR('Error Raised',-22001);

RAISE_APPLICATION_ERROR(-29001,'Error Raised');

RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)

RAISE_APPLICATION_ERROR('Error Raised',-20257);

Incorrect. Refer to Section 7 Lesson 3.

---

3. You want to display your own error message to the user. What is the correct syntax to do this?

Tandai untuk Ditinjau

(1) Point

RAISE_APPLICATION_ERROR(20001, 'My own message');

RAISE_APPLICATION_ERROR (-20001, 'My own message'); (*)

RAISE application_error;

RAISE_APPLICATION_ERROR('My own message', -20001);

Incorrect. Refer to Section 7 Lesson 3.

---

4. 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?

Tandai untuk Ditinjau

(1) Point

Handle, Raise, Declare

Raise, Handle, Declare

Declare, Raise, Handle (*)

The steps can be performed in any order.

Incorrect. Refer to Section 7 Lesson 3.

---

5. The following code does not violate any constraints and will not raise an ORA-02292 error. What will happen when the code is executed?

BEGIN
    DECLARE
       e_constraint_violation EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_constraint_violation, -2292);
    BEGIN
       DBMS_OUTPUT.PUT_LINE('Inner block message');
    END;
EXCEPTION
    WHEN e_constraint_violation THEN
       DBMS_OUTPUT.PUT_LINE('Outer block message');
END;

Tandai untuk Ditinjau

(1) Point

'Outer block message' will be displayed.

'Inner block message' will be displayed.

The code will fail because line 4 should read: PRAGMA EXCEPTION_INIT(-2292, e_constraint_violation);

The code will fail because the exception is declared in the inner block but is referenced in the outer block. (*)

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?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

7. 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;

Tandai untuk Ditinjau

(1) Point

The code will not execute because the syntax of the INSERT statement 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 PRAGMA EXCEPTION_INIT is wrong.

The exception will be raised and "Cannot be null" will be displayed.

Incorrect. Refer to Section 7 Lesson 2.

---

8. A PL/SQL block executes and an Oracle Server exception is raised. Which of the following contains the text message associated with the exception?

Tandai untuk Ditinjau

(1) Point

SQLCODE

SQLERRM (*)

SQL%MESSAGE

SQL_MESSAGE_TEXT

Incorrect. Refer to Section 7 Lesson 2.

---

9. How would you trap Oracle Server exception ORA-01403: no data found?

Tandai untuk Ditinjau

(1) Point

WHEN ORA-01403 THEN ...

WHEN NO_DATA_FOUND THEN ... (*)

WHEN SQL%ROWCOUNT=0 THEN ...

WHEN NO DATA FOUND THEN ...

Incorrect. Refer to Section 7 Lesson 2.

---

10. Examine the following code fragment. At Line A, you want to raise an exception if the fetched salary value is greater than 30000. How can you do this?

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees
       WHERE employee_id = 100;
    IF v_salary > 30000 THEN
       -- Line A
    END IF;
...

Tandai untuk Ditinjau

(1) Point

Test for WHEN OTHERS in the exception section, because WHEN OTHERS traps all exceptions.

Test for WHEN VALUE_TOO_HIGH in the exception section.

Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.

Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)

11. Examine the followiing code. Which exception handlers would successfully trap the exception which will be raised when this code is executed? (Choose two.)

DECLARE
    CURSOR emp_curs IS SELECT * FROM employees;
    v_emp_rec emp_curs%ROWTYPE;
BEGIN
    FETCH emp_curs INTO v_emp_rec;
    OPEN emp_curs;
    CLOSE emp_curs;
EXCEPTION ...
END;

Tandai untuk Ditinjau

(1) Point

WHEN NO_DATA_FOUND

WHEN CURSOR_NOT_OPEN

WHEN OTHERS(*)

WHEN INVALID_FETCH

WHEN INVALID_CURSOR(*)

Incorrect. Refer to Section 7 Lesson 2.

---

12. 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;

Tandai untuk Ditinjau

(1) Point

The exception handler should COMMIT the transaction.

The exception section should include a WHEN TOO_MANY_ROWS exception handler.(*)

You should not use DBMS_OUTPUT.PUT_LINE in an exception handler.

The exception handler should test for the named exception NO_DATA_FOUND.(*)

department_id 75 does not exist in the departments table.

Incorrect. Refer to Section 7 Lesson 1.

---

13. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?

Tandai untuk Ditinjau

(1) Point

Any other kind of exception that can occur within the block

None of these.

All of these. (*)

A SELECT statement returns more than one row.

A SELECT statement returns no rows.

Incorrect. Refer to Section 7 Lesson 1.

---

14. While a PL/SQL block is executing, more than one exception can occur at the same time. True or False?

Tandai untuk Ditinjau

(1) Point

TRUE

FALSE (*)

Incorrect. Refer to Section 7 Lesson 1.

---

15. The following EXCEPTION section is constructed correctly. True or False?

EXCEPTION
    WHEN NO_DATA_FOUND OR TOO_MANY_ROWS
       THEN statement_1;
       statement_2;
       WHEN OTHERS
          THEN statement_3;
END;

Tandai untuk Ditinjau

(1) Point

TRUE (*)

FALSE