PL/SQL Control Structures - PL/SQL (MCQ) questions and answers

Here, you can read PL/SQL Control Structures multiple choice questions and answers with explanation.

1)   In the PL/SQL block below, how many rows will be inserted in the messages table?

DECLARE
v_start_sales NUMBER := 2;
v_end_sales NUMBER := 100;
BEGIN
FOR i IN v_start_sales..v_end_sales LOOP
INSERT INTO messages(msgid)
VALUES v_start_sales;
END LOOP;
END;
- Published on 08 Jul 15

a. 0
b. 99
c. 1
d. 100
Answer  Explanation 

ANSWER: 99

Explanation:
The output will be 99 because the loop will start with 2 as v_start_sales NUMBER is declared as 2. The loop will go on until it reaches 100 as the v_end_sales NUMBER is declared as 100. So the loop will start with 2 and go on till 100. Its total comes to 99.

2)   For which Exception, if a SELECT statement attempts to retrieve data based on its conditions, this exception is raised when no rows satisfy the SELECT criteria?
- Published on 08 Jul 15

a. TOO_MANY_ROWS
b. NO_DATA_FOUND
c. VALUE_ERROR
d. DUP_VAL_ON_INDEX
Answer  Explanation 

ANSWER: NO_DATA_FOUND

Explanation:
The NO_DATA_FOUND exception is raised when the FETCH statement is not able to find the relevant data that we have asked for.

3)   In the SQL Cursor, which attribute is TRUE when a cursor has some remaining rows to fetch, and FALSE when a cursor has no rows left to fetch?
- Published on 08 Jul 15

a. %ROWCOUNT
b. %FOUND
c. %NOTFOUND
d. %ISOPEN
Answer  Explanation 

ANSWER: %FOUND

Explanation:
The %FOUND is a cursor attribute. The return value will be TRUE if the DML statements have an affect on atleast one row and the SELECT...INTO statement also returns atleast one row.
The return value will be FALSE if the DML statements do not affect any row and the SELECT...INTO statement does not return any row.

4)   To handle raised exceptions, you write separate routines called exception handlers.
- Published on 07 Jul 15

a. Yes
b. No
Answer  Explanation 

ANSWER: Yes

Explanation:
Once the exception handle run, the current block stops executing and the next block starts resuming. If there is no other block the control returns back to the host environment.

5)   An Explicit cursor is defined by the program for any query that returns more than one row of data.
- Published on 07 Jul 15

a. True
b. False
Answer  Explanation 

ANSWER: True

Explanation:
An explicit cursor is a user defined cursor and is used for gaining control over the context area. It is mostly used on the SELECT statement which returns more than one row.

6)   Which type of cursor is automatically declared by Oracle every time an SQL statement is executed?
- Published on 07 Jul 15

a. An Implicit
b. An Explicit
c. Both A & B
d. None of the above
Answer  Explanation 

ANSWER: An Implicit

Explanation:
The implicit cursor are automatically created. We cannot control this cursor. This cursor is associated with the DML statements.

7)   Oracle predefined errors are not associated with specific error codes.
- Published on 19 Oct 15

a. True
b. False
Answer  Explanation 

ANSWER: False

Explanation:
No explanation is available for this question!

8)   Which statements execute a sequence of statements multiple times?
- Published on 19 Oct 15

a. EXIT
b. LOOP
c. Both A & B
d. None of the above
Answer  Explanation 

ANSWER: LOOP

Explanation:
A loop helps us in executing a statement or a group of statement multiple times depending on the block of code.

9)   In PL/SQL, a warning or error condition is called an exception.
- Published on 03 Jul 15

a. True
b. False
Answer  Explanation 

ANSWER: True

Explanation:
An error condition during a program is known as an exception. The exception block is used in the program and an appropriate action is taken against the error condition.
There are 2 types of exceptions
1. System-defined exceptions
2. User-defined exceptions.

10)   How many nested IF clauses can be included within an IF clause?
- Published on 03 Jul 15

a. 1
b. 0
c. 15
d. Any number
Answer  Explanation 

ANSWER: Any number

Explanation:
PL/SQL supports the nested IF control structure. Hence, we can include any number of IF clauses.
1 2

Topics

▲