PL/SQL MCQ (Multiple Choice Questions) With Answers, PL/SQL Online Quiz

1 .

PL/SQL is a______

A)

Build Structured Language

B)

Brick Structured Language

C)

Block Structured Language

D)

Banner Structured Language

Correct Answer : Block Structured Language

Explanation : PL/SQL is a Block Structured Language that enables developers to combine the power of SQL with procedural statements.All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic.

2 .

When was PL/SQL released?

A)

1991

B)

1992

C)

1993

D)

1994

Correct Answer : 1991

Explanation : In 1991, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3.

3 .

What does PL/SQL stand for?

A)

PL/SQL stands for Pattern Language Extension of SQL

B)

PL/SQL stands for Private Language Extension of SQL

C)

PL/SQL stands for Primary Language Extension of SQL

D)

PL/SQL stands for Procedural Language Extension of SQL

Correct Answer : PL/SQL stands for Procedural Language Extension of SQL

Explanation : PL/SQL stands for Procedural Language Extension of SQL.

4 .

What is TRUE about PL/SQL functionalities?

A)

Arrays can be used with it as well as handling exceptions (runtime errors).

B)

Conditions and loops are fundamental elements of procedural languages like PL/SQL.

C)

Various types and variables can be declared, as can procedures and functions, as well as types and variables of those types.

D)

All of the above

Correct Answer : All of the above

Explanation :

TRUE about PL/SQL functionalities :

* Conditions and loops are fundamental elements of procedural languages like PL/SQL.

* Various types and variables can be declared, as can procedures and functions, as well as types and variables of those types.

* Arrays can be used with it as well as handling exceptions (runtime errors).

5 .

Oracle Database's ________ are inherited in PL/SQL.

A)

Security

B)

Portability

C)

Robustness

D)

All of the above

Correct Answer : All of the above

Explanation : Oracle Database's portability, robustness and security are inherited in it.

6 .

A Variable in PL/SQL should not exceed____

A)

20

B)

30

C)

40

D)

50

Correct Answer : 30

Explanation : A Variable in PL/SQL should not exceed 30 characters.

7 .

PL/SQL Variables are by default_____

A)

Upper Case Sensitive

B)

Case Sensitive

C)

Not Case Sensitive

D)

Lower Case Sensitive

Correct Answer : Not Case Sensitive

Explanation : PL/SQL Variables are by default not case sensitive.

8 .

PL/SQL text is made up of lexical units, which are groups of characters and can be classified as_______

A)

Literals

B)

Identifiers

C)

Delimiters

D)

All of the above

Correct Answer : All of the above

Explanation : PL/SQL text is made up of lexical units, which are groups of characters and can be classified as Delimiters, Identifiers, Literals and Comments.

9 .

PL/SQL Variable needs to be declared in the_____

A)

Variable Section

B)

Declaration Section

C)

Initialization Section

D)

None of the above

Correct Answer : Declaration Section

Explanation : PL/SQL Variable needs to be declared in the Declaration Section.

10 .

The correct syntax to declare PL/SQL variable is___

A)

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

B)

variable_name [CONSTANT] datatype [NULL] [:= | DEFAULT initial_value]

C)

datatype [CONSTANT] variable_name [NULL] [:= | DEFAULT initial_value]

D)

datatype [CONSTANT] variable_name [NOT NULL] [:= | DEFAULT initial_value]

Correct Answer : variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Explanation :

The correct syntax to declare PL/SQL variable is :

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

11 .

Which of the following is/are TRUE about PL/SQL Variables?

A)

PL/SQL programs benefit from its use.

B)

There is nothing special about it other than being the name of a storage area.

C)

Variables serve as a means for programmers to temporarily store data during code execution.

D)

All of the above

Correct Answer : All of the above

Explanation :

In the case of PL/SQL Variables :

* PL/SQL programs benefit from its use.

* Variables serve as a means for programmers to temporarily store data during code execution.

* There is nothing special about it other than being the name of a storage area.

12 .

Which of the following is not true about large object data types and in PL/SQL?

A)

CLOB is used to store large blocks of character data in the database.

B)

NCLOB is used to store large blocks of NCHAR data in the database.

C)

BLOB is used to store character data in the database.

D)

BFILE is used to store large binary objects in operating system files outside the database.

Correct Answer : BLOB is used to store character data in the database.

13 .

Which of the following is true about the following PL/SQL CASE statement syntax?

CASE selector
    WHEN 'value1' THEN S1;
    WHEN 'value2' THEN S2;
    WHEN 'value3' THEN S3;
    ...
    ELSE Sn;  -- default case
END CASE;

A)

It is perfectly written.

B)

It is wrongly written.

C)

It is you can specify the literal NULL for all the S expressions and the default Sn.

D)

All the expressions like the selector, the value and the returns values, need not be of the same data type.

