Deck 4: Cursors and Exception Handling

Question 1

Implicit and explicit cursors are considered dynamic because they are associated with specific queries.

Answer 1

False

Question 2

A cursor variable assigns a name to a work area holding a specific result set, whereas an explicit cursor is simply a pointer to a work area in which a query can be processed.

Answer 2

False

Question 3

In PL/SQL, comment text is not executed.

Answer 3

True

Question 4

A FORALL statement can process a group of rows in a DML operation.

Question 5

Bulk processing options can have a detrimental effect on performance.

Question 6

In Oracle, if the UPDATE statement does not affect any rows, an error is raised.

Question 7

Some cursor variables are references or pointers to a specific work area.

Question 8

Explicit cursors are declared automatically for all DML and SELECT statements issued within a PL/SQL block.

Question 9

You can process multiple rows of data from a database by creating explicit cursors.

Question 10

Implicit cursors are considered static because they are associated with specific queries. _________________________

Question 11

Three arguments are required in a PRAGMA EXCEPTION_INIT statement.

Question 12

When referencing SQL attributes, the attribute information always reflects the information from the most recent SQL statement processed.

Question 13

An exception is a mechanism to trap an error that occurs in processing.

Question 14

Implicit cursors are declared and manipulated in the PL/SQL block code for handling a set of rows returned by a SELECT statement. _________________________

Question 15

If the INTO clause of a SELECT statement contains either scalar or record variables and if more than one row is selected, the TOO_MANY_ROWS error is raised. _________________________

Question 16

A SELECT statement within a PL/SQL block has the possibility of raising the NO_DATA_FOUND exception.

Question 17

A(n) PRAGMA statement instructs Oracle to use some additional information provided when compiling and executing a block. _________________________

Question 18

Implicit cursors are declared and manipulated in the PL/SQL block code for handling a set of rows returned by a SELECT statement.

Question 19

A(n) EXCEPTION statement within a PL/SQL block has the possibility of raising the NO_DATA_FOUND or TOO_MANY_ROWS exception. _________________________

Question 20

In PL/SQL, if a SELECT statement returns no rows, Oracle does not return an error.

Question 21

An error raised in the executable section of a block immediately moves processing to the EXCEPTION section of the block in search of an exception handler. _________________________

Question 22

The ____ action used in an explicit cursor processes the query and creates an active set of rows available in the cursor.

A) DECLARE
B) OPEN
C) FETCH
D) CLOSE

Question 23

A(n) ____ represents a work area or section of memory in which an SQL statement is being processed in the Oracle server.

A) collection
B) variable
C) cursor
D) nested table

Question 24

The ____ action used with an explicit cursor creates a named cursor identified by a SELECT statement.

A) OPEN
B) DECLARE
C) FETCH
D) CLOSE

Question 25

A(n) ____ is a mechanism to trap an error that occurs in processing.

A) EXCEPTION
B) UPDATE
C) Exception handler
D) GOTO

Question 26

Because Oracle does not raise an error if a(n) UPDATE does not affect any rows, you must include specific instructions in the block to raise an error if no rows are updated. _________________________

Question 27

____ are declared and manipulated in the PL/SQL block code for handling a set of rows returned by a SELECT statement.

A) Implicit cursors
B) Collections
C) Index-by tables
D) Explicit cursors

Question 28

____ are values passed into the cursor when opened and used in the SELECT statement of the cursor to determine what value it will contain.

A) Collections
B) Subqueries
C) Parameters
D) Variables

Question 29

____ refers to a SELECT statement in a PL/SQL block that retrieves more than one row.

A) NO_DATA_FOUND
B) ZERO_DIVIDE
C) CASE_NOT_FOUND
D) TOO_MANY_ROWS

Question 30

The RAISE_APPLICATION_ERROR is an Oracle built-in procedure that allows the developer to associate their own error number and message to an error. _________________________

Question 31

____ are declared automatically for DML and SELECT statements issued within a PL/SQL block.

A) Implicit cursors
B) Index-by Tables
C) Assignment statements
D) Collections

Question 32

Oracle supplies a set of ____ or names associated with common Oracle errors.

A) predefined exceptions
B) looping constructs
C) exception handlers
D) Searched CASES

Question 33

What can be used to simplify processing each row of a set retrieved from a database?

A) CURSOR
B) CURSOR For LOOP
C) LOOP
D) CURSOR Attribute

Question 34

____ refers to a SELECT statement in a PL/SQL block that retrieves no rows.

A) TOO_MANY_ROWS
B) NO_DATA_FOUND
C) ZERO_DIVIDE
D) DUP_VAL_ON_INDEX

