[F2-FORMATIVE] Formative Assessment 2

A ________ allows us to declare a variable as a record based on a particular table's structure.

%ROWTYPE

Given the code below:

DECLARE

v_emp_record employees%rowtype;

BEGIN

SELECT * INTO v_emp_record FROM employees WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE('Email for ' || _____________.first_name ||

' ' || ____________.last_name);

END;

What is missing in the DBMS_OUTPUT section?

V_EMP_RECORD

Always add exception handlers whenever there is a possibility of an error occurring.

Group of answer choices

True

False

True

Each ________ is consists of a WHEN clause, which specifies an exception name.

HANDLER

Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.

Group of answer choices

True

False

True

The exception section begins with the keyword _______.

EXCEPTION

The RAISE keyword is used in user-defined exception for error notification.

Group of answer choices

True

False

True

Type and record declared in the outer block are visible only in the outer block.

Group of answer choices

True

False

False

The user-defined exceptions are declared within the declarative section and are raised implicitly.

Group of answer choices

True

False

False

When an exception is raised, control immediately shifts to the _______ and the appropriate handler in the exception section is executed.

EXCEPTION SECTION

What keyword is missing in the given syntax of user-define record? ______

TYPE type_name RECORD

(field_declaration[,field_declaration]...);

identifier typename ;

IS

PL/SQL records contain one or more components/fields of any _______ or composite type.

SCALAR

QUESTION WITH IMAGES

https://docs.google.com/document/d/10_q6Eho115iXj64taBqd_Q2eSvJTqaAnt_yDCmpS87k/edit?usp=sharing

In trapping a user-defined exception, these steps must be followed: DECLARE -> RAISE -> __________.

Group of answer choices

Not in the options

Exception Handling

Reference

Identify Exception

Reference

You must include the FOR UPDATE clause in the cursor query so that the rows are unlocked on OPEN.

Group of answer choices

True

False

False

Given the code below:

1. DECLARE

2. CURSOR cur_emps

3. SELECT employee_id, last_name FROM employees;

4. BEGIN

5. FOR v_emp_record IN cur_emps LOOP

6. EXIT WHEN cur_emps%ROWCOUNT > 5;

7. DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);

8. END LOOP;

9. END;

What is the error in line 5?

Group of answer choices

..

Counter variable

No error

Lower bound and upper bound value

Counter variable

Exception handlers are code that defines the recovery actions to be performed when execution-time errors occur.

Group of answer choices

True

False

True

Exception section is mandatory in PL/SQL block.

Group of answer choices

True

False

False

In exception section, the WHEN clause if followed by a condition.

Group of answer choices

True

False

False

Each exception handler is consists of a WHEN clause, which specifies an exception name.

Group of answer choices

True

False

True

Code that defines the recovery actions to be performed when execution-time errors occur.

EXCEPTION HANDLER

The term RAISE in exceptions is the same as handling any error by including corresponding exception handler.

Group of answer choices

True

False

False

The _______ is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.

OTHERS

Given the code below:

DECLARETYPE person_dept IS . . . . . . . .

v_person_dept_rec person_dept;BEGINSELECT e.first_name, e.last_name, d.department_name INTO _____________FROM employees e JOIN departments dON e.department_id = d.department_id WHERE employee_id = 200;DBMS_OUTPUT.PUT_LINE(v_person_dept_rec.first_name || ' ' || v_person_dept_rec.last_name || ' is in the ' || v_person_dept_rec.department_name || ' department.');END;

What is missing in the SELECT section? _________

V_PERSON_DEPT_REC

TYPE and ________ are composite structures.

RECORD

Given the declaration below:

DECLARE

TYPE person_dept IS _________

first_name employees.first_name%TYPE,

last_name employees.last_name%TYPE,

department_name departments.department_name%TYPE;

What is missing in the TYPE section?__________

RECORD

In explicit cursor, you need to fetch each row one at a time.

Group of answer choices

True

False

True

The FETCH statement positions the cursor pointer at the first row.

Group of answer choices

True

False

False

The OPEN will populate the cursor's active set with the result of the SELECT statement in the cursor's definition?

Group of answer choices

True

False

True

The term ________ in exceptions is the same as handling any error by including corresponding exception handler.

TRAPPING

Error in PL/SQL is known as ____________.

EXCEPTION

The OTHERS is mandatory exception-handling clause that traps any exceptions that have not been explicitly handled.

Group of answer choices

True

False

False

The ________ clause is used in user-defined exception to tell the compiler to associate an exception name with a specific Oracle error

Group of answer choices

RAISE_APPLICATION_ERROR

PRAGMA EXCEPTION_INIT

Not in the options

SQLCODE, SQLERRM

PRAGMA EXCEPTION_INIT

The given syntax in declaring a user-define record is correct.

TYPE type_name IS RECORD

(field_declaration[,field_declaration]...);

identifier type_name ;

Group of answer choices

True

False

True