Correct Answer : It is perfectly written.

14 .

Which of the following is the correct syntax for creating a VARRAY named grades, which can hold 100 integers, in a PL/SQL block?

A)

TYPE grades VARRAY(100) OF INTEGER;

B)

TYPE grades IS VARRAY(100) OF INTEGER;

C)

TYPE grades IS VARRAY(100) OF INTEGERS;

D)

VARRAY grades IS VARRAY(100) OF INTEGER;

Correct Answer : TYPE grades IS VARRAY(100) OF INTEGER;

15 .

Observe the following code and fill in the blanks :

DECLARE 
   total_rows number(2);
BEGIN
   UPDATE employees
   SET salary = salary + 500;
   IF ____________ THEN
      dbms_output.put_line('no employees selected');
   ELSIF ___________ THEN
      total_rows := _____________;
      dbms_output.put_line( total_rows || ' employees selected ');
   END IF; 
END;

A)

%notfound, %found, %rowcount.

B)

%found, %notfound, %rowcount.

C)

sql%found, sql%notfound, sql%rowcount.

D)

sql%notfound, sql%found, sql%rowcount.

Correct Answer : sql%notfound, sql%found, sql%rowcount.

16 .

Which of the following is not true about PL/SQL records?

A)

Records consist of different fields, similar to a row of a database table.

B)

A PL/SQL record is a data structure that can hold data items of different kinds.

C)

You can create table-based and cursor-based records by using the %ROWTYPE attribute.

D)

None of the above.

Correct Answer : None of the above.

17 .

Which of the following is not true about PL/SQL collections?

A)

Nested tables and Varrays are types of PL/SQL collections.

B)

A collection is an ordered group of elements having different data type.

C)

A collection is an ordered group of elements having the same data type.

D)

Each element is identified by a unique subscript that represents its position in the collection.

Correct Answer : A collection is an ordered group of elements having the same data type.

18 .

Which of the following code is the correct syntax for creating a nested table named salary that will store integer values?

A)

TYPE salary IS TABLE OF INTEGER;

B)

TABLE salary IS NESTED BY INTEGER;

C)

TABLE salary IS INDEXED BY INTEGER;

D)

TYPE salary IS NESTED TABLE OF INTEGER;

Correct Answer : TYPE salary IS TABLE OF INTEGER;

19 .

When ZERO_DIVIDE exception is raised?

A)

This error message appears when a number is zero.

B)

This error message appears when a number is divided by zero.

C)

This error message appears when a number is integrated by zero.

D)

This error message appears when a number is multiplied by zero.

Correct Answer : This error message appears when a number is divided by zero.

20 .

When VALUE_ERROR exception is raised?

A)

This error message appears when a number is divided by zero.

B)

Errors relating to arithmetic, conversion, truncation, or size constraints raise this signal.

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Errors relating to arithmetic, conversion, truncation, or size constraints raise this signal.

21 .

The exception thrown when more than one row is returned from a SELECT INTO statement?

A)

MANY_ROWS

B)

MORE_ROWS

C)

MORE_THAN_ONE_ROW

D)

TOO_MANY_ROWS

Correct Answer : TOO_MANY_ROWS

Explanation : The exception thrown when more than one row is returned from a SELECT INTO statement is TOO_MANY_ROWS.

22 .

A PL/SQL exception, which occurs when a memory error occurs?

A)

ZERO-DIVIDE

B)

VALUE_ERROR

C)

STORAGE_ERROR

D)

MEMORY_ERROR

Correct Answer : STORAGE_ERROR

Explanation : A PL /SQL exception, which occurs when a memory error occurs, is STORAGE_ERROR.

23 .

When SELF_IS_NULL exception is raised?

A)

Errors are raised when dividing by zero is attempted.

B)

Error in PL/SQL occurs when memory is exhausted or corrupt.

C)

It is raised when more than one row is returned by a SELECT INTO statement.

D)

When a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.

Correct Answer : When a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.

Explanation : SELF_IS_NULL exception is raised when a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.

24 .

When ROWTYPE_MISMATCH exception is raised?

A)

Error in PL/SQL occurs when memory is exhausted or corrupt.

B)

Cursors raise this exception when they seek values in variables of incompatible types.

C)

When a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.

D)

None of the above

Correct Answer : Cursors raise this exception when they seek values in variables of incompatible types.

Explanation : Cursors raise the exception ROWTYPE_MISMATCH when they seek values in variables of incompatible types.

25 .

Which exception is raised when there is an internal problem in PL/SQL?

A)

PROGRAM_ERROR

B)

VALUE_ERROR

C)

PROBLEM_ERROR

D)

INTERNAL_ERROR

Correct Answer : PROGRAM_ERROR

Explanation : PROGRAM_ERROR is raised when there is an internal problem in PL/SQL.

26 .