Question 35

An exception that has been declared must be referred to in the RAISE statement or a PL/SQL error will occur. _________________________

Question 36

The ____ section of a PL/SQL block addresses two situations: either an Oracle error is raised or a user-defined error is raised.

A) DECLARE
B) BEGIN
C) EXCEPTION
D) UPDATE

Question 37

The SQLCODE function returns the Oracle error message. _________________________

Question 38

The SQLERRM function returns the Oracle error number. _________________________

Question 39

____ refers to a condition where there is no WHEN clause in the CASE statement.

A) CASE_NOT_FOUND
B) ZERO_DIVIDE
C) DUP_VAL_ON_INDEX
D) NO_DATA_FOUND

Question 40

The ____ action used with an explicit cursor clears the active set of rows and frees the memory area used for the cursor.

A) CLOSE
B) OPEN
C) DECLARE
D) FETCH

Question 41

A(n) ____________________ is one that a developer explicitly raises in the block to enforce a business rule.

Question 42

The ____ option groups rows of queries or DML statements for processing.

A) FORALL
B) COLLECT BULK
C) BULK COLLECT
D) LIMIT

Question 43

Which of the following statements is correct?

A) The value of the SQLCODE can be used directly in an SQL statement.
B) The value of the SQLCODE is assigned to a variable.
C) The value of the SQLCODE can not be assigned to a variable.
D) The value of the SQLERRM can be used directly in an SQL statement.

Question 44

____ refers to an attempted violation of a unique or primary key column constraint.

A) NO_DATA_FOUND
B) TOO_MANY_ROWS
C) DUP_VAL_ON_INDEX
D) CASE_NOT_FOUND

Question 45

The ____ handler should always be the last handler listed in the EXCEPTION section of a block.

A) TOO_MANY_ROWS
B) NO_DATA_FOUND
C) EXIT WHEN
D) WHEN OTHERS

Question 46

If the ____________________ clause of a SELECT statement contains either scalar or record variables and if more than one row is selected, the TOO_MANY_ROWS error is raised.

Question 47

____ is required in a PRAGMA EXCEPTION_INIT statement.

A) LOOP
B) Exception name
C) Predefined error
D) INIT code

Question 48

An attempted violation of a unique or primary key column constraint causes the ____________________ exception.

Question 49

Which of the following statements is correct?

A) An exception that has been declared must be referred to in the RAISE statement, or a PL/SQL error will occur.
B) An exception that has not been declared must be referred to in the RAISE statement, or a PL/SQL error will occur.
C) An exception that has been declared must not be referred to in the RAISE statement, or a PL/SQL error will occur.
D) An exception that has been declared must be referred to in the SELECT statement, or a PL/SQL error will occur.

Question 50

Predefined exceptions are declared in the ____________________ package, which makes these globally available on the system.

Question 51

Because Oracle does not raise an error if a(n) ____________________ does not affect any rows, you must include specific instructions in the block.

Question 52

The ____ option specifies the number of rows to process as a group.

A) FORALL
B) NUMROWS
C) BULK COLLECT
D) LIMIT

Question 53

____ has the possibility of raising the NO_DATA_FOUND or TOO_MANY_ROWS exception.

A) DECLARE
B) SELECT
C) CASE
D) LOOP

Question 54

A(n) ____ is one that a developer explicitly raises in the block to enforce a business rule.

A) predefined exception
B) exception
C) user-defined exception
D) error

Question 55

____ is used to trap errors not specifically addressed in one of the exception handlers.

A) WHERE
B) EXIT WHEN
C) WHEN OTHERS
D) EXCEPTION

Question 56

____ returns the Oracle error message.

A) EXIT WHEN
B) SQLCODE
C) SQLERR
D) SQLERRM

Question 57

A(n) ____________________ statement within a PL/SQL block has the possibility of raising the NO_DATA_FOUND or TOO_MANY_ROWS exception.

Question 58

An attempted division by zero causes the ____________________ exception.

Question 59

The ____ is an Oracle built-in procedure that allows developers to associate their own error number and message to an error.

A) SQLCODE
B) SQLERRM
C) RAISE_APPLICATION_ERROR
D) WHEN OTHERS

Question 60

The ____________________ function returns the Oracle error number.

Question 61

A good programming practice is to include ____________________ in your code to document what is being accomplished throughout the program.

Question 62

What is the difference between an explicit and an implicit cursor?

Question 63

When processing many rows in a query, ____________________ can be used to improve performance.

Question 64

What is the difference between a predefined exception and a user-defined exception?

Question 65

Discuss the meaning of the term exception handler.