Quiz s6s7
Quiz s6s7
Quiz s6s7
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;
...
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? Mark for
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, but all the components must have scalar datatypes (*)
It can be defined as NOT NULL
Correct
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
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;
(*)
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 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.
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;
LESSON 2
1. Which kind of error can NOT be handled by PL/SQL? Mark for Review
(1) Points
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;
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;
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? Mark for Review
(1) Points
NO_DATA_FOUND
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? Mark for Review
(1) Points
WHEN NO DATA FOUND THEN ...
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? Mark for Review
(1) Points
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.
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;
...
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;
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? Mark for Review
(1) Points
SQLCODE
SQLERRM (*)
SQL%MESSAGE
SQL_MESSAGE_TEXT
Correct
LESSON 3
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;
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;
(*)
Correct
5. What is the datatype of a user-defined exception? Mark for Review
(1) Points
BOOLEAN
VARCHAR2
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? Mark for
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 ? Mark for Review
(1) Points
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.
Correct
LESSON 4
<<outer>>
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;
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;
(*)
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;
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? Mark for Review
(1) Points
True (*)
False
Correct