An exception which occurs when attempting to access a database without connecting to it is________

A)

NOT_LOGGED_ON

B)

NOT_LOGGED_IN

C)

NULL_LOGGED-ON

D)

PROGRAM_ERROR

Correct Answer : NOT_LOGGED_ON

Explanation : An exception which occurs when attempting to access a database without connecting to it is NOT_LOGGED_ON.

27 .

When DUP_VAL_ON_INDEX exception does is raised?

A)

An invalid username or password is used to log on to the database by a program.

B)

When character strings are converted to numbers, it is raised if the string did not represent a valid number.

C)

A unique index column with duplicate values is raised when this error occurs.

D)

The error is raised if an attempt is made to close an unopened cursor or perform a cursor operation without permission.

Correct Answer : A unique index column with duplicate values is raised when this error occurs.

Explanation : DUP_VAL_ON_INDEX exception is raised when an attempt is made to close an unopened cursor or perform a cursor operation without permission.

28 .

When COLLECTION_IS_NULL exception is raised?

A)

This error message appears when a number is divided by zero.

B)

If a select into statement fails to return any rows, it raises this error.

C)

A unique index column with duplicate values is raised when this error occurs.

D)

The exception is thrown when an uninitialized nested table or varray is attempted to be populated with collection methods other than exist, or when an element of an uninitialized nested table or varray is attempted to be assigned values.

Correct Answer : The exception is thrown when an uninitialized nested table or varray is attempted to be populated with collection methods other than exist, or when an element of an uninitialized nested table or varray is attempted to be assigned values.

Explanation : COLLECTION_IS_NULL exception is raised when an uninitialized nested table or varray is attempted to be populated with collection methods other than exist, or when an element of an uninitialized nested table or varray is attempted to be assigned values.

29 .

Which exception is rose when there is no other clause in the "WHEN" clause of a CASE statement, and none of the choices in the "WHEN" clause have been selected?

A)

VALUE_ERROR

B)

CASE_NOT_FOUND

C)

INVALID_CURSOR

D)

DUP_VAL_ON_INDEX

Correct Answer : CASE_NOT_FOUND

Explanation : CASE_NOT_FOUND exception is rose when there is no other clause in the "WHEN" clause of a CASE statement, and none of the choices in the "WHEN" clause have been selected.

30 .

When ACCESS_INTO_NULL exception does is raised?

A)

A unique index column with duplicate values is raised when this error occurs.

B)

An automatic assignment of a value to a NULL object raises this exception.

C)

It is raised when more than one row is returned by a SELECT INTO statement.

D)

An invalid username or password is used by a program to connect to a database.

Correct Answer : An automatic assignment of a value to a NULL object raises this exception.

Explanation : ACCESS_INTO_NULL exception is raised when an automatic assignment of a value to a NULL object raises this exception.

31 .

When SELECT INTO returns no rows, which pre-defined exception is raised?

A)

NO_DATA_FOUND

B)

VALUE_ERROR

C)

NOT_LOGGED_ON

D)

ACCESS_INTO_NULL

Correct Answer : NO_DATA_FOUND

Explanation : When SELECT INTO returns no rows, NO_DATA_FOUND pre-defined exception is raised.

32 .

Which of the following clause does not comes in the syntax while raising an exception?

A)

END

B)

WHEN

C)

CLOSE

D)

DECLARE

Correct Answer : CLOSE

Explanation : CLOSE clause doesn't come in the syntax while raising an exception.

33 .

What is the syntax of User-defined exceptions?

A)

EXCEPTION;

B)

DECLARE my-exception;

C)

DECLARE EXCEPTION;

D)

DECLARE my-exception EXCEPTION;

Correct Answer : DECLARE my-exception EXCEPTION;

Explanation : DECLARE my-exception EXCEPTION is the syntax of user-defined exceptions.

34 .

Which of the following is TRUE about User-defined exceptions?

A)

Users can explicitly raise an exception by using a RAISE statement

B)

RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Both (A) and (B)

Explanation : Users can explicitly raise an exception by using a RAISE statement or DBMS_STANDARD.RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.

35 .

Which command is used in order to raise an exception explicitly?

A)

RISE

B)

RAISE

C)

ROSE

D)

RINSE

Correct Answer : RAISE

Explanation : With the help of RAISE command, one can easily raise an exception explicitly.

36 .

Which of the following is/are an/the type(s) of exceptions?

A)

User-defined

B)

System-defined

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Both (A) and (B)

Explanation : System-defined and User-defined exceptions are both the types of exceptions.

37 .

How many types of exceptions are there?

A)

2

B)

3

C)

4

D)

5

Correct Answer : 2

Explanation : There are 2 types of exceptions.

38 .

Errors that are encountered during the execution of the program are referred to as ___________ in PL/SQL.

A)

CURSOR

B)

EXCEPTION

C)