The FETCH will populate the cursor's active set with the result of the SELECT statement in the cursor's definition?

Group of answer choices

True

False

True

Use the ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.

Group of answer choices

True

False

False

The following statements are examples of exception handler.

Entering an expiration date that has passed

Selecting more than one row into a single variablE

Receiving "no rows returned" from a select statement

Group of answer choices

True

False

False

The term TRAP in exceptions is the same as handling any error by including corresponding exception handler.

Group of answer choices

True

False

True

The following statements are examples of exception.

Entering an expiration date that has passed

Selecting more than one row into a single variable

Receiving "no rows returned" from a select statement

Group of answer choices

True

False

True

Each exception handler is consists of a _____ clause, which specifies an exception name.

WHEN

You must include the FOR UPDATE clause in the cursor query so that the rows are locked on _____.

OPEN

What is missing in the given exception syntax? ____

EXCEPTION

WHEN exception1 [OR exception2 . . .]

statement1; statement2;

THEN

The OTHERS is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.

Group of answer choices

True

False

True

The RAISE statement can be used to raise either user-defined or ________ exception.

Group of answer choices

NON-PREDEFINED

PREDEFINED

PREDEFINED

In explicit cursor operations, the set of rows returned by a multiple-row query is called RECORD SET.

Group of answer choices

True

False

False

You can use the EXCEPTION_INIT procedure to return user-defined error messages from stored subprograms.

Group of answer choices

True

False

False

You can use the ________________ procedure to return user-defined error messages from stored subprograms.

Group of answer choices

PRAGMA EXCEPTION_INIT

SQLCODE

RAISE_APPLICATION_ERROR

SQLERRM

RAISE_APPLICATION_ERROR

An exception occurs when an error is discovered during the execution of a program that disrupts the normal operation of the program.

Group of answer choices

True

False

True

Start with the _____ keyword to define a user-defined record structure.

TYPE

The NOWAIT keyword is optional in the FOR UPDATE clause.

Group of answer choices

True

False

True

In explicit cursor operations, the set of rows returned by a multiple-row query is called ________.

Group of answer choices

Active set

Record set

Active Cursor

Cursor

Active set

What are/is the type of exception can be raised using the RAISE statement?

Group of answer choices

Pre-defined, User-defined

User-defined

NON-PREDEFINED

Non-predefined, User-defined

Pre-defined, Non-predefined

Pre-defined, User-defined

Non-predefined errors are raised explicitly.

Group of answer choices

True

False

False

What symbol is used to terminate the TYPE statement?

DECLARE

TYPE person_dept IS . . . . . . . .

END

What keyword is missing in the given syntax of user-define record? ___________

TYPE type_name IS

(field_declaration[,field_declaration]...);

identifier typename ;

RECORD

The cursor defined in the code below is _______.

1. DECLARE

2. CURSOR cur_emps

3. SELECT employee_id, last_name FROM employees;

Group of answer choices

Explicit Cursor

IMPLICIT CURSOR

Explicit Cursor

The given syntax in declaring a user-define record is incorrect.

TYPE type_name IS RECORD

(field_declaration[,field_declaration]...);

identifier type_name ;

Group of answer choices

True

False

False

The ___________ and ___________ are the two types of Oracle Server Errors.

Group of answer choices

Predefined, Non-predefined

Implicit, Explicit

Predefined, Non-predefined

In non-predefined exception, you must reference the ________ within a WHEN clause in the exception-handling section.

Group of answer choices

Oracle associated error#

Declared exception name

Exception name

All the options are possible

Declared exception name

The RAISE_APPLICATION_ERROR can be used in executable and exception section.

Group of answer choices

True

False

True

Each exception handler is consists of a _____ clause, which specifies an exception name.

Group of answer choices

WHEN

IF

Not in the options

CONDITION

WHEN

In exception section, the WHEN clause if followed by _______.

EXCEPTION NAME

There are 2 parameters needed in the pragma declaration of an exception.

Group of answer choices

True

False

True

Pragma declaration is used in declaring non-predefined exceptions.

Group of answer choices

True

False

True

The PRAGMA clause is used in predefined exception to tell the compiler to associate an exception name with a specific Oracle error number.

Group of answer choices

True

False

False

Names for predefined exceptions must be declared in the declaration section.

Group of answer choices

True

False

False

Given the code below, EMPLOYEES table must be specified at the DBMS_OUTPUT section.

DECLARE

v_emp_record employees2%rowtype;

BEGIN

SELECT * INTO v_emp_record FROM employees2 WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE('Email for ' || _____________.first_name || ' ' || ____________.last_name);

END;

Group of answer choices

True

False

False

You must include the ________ clause in the cursor query so that the rows are locked on OPEN.

FOR UPDATE

The NOWAIT keyword is mandatory in the FOR UPDATE clause.

Group of answer choices

True

False

False

The %ROWTYPE cannot be used to declare a record based on another record.