FUNCTION

D)

PROCEDURE

Correct Answer : EXCEPTION

Explanation : Errors that are encountered during the execution of the program are referred to as exceptions in PL/SQL.

39 .

The term _________ refers to a declaration with a size, scale, or precision limit.

A)

Constant decision

B)

Constant declaration

C)

Constrained decision

D)

Constrained declaration

Correct Answer : Constrained declaration

Explanation : The term constrained declaration refers to a declaration with a size, scale, or precision limit.

40 .

Which of the following is TRUE about Constrained Declaration?

A)

An unconstrained declaration requires equal memory to a constrained declaration.

B)

An unconstrained declaration requires less memory than a constrained declaration.

C)

An unconstrained declaration requires more memory than a constrained declaration.

D)

None of the above

Correct Answer : An unconstrained declaration requires more memory than a constrained declaration.

41 .

What is/are the Naming Rules for the PL/SQL Variables?

A)

An ASCII letter must be the first letter in the variable name.

B)

Optionally, the variable can be specified as NOT NULL.

C)

In the case of variables, make sure you use easy to read, easy to understand characters after the first character, such as underscore (_) or dollar sign ($).

D)

All of the above

Correct Answer : All of the above

Explanation :

The Naming Rules for the PL/SQL Variables states that :

An ASCII letter must be the first letter in the variable name.

In the case of variables, make sure you use easy to read, easy to understand characters after the first character, such as underscore (_) or dollar sign ($).

Optionally, the variable can be specified as NOT NULL.

42 .

In order to initialize a variable with the value other than NULL, it can be done using which method?

A)

The DEFAULT Keyword

B)

The Assignment Operator

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Both (A) and (B)

Explanation : In order to initialize a variable with the value other than NULL, it can be done using the Assignment Operator and the DEFAULT Keyword both.

43 .

To get the server output result and display it into the screen, you need to write____

A)

Set serveroutput On

B)

Set Dbms output On

C)

Set Dbmsoutput On

D)

Set Server Output On

Correct Answer : Set serveroutput On

44 .

What is wrong in the following code snippet?

DECLARE
   x number := 1;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + 1;
      IF x > 10 THEN
         exit;
      END IF;
     dbms_output.put_line('After Exit x is: ' || x);
END;

A)

There is nothing wrong.

B)

There should be an END LOOP statement.

C)

The IF statement is not required.

D)

The exit statement should be in capital letters.

Correct Answer : There should be an END LOOP statement.

45 .

Consider the following code snippet: what will be the output?

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
   END LOOP;
dbms_output.put_line(a);
END;

A)

10

B)

11

C)

29

D)

30

Correct Answer : 10

46 .

Which of the following is a way of passing parameters to PL/SQL subprograms?

A)

Mixed notation

B)

Named notation

C)

Positional notation

D)

All of the above.

Correct Answer : All of the above.

47 .

Which of the following is not among the types of PL/SQL records?

A)

View-based

B)

Table-based

C)

User-defined records

D)

Cursor-based records

Correct Answer : View-based

48 .

The pre-defined exception TOO_MANY_ROWS is raised when

A)

PL/SQL ran out of memory or memory was corrupted.

B)

SELECT INTO statement returns more than one row.

C)

A cursor fetches value in a variable having incompatible data type.

D)

None of the above.

Correct Answer : SELECT INTO statement returns more than one row.

49 .

Which of the following is true about PL/SQL index-by tables?

A)

It is a set of key-value pairs.

B)

The key can be either an integer or a string.

C)

Each key is unique and is used to locate the corresponding value.

D)

All of the above.

Correct Answer : All of the above.

50 .

Which of the following is not true about PL/SQL nested tables?

A)

A nested table can be stored in a database column.

B)

Elements of a nested table could be a %ROWTYPE of any database table.

C)

Declaration of a nested table is similar to declaration of an index-by table along with the INDEX BY clause.

D)

Elements of a nested table could also be %TYPE of any database table field.

Correct Answer : Declaration of a nested table is similar to declaration of an index-by table along with the INDEX BY clause.

51 .

Which of the following is not true about the Constructors?

A)

Every object has a system defined constructor method.

B)

The name of the constructor is same as the object type.

C)

These are functions that return a new object as its value.

D)

None of the above.

Correct Answer : None of the above.

52 .

What is the name of the two variable scopes in PL/SQL?

A)

Local & Global Variable

B)

Letter & Grind Variable

C)

Local & Grind Variable

D)

Letter & Global Variable

Correct Answer : Local & Global Variable

Explanation : The two variable scopes in PL/SQL are Local & Global Variables.

53 .

What is the difference between Local & Global Variables?

A)

Variables in an outer block are defined as local variables since they cannot be accessed from the outside whereas the innermost block declares global variables.

B)

Variables in an inner block are defined as local variables since they cannot be accessed from the outside whereas the outermost block declares global variables.

C)

Variables in an outer block are defined as global variables since they can be accessed from the outside and the innermost block also declares global variables.

D)

Variables in an inner block are defined as global variables since they cannot be accessed from the outside whereas the outermost block declares local variables.

Correct Answer : Variables in an inner block are defined as local variables since they cannot be accessed from the outside whereas the outermost block declares global variables.

54 .

________ are values used in PL/SQL blocks that do not change during execution.

A)

Cursor

B)

Constants

C)

Variables

D)

Functions

Correct Answer : Constants

Explanation : Constants are values used in PL/SQL blocks that do not change during execution.

55 .

PL/SQL Constant is a/an _______ literal value.

A)

built In

B)

In-built

C)

User-defined

D)

None of the above

Correct Answer : User-defined

Explanation : PL/SQL Constant is a User-defined literal value.

56 .

Choose the correct syntax to declare a constant.

A)

datatype CONSTANT constant_name := VALUE;

B)

constant_name CONSTANT datatype =: VALUE;

C)

datatype CONSTANT constant_name =: VALUE;

D)

constant_name CONSTANT datatype := VALUE;

Correct Answer : constant_name CONSTANT datatype := VALUE;

Explanation :

The correct syntax to declare a constant is

constant_name CONSTANT datatype := VALUE;

57 .

What is TRUE about constant_name in PL/SQL Constant?

A)

Exactly like a variable name, it is a name for a constant.

B)

A constant word doesn't change its value and is a reserved word.

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Both (A) and (B)

Explanation :

In case of constant_name in PL/SQL Constant :

Exactly like a variable name, it is a name for a constant.

A constant word doesn't change its value and is a reserved word.

58 .

Which of the following is correct syntax of IF statement?

A)

IF-THEN statement

B)

IF-THEN-ELSE statement

C)

IF-THEN-ELSIF statement

D)

All of the above

Correct Answer : All of the above

Explanation :

Different Syntax of If statements are :

IF-THEN statement

IF-THEN-ELSE statement

IF-THEN-ELSIF statement

IF-THEN-ELSIF-ELSE statement

59 .

IF-THEN syntax is used____

A)

If the condition is TRUE, then you want statements to be executed

B)

Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.

C)

As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.

D)

When condition1 is TRUE, one set of statements will be executed, and when both conditions are FALSE, a different set of statements will be executed.

Correct Answer : If the condition is TRUE, then you want statements to be executed

60 .

IF-THEN-ELSE syntax is used_________

A)

If the condition is TRUE, then you want statements to be executed

B)

Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.

C)

As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.

D)

When condition1 is TRUE, one set of statements will be executed, and when both conditions are FALSE, a different set of statements will be executed.

Correct Answer : Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.

61 .

IF-THEN-ELSIF syntax is used_________

A)

Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.

B)

If the condition is TRUE, then you want statements to be executed

C)

As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.

D)

When condition1 is TRUE, one set of statements will be executed, and when both conditions are FALSE, a different set of statements will be executed.

Correct Answer : As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.

62 .

______ executes the corresponding code whenever a TRUE condition is found. Otherwise, the condition cannot be checked again.

A)

IF-THEN statement

B)

IF-THEN-ELSE statement

C)

IF-THEN-ELSIF statement

D)

IF-THEN-ELSIF-ELSE statement

Correct Answer : IF-THEN-ELSE statement

Explanation : IF-THEN-ELSE executes the corresponding code whenever a TRUE condition is found. Otherwise, the condition cannot be checked again.

63 .

In the absence of a condition, the ELSE part of the ________ statement will be executed.

A)

IF-THEN-ELSE statement

B)

IF-THEN-ELSIF statement

C)

IF-THEN-ELSIF-ELSE statement

D)

None of the above

Correct Answer : IF-THEN-ELSE statement

64 .

Which portion is optional in IF statements?

A)

IF

B)

THEN

C)

Both (A) and (B)

D)

ELSE

Correct Answer : ELSE

65 .

CASE statement uses which keyword to work like IF statement?

A)

IN

B)

AS

C)

INTO

D)

WHEN

Correct Answer : WHEN

Explanation : CASE statement uses WHEN keyword to work like IF statement.

66 .

What are the selectors in case of CASE statement?

A)

Variable

B)

Function

C)

Expression

D)

All of the above

Correct Answer : All of the above

Explanation : The selectors in case of CASE statement are Variable, Function or Expression.

67 .

Evaluation of CASE statement is done________

A)

RIGHT to LEFT

B)

TOP to BOTTOM

C)

BOTTOM to TOP

D)

None of the above

Correct Answer : TOP to BOTTOM

Explanation : Evaluation of CASE statement is done TOP to BOTTOM.

68 .

By using PL/SQL _______, you can repeatedly execute one or more statements over and over again.