Group of answer choices

True

False

False

Given the code below:

DECLARE

CURSOR cur_emps

SELECT employee_id, last_name FROM employees;

BEGIN

FOR cur_emps IN v_emp_record LOOP

EXIT WHEN cur_emps%ROWCOUNT > 5;

DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);

END LOOP;

END;

Line 5 is valid or invalid?

Group of answer choices

Invalid

Valid

Invalid

Given the code below:

1. DECLARE

2. CURSOR cur_emps

3. SELECT employee_id, last_name FROM employees;

4. BEGIN

5. FOR v_emp_record IN cur_emps LOOP

6. EXIT WHEN cur_emps%ROWCOUNT > 5;

7. DBMS_OUTPUT.PUT_LINE(_____________.employee_id || ' ' || ___________.last_name);

8. END LOOP;

9. END;

What is missing in line#7?

Group of answer choices

V_EMP_RECORD

Cur_emps

V_EMP_RECORD

The ( ) symbol is used to enclose the elements of the TYPE structure.

Group of answer choices

True

False

True

If you omit the ______ keyword, then the Oracle server waits indefinitely until the rows are available.

NOWAIT

When we declare a cursor FOR UPDATE, each row is locked as we open the cursor but does not prevent other users from reading the rows.

Group of answer choices

True

False

True

The given code below declares an explicit cursor. What will cause an error in the code?

DECLARE

CURSOR cur_depts

SELECT * FROM departments WHERE location_id = 1700

ORDER BY department_name;

Group of answer choices

WHERE location_id = 1700

ORDER BY department_name;

SELECT *

IS

IS

The ______ returns character data containing the message associated with the error number.

Group of answer choices

SQLCODE

SQLERRM

SQLERRM

The _______________ is used in non-predefined exception to tell the compiler to associate an exception name with a specific Oracle error

Group of answer choices

PRAGMA EXCEPTION INIT

EXCEPTION INIT

PRAGMA EXCEPTION_INIT

EXCEPTION_INIT

PRAGMA EXCEPTION_INIT

The RAISE statement can be used to raise either user-defined or predefined exception.

Group of answer choices

True

False

False

The %ROWTYPE can be used to declare a record based on another record.

Group of answer choices

True

False

True

The oracle error number, at the PRAGMA EXCEPTION_INIT function, starts with _____.

Group of answer choices

0

1

UNDERSCORE

HYPEN

HYPEN

What is the first step in handing non-predefined exception?

Group of answer choices

Exception name declaration

Pragma declaration

Exception name declaration

The RAISE_APPLICATION_ERROR can be used in:

Group of answer choices

Executable and Exception section

Executable section

Exception section

Declaration and Exception section

Executable and Exception section

Given the code below:

DECLARE

CURSOR cur_emps

SELECT employee_id, last_name FROM employees;

BEGIN

FOR cur_emps IN v_emp_record LOOP

EXIT WHEN cur_emps%ROWCOUNT > 5;

DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);

END LOOP;

END;

What will cause an error in the FOR section?

Group of answer choices

No error

Remove LOOP

Counter declaration

cur_emps IN v_emp_record

cur_emps IN v_emp_record

PL/SQL records contain one or more components/fields of any SCALAR or ______ type.

COMPOSITE

The __________ clause is used in conjunction with the FOR UPDATE clause to refer to the most recently fetched row in an explicit cursor.

WHERE CURRENT OF

Given the code below, EMPLOYEES%ROWTYPE is missing in the declaration section to declare v_emp_record as type record of EMPLOYEES table.

DECLARE

v_emp_record _______________;

Group of answer choices

True

False

True

PL/SQL records contain one or more components/fields of any scalar or composite type.

Group of answer choices

True

False

True

The declared non-predefined exception is raised ____________.

Group of answer choices

IMPLICITLY

EXPLICITLY

IMPLICITLY

The SQLCODE function returns the numeric value for the error code.

Group of answer choices

True

False

True

An exception handler for a particular exception must contain only one statement.

Group of answer choices

True

False

False

The __________ keyword is used in user-defined exception for error notification.

RAISE

The ___________ contains the exceptions handlers.

EXCEPTION SECTION

Handle named exceptions whenever possible, instead of using ______ in exception handlers.

OTHERS

Always add ________ whenever there is a possibility of an error occurring.

EXCEPTION HANDLER

The following statements are examples of ________________.

Entering an expiration date that has passed

Selecting more than one row into a single variable

Receiving "no rows returned" from a select statement

EXCEPTION

When code does not work as expected, PL/SQL raises an exception handler.

Group of answer choices

True

False

False

When an exception is raised, control immediately shifts to the exception section and the appropriate handler in the exception section is executed.

Group of answer choices

True

False

True

When an exception is raised, the rest of the execution section of the PL/SQL block is not executed.

Group of answer choices

True

False

True

The RAISE keyword is used by non-predefined exception.

Group of answer choices

True

False

False