A)

Loops

B)

Case

C)

Cursor

D)

Variables

Correct Answer : Loops

Explanation : By using PL/SQL loops, you can repeatedly execute one or more statements over and over again.

69 .

Which clause(s) is/are used to specify when the trigger will get executed?

A)

BEFORE

B)

AFTER

C)

INSTEAD OF

D)

All of the above

Correct Answer : All of the above

Explanation : BEFORE or AFTER or INSTEAD OF clauses are used to specify when the trigger will get executed.

70 .

What does INSERT or UPDATE or DELETE clauses do in Trigger syntax?

A)

TCL Operation is performed

B)

DDL Operation is performed

C)

DML Operation is performed

D)

DCL Operation is performed

Correct Answer : DML Operation is performed

Explanation : INSERT or UPDATE or DELETE clauses performs the DML Operations in Trigger syntax.

71 .

Which of the following is not an advantage of trigger?

A)

Maintains the integrity of referential

B)

Tables are replicated asynchronously

C)

Validating transactions and preventing them from being invalid

D)

Various column values are automatically generated by triggers

Correct Answer : Tables are replicated asynchronously

Explanation : Trigger has this advantage of the tables to be replicated synchronously and not asynchronously.

72 .

Which of the following is database operation?

A)

STARTUP

B)

SHUTDOWN

C)

SERVERERROR

D)

All of the above

Correct Answer : All of the above

Explanation : SERVERERROR, STARTUP, SHUTDOWN, LOGON and LOGOFF are all the database operations.

73 .

In which event(s) trigger is executed?

A)

DDL

B)

DML

C)

Database Operation

D)

All of the above

Correct Answer : All of the above

74 .

Which of the following is TRUE about PL/SQL Trigger?

A)

When certain conditions are met, a trigger stored in a database is triggered.

B)

A trigger is an application that executes or fires automatically when a certain event occurs.

C)

Both (A) and (B)

D)

None of the above

Correct Answer : None of the above

Explanation :

The following points are TRUE about PL/SQL Trigger :

When certain conditions are met, a trigger stored in a database is triggered.

A trigger is an application that executes or fires automatically when a certain event occurs.

75 .

Whenever a specified event occurs, the _______ is automatically triggered by the Oracle engine.

A)

Trigger

B)

View

C)

Cursor

D)

Exception

Correct Answer : Trigger

Explanation : Whenever a specified event occurs, the trigger is automatically triggered by the Oracle engine.

76 .

Which clause is used to close the cursor?

A)

END

B)

CLOSE

C)

STOP

D)

FINISH

Correct Answer : CLOSE

Explanation : CLOSE clause is used to close the cursor.

77 .

Which clause is used to open the cursor?

A)

START

B)

BEGIN

C)

OPEN

D)

INITIATE

Correct Answer : OPEN

Explanation : OPEN is the clause used to open the cursor.

78 .

Which of the following is the correct syntax to fetch the cursor?

A)

FETCH variable_list INTO cursor_name;

B)

FETCH cursor_name INTO variable_list;

C)

INTO variable_list FETCH cursor_name;

D)

INTO cursor_name FETCH variable_list;

Correct Answer : FETCH cursor_name INTO variable_list;

Explanation : FETCH cursor_name INTO variable_list is the correct syntax to fetch the cursor.

79 .

PL/SQL Loops are also known as_______

A)

Iterative Control Statements

B)

Iterative Case Statements

C)

Indentation Case Statements

D)

Indentation Control Statements

Correct Answer : Iterative Control Statements

Explanation : PL/SQL Loops are also known as Iterative Control Statements.

80 .

What is the syntax of PL/SQL Loop?

A)

LOOP
END LOOP;
Sequence of statements;

B)

END LOOP;
Sequence of statements;
LOOP

C)

END LOOP;
LOOP
Sequence of statements;

D)

LOOP
Sequence of statements;
END LOOP;

Correct Answer :

LOOP
Sequence of statements;
END LOOP;

81 .

How many types of PL/SQL Loops are there?

A)

3

B)

6

C)

5

D)

4

Correct Answer : 4

Explanation : PL/SQL provides four kinds of loop statements :

* basic loop
* WHILE loop
* FOR loop
* cursor FOR loop.

82 .

A/An _______ loop in PL/SQL ensures that at least one statement is executed before the loop terminates.

A)

For

B)

Cursor For

C)

While

D)

Exit

Correct Answer : Exit

Explanation : An exit loop in PL/SQL ensures that at least one statement is executed before the loop terminates.

83 .

Which of the following is TRUE while using PL/SQL Exit Loop?

A)

Variables in the loop are incremented.

B)

The loop body should be initialized with a variable

C)

When you are ready to exit the loop, you should use the EXIT WHEN statement.

D)

All of the above

Correct Answer : All of the above

Explanation :

While using PL/SQL Exit Loop :

The loop body should be initialized with a variable

Variables in the loop are incremented.

When you are ready to exit the loop, you should use the EXIT WHEN statement.

84 .

The PL/SQL ________ loop runs a series of statements as long as a condition is true, so it can be used in a series of statements.

A)

For

B)

Basic

C)

While

D)

Cursor For

Correct Answer : While

Explanation : The PL/SQL While loop runs a series of statements as long as a condition is true, so it can be used in a series of statements.

85 .

What is the correct syntax of WHILE Loop?

A)

LOOP statements;   
WHILE <condition>    
END LOOP;

B)

WHILE <condition>   
LOOP statements;   
END LOOP;

C)

END LOOP;
WHILE <condition>   
LOOP statements;

D)

None of the above

Correct Answer :

WHILE <condition>   
LOOP statements;   
END LOOP;

86 .

When you want to execute a series of statements repeatedly, you use the PL/SQL ____ loop.

A)

For

B)

While

C)

Cursor

D)

Cursor For

Correct Answer : For

Explanation : When you want to execute a series of statements repeatedly, you use the PL/SQL for loop.

87 .

The counter in PL/SQL FOR Loop is by default incremented by______

A)

0

B)

1

C)

2

D)

3

Correct Answer : 1

Explanation :

The counter in PL/SQL FOR Loop is by default incremented by 1.

88 .

Which of the following is TRUE while using PL/SQL FOR Loop?

A)

FOR loops can use EXIT WHEN and EXIT statements, but it isn't often used.

B)

It is not necessary to explicitly increment the counter variable since it is incremented by 1.

C)

The counter variable is implicitly declared in the declaration section, so you do not need to declare it explicitly.

D)

All of the above

Correct Answer : All of the above

89 .

PL/SQL ____________ statement initiates the next iteration of a loop, skipping any code in between, by exiting the loop from the reminder in its body either conditionally or unconditionally.

A)

Check

B)

Cursor

C)

Continue

D)

Condition

Correct Answer : Continue

Explanation : PL/SQL Continue statement initiates the next iteration of a loop, skipping any code in between, by exiting the loop from the reminder in its body either conditionally or unconditionally.

90 .

In which Oracle does the PL/SQL Continue Statement is supported?

A)

Oracle 8g

B)

Oracle 9g

C)

Oracle 10g

D)

Oracle 11g

Correct Answer : Oracle 11g

Explanation : Oracle 11g supports PL/SQL Continue statement.

91 .

A _______ statement in PL/SQL allows you to jump from this statement to a specific statement label within the same subprogram within a PL/SQL block.

A)

GOTO

B)

BLINK

C)

JUMP

D)

CONTINUE

Correct Answer : GOTO

Explanation : A GOTO statement in PL/SQL allows you to jump from this statement to a specific statement label within the same subprogram within a PL/SQL block.

92 .

The symbol in which the label_name is encapsulated in PL/SQL GOTO statement is__

A)

( )

B)

[ ]

C)

<<>>

D)

{ }

Correct Answer : <<>>

Explanation : The symbol in which the label_name is encapsulated in PL/SQL GOTO statement is << >>.

93 .

What is/are the correct restriction(s) on GOTO statement?

A)

No control can be transferred between outer blocks and sub-blocks.

B)

A CASE statement WHEN clause or an IF statement clause cannot transfer control to another.

C)

A LOOP statement or sub-block cannot be used as an IF statement, CASE statement, or LOOP statement.

D)

All of the above

Correct Answer : All of the above

Explanation :

The restrictions on the GOTO statement are :

A LOOP statement or sub-block cannot be used as an IF statement, CASE statement, or LOOP statement.

A CASE statement WHEN clause or an IF statement clause cannot transfer control to another.

No control can be transferred between outer blocks and sub-blocks.

A subprogram cannot be controlled by a parent program.

An exception handler cannot take control.

94 .

PL/SQL Procedure consists of_______

A)

Header and Footer

B)

Header and Body

C)

Body and Footer

D)

None of the above

Correct Answer : Header and Body

Explanation : PL/SQL Procedure consists of Header and Body.

95 .

What is TRUE about header?

A)

Parameters and variables are contained in the header of the procedure.

B)

Similar to a general PL/SQL block, the header contains declarations, executions, and exceptions.

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Parameters and variables are contained in the header of the procedure.

96 .

How much number of ways is there to pass the parameters in procedure?

A)

2

B)

3

C)

4

D)

5

Correct Answer : 3

Explanation : There are 3 numbers of ways to pass the parameters in the procedure.

97 .

Which of the following are the pass parameters in procedure?

A)

IN

B)

OUT

C)

INOUT

D)

All of the above

Correct Answer : All of the above

98 .

Which of the following PL/SQL Cursor is automatically generated by Oracle?

A)

Implicit

B)

Explicit

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Implicit

99 .

Which of the following is/are the type(s) of the PL/SQL Cursor?

A)

Implicit

B)

Explicit

C)

Both (A) and (B)

D)

None of the above

Correct Answer : Both (A) and (B)

Explanation : Implicit and Explicit Cursors are the types of PL/SQL Cursor.

100 .

How many types of PL/SQL Cursor are there?

A)

1

B)

2

C)

3

D)

4

Correct Answer : 2

Explanation : There are 2 types of PL/SQL Cursors.

101 .

An SQL ______ refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement.

A)

Cursor

B)

View

C)

Function

D)

Procedure

Correct Answer : Cursor

Explanation : An SQL cursor refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement.

102 .

In the contexts created by Oracle, a cursor represents a _________

A)

Table

B)

Pointer

C)

Function

D)

None of the above

Correct Answer : Pointer

Explanation : In the contexts, create by Oracle, a cursor represents a Pointer.

103 .

Oracle creates _______ when SQL statements are processed.

A)

Context Ids

B)

Content Ids

C)

Content Areas

D)

Context Areas

Correct Answer : Context Areas

Explanation : Oracle creates context areas when SQL statements are processed.

104 .

In order to remove the PL/SQL function, which function is used?

A)

ERASE FUNCTION

B)

DELETE FUNCTION

C)

DROP FUNCTION

D)

REMOVE FUNCTION

Correct Answer : DROP FUNCTION

105 .

It is called ______ when the subprogram calls itself and _______ is the process.

A)

Recursive Call, Recursion

B)

Recursion, Recursive Call

C)

Recursive Call, Recursive Name

D)

Recursive Name, Recursive Call

Correct Answer : Recursive Name, Recursive Call

Explanation : It is called recursive call when the subprogram calls itself and recursion is the process.

106 .

Calling a function requires that the parameters be provided along with the ________, and if the function returns a value, that value can be stored.

A)

Function Name

B)

IF Statement

C)

Rollback Statement

D)

Optional Parameter List

Correct Answer : Function Name

Explanation : Calling a function requires that the parameters be provided along with the function name, and if the function returns a value, that value can be stored.

107 .

The call function returns program control to the _____ after successful completion of the defined task.

A)

Main Row

B)

Main Program

C)

Main Table

D)

Main Database

Correct Answer : Main Program

Explanation : The call function returns program control to the main program after successful completion of the defined task.

108 .

Which of the following is /are TRUE about calling a PL/SQL function?

A)

You need to define a function's purpose when creating it.

B)

Calling a function passes the program control to that function.

C)

In order to utilize a function, you must call it in order to accomplish the defined task.

D)

All of the above

Correct Answer : All of the above

109 .

AS Keyword is used in the PL/SQL function in order to create a__

A)

Identity Function

B)

Standalone Function

C)

Quadratic Function

D)

One to One Function

Correct Answer : Standalone Function

Explanation :

AS keyword is used in the PL/SQL function in order to create a Standalone Function.

110 .

PL/SQL function must contain a_________

A)

GOTO Statement

B)

Follow Statement

C)

Return Statement

D)

NULL Statement

Correct Answer : Return Statement

Explanation : PL/SQL function must contain a Return Statement.

111 .

Which of the following is NOT present in the syntax of PL/SQL function?

A)

[OR REPLACE]

B)

Function_name

C)

Optional Parameter List

D)

None of the above

Correct Answer : None of the above

Explanation : Function_name, [OR REPLACE], Optional Parameter List, IN are all present in the syntax of PL/SQL function.

112 .

What is the difference between PL/SQL Function and PL/SQL Procedure?

A)

PL/SQL Procedure may or may not return the value whereas PL/SQL Function must have to return the value.

B)

PL/SQL function may or may not return the value whereas PL/SQL Procedure must have to return the value.

C)

PL/SQL Function may or may not return the function whereas PL/SQL Procedure must have to return the function.

D)

None of the above

Correct Answer : PL/SQL Procedure may or may not return the value whereas PL/SQL Function must have to return the value.

113 .

Which of the following can be performed in PL/SQL Procedure?

A)

Call

B)

Drop

C)

Create

D)

All of the above

Correct Answer : All of the above

Explanation :

In PL/SQL Procedure, the following functions can be performed :

Call

Drop
Create

114 .

Which of the following cannot be overwritten by procedure?

A)

IN

B)

OUT

C)

INOUT

D)

None of the above

Correct Answer : IN

Explanation : IN pass parameter cannot be overwritten by procedure.

115 .

Which of the following pass parameters can be referenced by procedure?

A)

IN, OUT

B)

IN, INOUT

C)

OUT, INOUT

D)

None of the Above

Correct Answer : IN, INOUT

Explanation : IN and INOUT pass parameters can be reference by procedure.