Notification texts go here Contact Us Buy Now!
Home
PL/SQL-MCQ

PL/SQL Multiple-Choice Questions (MCQs)

Estimated read time: 61 min
Please wait 0 seconds...
Scroll Down and click on Go to Link for destination
Congrats! Link is Generated

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. Here are top Pl/SQL MCQ's which can help to revise your concepts clearly.We had made this MCQ series from basics to advanced.

\

1. PL/SQL is a –

  1. Brick Structured Language
  2. Block Structured Language
  3. Banner Structured Language
  4. Build Structured Language

Answer: B) Block Structured Language

Explanation:

PL/SQL is a Block Structured Language.

2. What does PL/SQL stand for?

  1. PL/SQL stands for Procedural Language Extension of SQL
  2. PL/SQL stands for Primary Language Extension of SQL
  3. PL/SQL stands for Pattern Language Extension of SQL
  4. PL/SQL stands for Private Language Extension of SQL

Answer: A) PL/SQL stands for Procedural Language Extension of SQL

Explanation:

PL/SQL stands for Procedural Language Extension of SQL.

3. What is TRUE about PL/SQL functionalities?

  1. Conditions and loops are fundamental elements of procedural languages like PL/SQL.
  2. Various types and variables can be declared, as can procedures and functions, as well as types and variables of those types.
  3. Arrays can be used with it as well as handling exceptions (runtime errors).
  4. All of the above

Answer: D) All of the above

Explanation:

TRUE about PL/SQL functionalities –

  1. Conditions and loops are fundamental elements of procedural languages like PL/SQL.
  2. Various types and variables can be declared, as can procedures and functions, as well as types and variables of those types.
  3. Arrays can be used with it as well as handling exceptions (runtime errors).

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

  1. Portability
  2. Robustness
  3. Security
  4. All of the above

Answer: D) All of the above

Explanation:

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

5. PL/SQL text is made up of lexical units, which are groups of characters and can be classified as –

  1. Delimiters
  2. Identifiers
  3. Literals
  4. All of the above

Answer: D) 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.


6. A Variable in PL/SQL should not exceed –

  1. 10
  2. 20
  3. 30
  4. 40

Answer: C) 30

Explanation:

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

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

  1. Variables serve as a means for programmers to temporarily store data during code execution.
  2. PL/SQL programs benefit from its use.
  3. There is nothing special about it other than being the name of a storage area.
  4. All of the above

Answer: D) All of the above

Explanation:

In the case of PL/SQL Variables –

  1. Variables serve as a means for programmers to temporarily store data during code execution.
  2. PL/SQL programs benefit from its use.
  3. There is nothing special about it other than being the name of a storage area.

8. PL/SQL Variables are by default –

  1. Case Sensitive
  2. Upper Case Sensitive
  3. Lower Case Sensitive
  4. Not Case Sensitive

Answer: D) Not Case Sensitive

Explanation:

PL/SQL Variables are by default not case sensitive.

9. PL/SQL Variable needs to be declared in the –

  1. Variable Section
  2. Declaration Section
  3. Initialization Section
  4. None of the above

Answer: B) Declaration Section

Explanation:

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

10. The correct syntax to declare PL/SQL variable is –

  1. variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
  2. datatype [CONSTANT] variable_name [NOT NULL] [:= | DEFAULT initial_value]
  3. variable_name [CONSTANT] datatype [NULL] [:= | DEFAULT initial_value]
  4. datatype [CONSTANT] variable_name [NULL] [:= | DEFAULT initial_value]

Answer: A) 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. The term ________________ refers to a declaration with a size, scale, or precision limit.

  1. Constant declaration
  2. Constrained declaration
  3. Constant decision
  4. Constrained decision

Answer: B) Constrained declaration

Explanation:

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

12. Which of the following is TRUE about Constrained Declaration?

  1. An unconstrained declaration requires more memory than a constrained declaration.
  2. An unconstrained declaration requires less memory than a constrained declaration.
  3. An unconstrained declaration requires equal memory to a constrained declaration.
  4. None of the above

Answer: A) An unconstrained declaration requires more memory than a constrained declaration

Explanation:

An unconstrained declaration requires more memory than a constrained declaration.

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

  1. An ASCII letter must be the first letter in the variable name.
  2. 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 ($).
  3. Optionally, the variable can be specified as NOT NULL.
  4. All of the above

Answer: D) All of the above

Explanation:

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

  1. An ASCII letter must be the first letter in the variable name.
  2. 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 ($).
  3. Optionally, the variable can be specified as NOT NULL.

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

  1. The Assignment Operator
  2. The DEFAULT Keyword
  3. Both A. and B.
  4. None of the above

Answer: C) 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.

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

  1. Local & Grind Variable
  2. Letter & Grind Variable
  3. Local & Global Variable
  4. Letter & Global Variable

Answer: C) Local & Global Variable

Explanation:

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

16. What is the difference between Local & Global Variables?

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Answer: D) 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.

Explanation:

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.

2) PL/SQL Constants, Literals, and If MCQs

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

  1. Variables
  2. Constants
  3. Functions
  4. Cursor

Answer: B) Constants

Explanation:

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

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

  1. In-built
  2. User-defined
  3. Both a and b
  4. None of the above

Answer: B) User-defined

Explanation:

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

19. Choose the correct syntax to declare a constant.

  1. constant_name CONSTANT datatype := VALUE;
  2. datatype CONSTANT constant_name := VALUE;
  3. constant_name CONSTANT datatype =: VALUE;
  4. datatype CONSTANT constant_name =: VALUE;

Answer: A) constant_name CONSTANT datatype := VALUE;

Explanation:

The correct syntax to declare a constant is – 

constant_name CONSTANT datatype := VALUE;

20. What is TRUE about constant_name in PL/SQL Constant?

  1. Exactly like a variable name, it is a name for a constant.
  2. A constant word doesn't change its value and is a reserved word.
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

In case of constant_name in PL/SQL Constant –

  1. Exactly like a variable name, it is a name for a constant.
  2. A constant word doesn't change its value and is a reserved word.

21. A value is assigned to a constant in PL/SQL at the time of –

  1. Initialization
  2. Declaration
  3. Valuation
  4. Numeralization

Answer: B) Declaration

Explanation:

A value is assigned to a constant in PL/SQL at the time of Declaration.

22. __________are values which do not have an identifier and are numeric, character, string, or boolean.

  1. Constants
  2. Literals
  3. Cursor
  4. Variables

Answer: B) Literals

Explanation:

Literals are values which do not have an identifier and are numeric, character, string, or Boolean.

23. Which is the correct example of the literal of type Boolean.

  1. NULL
  2. TRUE
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

NULL, TRUE, etc. Are the examples of the literal of type Boolean.

24. Select the correct statement.

  1. PL/SQL literals are case-sensitive
  2. PL/SQL literals are not case-sensitive
  3. PL-SQL Variables are not case-sensitive
  4. None of the above

Answer: A) PL/SQL literals are case-sensitive

Explanation:

PL/SQL literals are case-sensitive.

25. Which of the following is NOT a type of PL/SQL literal?

  1. Numeric
  2. String
  3. Boolean
  4. Check

Answer: D) Check

Explanation:

Type of PL/SQL literals are Numeric, Character, String, Boolean and Date & Time.

26. Which of the following is Numeric literal?

  1. 1234
  2. 22
  3. 0
  4. All of the above

Answer: D) All of the above

Explanation:

Examples of Numeric literals are 1234, 12.22, 0 etc.

27. Which of the following is NOT a Boolean literal?

  1. TRUE
  2. FALSE
  3. HELLO
  4. NULL

Answer: C) HELLO

Explanation:

HELLO is NOT a Boolean literal. TRUE, FALSE, NULL etc. are Boolean literals.

28. Which of the following is a Character literal?

  1. B
  2. 4
  3. %
  4. All of the above

Answer: D) All of the above

Explanation:

B, 4, % etc. are all Character literals.

29. What programming languages features are used in PL/SQL?

  1. Iterative Statements
  2. Conditional Statements
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

Programming languages features that are used in PL/SQL are iterative and conditional statements.

30. Which of the following is correct syntax of IF statement?

  1. IF-THEN statement
  2. IF-THEN-ELSE statement
  3. IF-THEN-ELSIF statement
  4. All of the above

Answer: D) All of the above

Explanation:

Explanation: Different Syntax of If statements are –

  1. IF-THEN statement
  2. IF-THEN-ELSE statement
  3. IF-THEN-ELSIF statement
  4. IF-THEN-ELSIF-ELSE statement

31. IF-THEN syntax is used –

  1. Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.
  2. If the condition is TRUE, then you want statements to be executed
  3. As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.
  4. 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.

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

Explanation:

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

32. IF-THEN-ELSE syntax is used –

  1. Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.
  2. If the condition is TRUE, then you want statements to be executed
  3. As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.
  4. 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.

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

Explanation:

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

33. IF-THEN-ELSIF syntax is used –

  1. Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.
  2. If the condition is TRUE, then you want statements to be executed
  3. As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.
  4. 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.

Answer: 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.

Explanation:

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

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

  1. IF-THEN statement
  2. IF-THEN-ELSE statement
  3. IF-THEN-ELSIF statement
  4. IF-THEN-ELSIF-ELSE statement

Answer: B) 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.

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

  1. IF-THEN-ELSE statement
  2. IF-THEN-ELSIF statement
  3. IF-THEN-ELSIF-ELSE statement
  4. None of the above

Answer: A) IF-THEN-ELSE statement

Explanation:

In the absence of a condition, the ELSE part of the IF-THEN-ELSE statement will be executed.

36. Which portion is optional in IF statements?

  1. IF
  2. THEN
  3. ELSE
  4. None of the above

Answer: C) ELSE

Explanation:

ELSE and ELSIF portions are optional in IF statements.

3) PL/SQL CASE, LOOP, CONTINUE, GOTO MCQs

37. CASE statement uses which keyword to work like IF statement?

  1. INTO
  2. AS
  3. WHEN
  4. IN

Answer: C) WHEN

Explanation:

CASE statement uses WHEN keyword to work like IF statement.

38. What are the selectors in case of CASE statement?

  1. Variable
  2. Function
  3. Expression
  4. All of the above

Answer: D) All of the above

Explanation:

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

39. Evaluation of CASE statement is done –

  1. RIGHT to LEFT
  2. TOP to BOTTOM
  3. BOTTOM to TOP
  4. None of the above

Answer: B) TOP to BOTTOM

Explanation:

Evaluation of CASE statement is done TOP to BOTTOM.

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

  1. Loops
  2. Cursor
  3. Variables
  4. Case

Answer: A) Loops

Explanation:

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

41. PL/SQL Loops are also known as –

  1. Iterative Case Statements
  2. Iterative Control Statements
  3. Indentation Control Statements
  4. Indentation Case Statements

Answer: B) Iterative Control Statements

Explanation:

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

42. What is the syntax of PL/SQL Loop? 

  1. LOOP
END LOOP;
Sequence of statements;
  2. END LOOP;
LOOP
Sequence of statements;
  3. LOOP
Sequence of statements;
END LOOP;
  4. END LOOP;
Sequence of statements;
LOOP

Answer: C) 

LOOP
Sequence of statements;
END LOOP;

Explanation:

Syntax of PL/SQL Loop is – 

LOOP
Sequence of statements;
END LOOP;

43. How many types of PL/SQL Loops are there?

  1. 3
  2. 4
  3. 5
  4. 6

Answer: B) 4

Explanation:

There are 4 types of PL/SQL loops.

44. Which of the following is correct type of PL/SQL Loop?

  1. While
  2. For
  3. Cursor For
  4. All of the above

Answer: D) All of the above

Explanation:

Types of PL/SQL Loops are –

  1. Basic Loop / Exit Loop
  2. While Loop
  3. For Loop
  4. Cursor For Loop

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

  1. While
  2. For
  3. Cursor For
  4. Exit

Answer: D) Exit

Explanation:

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

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

  1. The loop body should be initialized with a variable
  2. Variables in the loop are incremented.
  3. When you are ready to exit the loop, you should use the EXIT WHEN statement.
  4. All of the above

Answer: D) All of the above

Explanation:

While using PL/SQL Exit Loop –

  1. The loop body should be initialized with a variable
  2. Variables in the loop are incremented.
  3. When you are ready to exit the loop, you should use the EXIT WHEN statement.

47. 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.

  1. While
  2. Cursor For
  3. Basic
  4. For

Answer: A) 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.

48. What is the correct syntax of WHILE Loop? 

  1. WHILE <condition>   
LOOP statements;   
END LOOP;
  2. LOOP statements;   
WHILE <condition>    
END LOOP;
  3. END LOOP;
WHILE <condition>   
LOOP statements;
  4. None of the above

Answer: A) 

WHILE <condition>   
LOOP statements;   
END LOOP;

Explanation:

The correct syntax of WHILE Loop is – 

WHILE <condition>
LOOP statements;   
END LOOP;

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

  1. Cursor For
  2. Cursor
  3. For
  4. While

Answer: C) For

Explanation:

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

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

  1. 0
  2. 1
  3. 2
  4. NULL

Answer: B) 1

Explanation:

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

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

  1. The counter variable is implicitly declared in the declaration section, so you do not need to declare it explicitly.
  2. It is not necessary to explicitly increment the counter variable since it is incremented by 1.
  3. FOR loops can use EXIT WHEN and EXIT statements, but it isn't often used.
  4. All of the above

Answer: D) All of the above

Explanation:

While using PL/SQL FOR Loop –

  1. The counter variable is implicitly declared in the declaration section, so you do not need to declare it explicitly.
  2. It is not necessary to explicitly increment the counter variable since it is incremented by 1.
  3. FOR loops can use EXIT WHEN and EXIT statements, but it isn't often used.

52. 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.

  1. Condition
  2. Cursor
  3. Continue
  4. Check

Answer: C) 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.

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

  1. Oracle 8g
  2. Oracle 9g
  3. Oracle 10g
  4. Oracle 11g

Answer: D) Oracle 11g

Explanation:

Oracle 11g supports PL/SQL Continue statement.

54. 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.

  1. JUMP
  2. GOTO
  3. CONTINUE
  4. BLINK

Answer: B) 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.

55. The symbol in which the label_name is encapsulated in PL/SQL GOTO statement is –

  1. ( )
  2. { }
  3. [ ]
  4. <<>> 

Answer: D) <<>> 

Explanation:

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

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

  1. A LOOP statement or sub-block cannot be used as an IF statement, CASE statement, or LOOP statement.
  2. A CASE statement WHEN clause or an IF statement clause cannot transfer control to another.
  3. No control can be transferred between outer blocks and sub-blocks.
  4. All of the above

Answer: D) All of the above

Explanation:

The restrictions on the GOTO statement are –

  1. A LOOP statement or sub-block cannot be used as an IF statement, CASE statement, or LOOP statement.
  2. A CASE statement WHEN clause or an IF statement clause cannot transfer control to another.
  3. No control can be transferred between outer blocks and sub-blocks.
  4. A subprogram cannot be controlled by a parent program.
  5. An exception handler cannot take control.

4) PL/SQL Procedure MCQs

57. PL/SQL Procedure consists of –

  1. Header and Footer
  2. Body and Footer
  3. Header and Body
  4. None of the above

Answer: C) Header and Body

Explanation:

PL/SQL Procedure consists of Header and Body.

58. What is TRUE about header?

  1. Parameters and variables are contained in the header of the procedure.
  2. Similar to a general PL/SQL block, the header contains declarations, executions, and exceptions.
  3. Both A. and B.
  4. None of the above

Answer: A) Parameters and variables are contained in the header of the procedure

Explanation:

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

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

  1. 1
  2. 2
  3. 3
  4. 4

Answer: C) 3

Explanation:

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

60. Which of the following are the pass parameters in procedure?

  1. IN
  2. OUT
  3. INOUT
  4. All of the above

Answer: D) All of the above

Explanation:

The pass parameters in procedure are –

  1. IN
  2. OUT
  3. INOUT

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

  1. IN, OUT
  2. OUT, INOUT
  3. IN, INOUT
  4. None of the above

Answer: C) IN, INOUT

Explanation:

IN and INOUT pass parameters can be reference by procedure.

62. Which of the following cannot be overwritten by procedure?

  1. IN
  2. OUT
  3. INOUT
  4. None of the above

Answer: A) IN

Explanation:

IN pass parameter cannot be overwritten by procedure.

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

  1. Create
  2. Call
  3. Drop
  4. All of the above

Answer: D) All of the above

Explanation:

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

  1. Create
  2. Call
  3. Drop

5) PL/SQL Function MCQs

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

  1. PL/SQL function may or may not return the value whereas PL/SQL Procedure must have to return the value.
  2. PL/SQL Procedure may or may not return the value whereas PL/SQL Function must have to return the value.
  3. PL/SQL Function may or may not return the function whereas PL/SQL Procedure must have to return the function.
  4. None of the above

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

Explanation:

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

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

  1. Function_name
  2. [OR REPLACE]
  3. Optional Parameter List
  4. None of the above

Answer: D) None of the above

Explanation:

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

66. PL/SQL function must contain a –

  1. Follow Statement
  2. GOTO Statement
  3. Return Statement
  4. NULL Statement

Answer: C) Return Statement

Explanation:

PL/SQL function must contain a Return Statement.

67. AS Keyword is used in the PL/SQL function in order to create a –

  1. Identity Function
  2. Quadratic Function
  3. One to One Function
  4. Standalone Function

Answer: D) Standalone Function

Explanation:

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

68. We can demonstrate which of the following in a PL/SQL function?

  1. Declare
  2. Define
  3. Invoke
  4. All of the above

Answer: D) All of the above

Explanation:

We can demonstrate Declare, Define and Invoke in PL/SQL function.

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

  1. You need to define a function's purpose when creating it.
  2. In order to utilize a function, you must call it in order to accomplish the defined task.
  3. Calling a function passes the program control to that function.
  4. All of the above

Answer: D) All of the above

Explanation:

All the below points are TRUE about calling a PL/SQL function –

  1. You need to define a function's purpose when creating it.
  2. In order to utilize a function, you must call it in order to accomplish the defined task.
  3. Calling a function passes the program control to that function.

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

  1. Main Table
  2. Main Database
  3. Main Program
  4. Main Row

Answer: C) Main Program

Explanation:

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

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

  1. Function Name
  2. Optional Parameter List
  3. IF Statement
  4. Rollback Statement

Answer: A) 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.

72. It is called _____________ when the subprogram calls itself and __________ is the process.

  1. Recursion, Recursive Call
  2. Recursive Call, Recursion
  3. Recursive Name, Recursive Call
  4. Recursive Call, Recursive Name

Answer: B) Recursive Call, Recursion

Explanation:

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

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

  1. REMOVE FUNCTION
  2. DELETE FUNCTION
  3. ERASE FUNCTION
  4. DROP FUNCTION

Answer: D) DROP FUNCTION

Explanation:

In order to remove the PL/SQL function, DROP FUNCTION is used.

6) PL/SQL Cursor MCQs

74. Oracle creates ___________ when SQL statements are processed.

  1. Content Areas
  2. Context Areas
  3. Context Ids
  4. Content Ids

Answer: B) Context Areas

Explanation:

Oracle creates context areas when SQL statements are processed.

75. In the contexts created by Oracle, a cursor represents a _____________.

  1. Function
  2. Table
  3. Pointer
  4. None of the above

Answer: C) Pointer

Explanation:

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

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

  1. Cursor
  2. Function
  3. Procedure
  4. View

Answer: A) 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.

77. How many types of PL/SQL Cursor are there?

  1. 1
  2. 2
  3. 3
  4. 4

Answer: B) 2

Explanation:

There are 2 types of PL/SQL Cursors.

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

  1. Implicit
  2. Explicit
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

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

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

  1. Implicit
  2. Explicit
  3. Both a and b
  4. None of the above

Answer: A) Implicit

Explanation:

Implicit Cursor is automatically generated by Oracle.

80. The Implicit cursors are created in order to process the ____ statements.

  1. DDL
  2. DCL
  3. DML
  4. TCL

Answer: C) DML

Explanation:

The implicit cursors are created in order to process the DML statements such as INSERT, DELETE, UPDATE, etc.

81. Which of the following is/are an/the implicit cursor's attribute(s)?

  1. %FOUND
  2. %ROWCOUNT
  3. %ISOPEN
  4. All of the above

Answer: D) All of the above

Explanation:

%FOUND, %ROWCOUNT, %OPEN and %NOTFOUND are all the implicit cursor's attributes.

82. What is TRUE about %FOUND in PL/SQL Cursor?

  1. If any DML statement, such as INSERT, DELETE or UPDATE, effects one or more rows, or if a SELECT INTO statement returns at least one row, this method returns TRUE.
  2. A SELECT INTO statement or DML statements like INSERT, DELETE, AND UPDATE do not result in any rows being affected.
  3. Because SQL cursors are automatically closed after execution of their associated SQL statements, the function always returns FALSE for implicit cursors.
  4. Results are returned for DML statements such as inserting, deleting, and updating records, or for those returned by a SELECT INTO command.

Answer: A) If any DML statement, such as INSERT, DELETE or UPDATE, effects one or more rows, or if a SELECT INTO statement returns at least one row, this method returns TRUE

Explanation:

In the case of %FOUND, if any DML statement, such as INSERT, DELETE or UPDATE, effects one or more rows, or if a SELECT INTO statement returns at least one row, this method returns TRUE.

83. What is TRUE about %ISOPEN in PL/SQL Cursor?

  1. If any DML statement, such as INSERT, DELETE or UPDATE, effects one or more rows, or if a SELECT INTO statement returns at least one row, this method returns TRUE.
  2. A SELECT INTO statement or DML statements like INSERT, DELETE, AND UPDATE do not result in any rows being affected.
  3. Because SQL cursors are automatically closed after execution of their associated SQL statements, the function always returns FALSE for implicit cursors.
  4. Results are returned for DML statements such as inserting, deleting, and updating records, or for those returned by a SELECT INTO command.

Answer: C) Because SQL cursors are automatically closed after execution of their associated SQL statements, the function always returns FALSE for implicit cursors

Explanation:

In the case of %ISOPEN, Because SQL cursors are automatically closed after execution of their associated SQL statements, the function always returns FALSE for implicit cursors.

84. What is TRUE about %ROWCOUNT in PL/SQL Cursor?

  1. A SELECT INTO statement or DML statements like INSERT, DELETE, AND UPDATE do not result in any rows being affected.
  2. Results are returned for DML statements such as inserting, deleting, and updating records, or for those returned by a SELECT INTO command.
  3. Both A. and B.
  4. None of the above

Answer: B) Results are returned for DML statements such as inserting, deleting, and updating records, or for those returned by a SELECT INTO command

Explanation:

Results are returned for DML statements such as inserting, deleting, and updating records, or for those returned by a SELECT INTO command.

85. For ________ control over the context area, programmers can define explicit cursors.

  1. Greater
  2. Lesser
  3. Equal
  4. None of the above

Answer: A) Greater

Explanation:

For greater control over the context area, programmers can define explicit cursors.

86. In the PL/SQL Block, Explicit cursors are defined in ___________ section.

  1. Initialization
  2. Declaration
  3. End
  4. None of the above

Answer: B) Declaration

Explanation:

In the PL/SQL Block, Explicit cursors are defined in Declaration Section.

87. On the _______ statements, the explicit cursors are created which return rows more than one.

  1. DELETE
  2. CREATE
  3. SELECT
  4. UPDATE

Answer: C) SELECT

Explanation:

On the SELECT statements, the explicit cursors are created which return rows more than one.

88. Which of the following is the correct syntax to create the Explicit Cursor?

  1. IS select_statement cursor_name CURSOR;
  2. CURSOR select_statement IS cursor_name;
  3. CURSOR cursor_name select_statement IS;
  4. CURSOR cursor_name IS select_statement;

Answer: D) CURSOR cursor_name IS select_statement;

Explanation:

CURSOR cursor_name IS select_statement; is the correct syntax to create the Explicit Cursor.

89. There are 4 steps to work on Explicit Cursor:

  1. Declare the cursor to be initialized in memory.
  2. Allocate memory by opening the cursor.
  3. Retrieve data by fetching the cursor.
  4. Release allocated memory by closing the cursor.

Select the correct order of the above 4 steps to work on Explicit Cursor.

  1. II > III > IV > I
  2. I > II > IV > III
  3. I > II > III > IV
  4. IV > III > II > I

Answer: C) I > II > III > IV

Explanation:

This is the order in which 4 steps will be performed to work on Explicit Cursor:

  1. Declare the cursor to be initialized in memory.
  2. Allocate memory by opening the cursor.
  3. Retrieve data by fetching the cursor.
  4. Release allocated memory by closing the cursor.

90. Which of the following is the correct syntax to declare explicit cursor? 

  1. CURSOR IS name
SELECT statement;
  2. CURSOR name is
Statement SELECT;
  3. Name IS CURSOR
SELECT statement;
  4. CURSOR name IS
SELECT statement;

Answer: D) 

CURSOR name IS
SELECT statement;

Explanation:

The correct syntax to declare explicit cursor is – 

CURSOR name IS
SELECT statement;

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

  1. FETCH cursor_name INTO variable_list;
  2. FETCH variable_list INTO cursor_name;
  3. INTO cursor_name FETCH variable_list;
  4. INTO variable_list FETCH cursor_name;

Answer: A) FETCH cursor_name INTO variable_list;

Explanation:

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

92. Which clause is used to open the cursor?

  1. BEGIN
  2. START
  3. OPEN
  4. INITIATE

Answer: C) OPEN

Explanation:

OPEN is the clause used to open the cursor.

93. Which clause is used to close the cursor?

  1. END
  2. STOP
  3. FINISH
  4. CLOSE

Answer: D) CLOSE

Explanation:

CLOSE clause is used to close the cursor.

7) PL/SQL Triggers MCQs

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

  1. Cursor
  2. Trigger
  3. Exception
  4. View

Answer: B) Trigger

Explanation:

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

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

  1. When certain conditions are met, a trigger stored in a database is triggered.
  2. A trigger is an application that executes or fires automatically when a certain event occurs.
  3. Both A. and B.
  4. None of the above

Answer: D) None of the above

Explanation:

The following points are TRUE about PL/SQL Trigger –

  1. When certain conditions are met, a trigger stored in a database is triggered.
  2. A trigger is an application that executes or fires automatically when a certain event occurs.

96. In which event(s) trigger is executed?

  1. DDL
  2. DML
  3. Database Operation
  4. All of the above

Answer: D) All of the above

Explanation:

Trigger is executed when -

  1. DDL occurs
  2. DML occurs
  3. Database Operation occurs

97. Which of the following is database operation?

  1. SERVERERROR
  2. STARTUP
  3. SHUTDOWN
  4. All of the above

Answer: D) All of the above

Explanation:

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

98. Which of the following is not an advantage of trigger?

  1. Various column values are automatically generated by triggers
  2. Maintains the integrity of referential
  3. Tables are replicated asynchronously
  4. Validating transactions and preventing them from being invalid

Answer: C) Tables are replicated asynchronously

Explanation:

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

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

  1. DML Operation is performed
  2. DDL Operation is performed
  3. DCL Operation is performed
  4. TCL Operation is performed

Answer: A) DML Operation is performed

Explanation:

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

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

  1. BEFORE
  2. AFTER
  3. INSTEAD OF
  4. All of the above

Answer: D) All of the above

Explanation:

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

101. Which clause is used to create trigger on a view?

  1. BEFORE
  2. AFTER
  3. INSTEAD OF
  4. None of the above

Answer: C) INSTEAD OF

Explanation:

INSTEAD OF clause is used to create trigger on a view.

102. What is the difference between OF column_name and ON table_name in trigger syntax?

  1. OF column_name specifies the column name that is needed to be updated whereas ON table_name specified the table name that is associated with the trigger.
  2. ON table_name specifies the column name that is needed to be updated whereas OF column_name specified the table name that is associated with the trigger.
  3. OF table_name specifies the column name that is needed to be updated whereas ON column_name specified the table name that is associated with the trigger.
  4. ON column_name specifies the column name that is needed to be updated whereas OF table_name specified the table name that is associated with the trigger.

Answer: A) OF column_name specifies the column name that is needed to be updated whereas ON table_name specified the table name that is associated with the trigger.

Explanation:

OF column_name specifies the column name that is needed to be updated whereas ON table_name specified the table name that is associated with the trigger.

103. What does FOR EACH ROW clause do in Trigger syntax?

  1. A row-level trigger is specified, meaning that each row is affected by this trigger
  2. When the SQL statement is run, the trigger will be executed just once, which is what is called a table-level trigger
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

In the case of FOR EACH ROW –

  1. A row-level trigger is specified, meaning that each row is affected by this trigger
  2. When the SQL statement is run, the trigger will be executed just once, which is what is called a table-level trigger

104. WHEN condition is valid for which triggers?

  1. Table Level Triggers
  2. Row Level Triggers
  3. Column Level Triggers
  4. Database Level Triggers

Answer: B) Row Level Triggers

Explanation:

WHEN condition is valid for Row Level Triggers.

105. Which of the following statement is TRUE?

  1. Table-level triggers do not have access to OLD and NEW references. Record-level triggers use OLD and NEW references.
  2. Record-level triggers do not have access to OLD and NEW references. Table-level triggers use OLD and NEW references.
  3. Table-level triggers have access to OLD and NEW references.
  4. Record-level triggers do not use OLD and NEW references.

Answer: A) Table-level triggers do not have access to OLD and NEW references. Record-level triggers use OLD and NEW references.

Explanation:

Table-level triggers do not have access to OLD and NEW references. Record-level triggers use OLD and NEW references.

106. Which keyword is used to query the table in the same trigger?

  1. BEFORE
  2. AFTER
  3. SAME
  4. EXACT

Answer: B) AFTER

Explanation:

AFTER keyword is used to query the table in the same trigger.

107. What does REFERENCING OLD AS o NEW AS n clause do in trigger?

  1. This clause is used to refer the old values for different DML statements.
  2. This clause is used to refer the new values for different DML statements.
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

REFERENCING OLD AS o NEW AS n clause is used to refer the old and new values for different DML statements.

108. Which of the following is an advantage of trigger?

  1. Imposing authorizations for security
  2. Keeping track of table access events and logging them
  3. Auditing
  4. All of the above

Answer: D) All of the above

Explanation:

The advantages of trigger are –

  1. Imposing authorizations for security
  2. Keeping track of table access events and logging them
  3. Auditing

109. Which of the following clause is not present in the syntax of trigger?

  1. DECLARE
  2. TRIGGER
  3. CURSOR
  4. INSTEAD OF

Answer: C) CURSOR

Explanation:

CURSOR clause is not present in the syntax of trigger.

110. Tables, views, schemas, or databases can be defined as ________ for events.

  1. Procedure
  2. Views
  3. Triggers
  4. Cursors

Answer: C) Triggers

Explanation:

Tables, views, schemas, or databases can be defined as triggers for events.

8) PL/SQL Exception MCQs

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

  1. FUNCTION
  2. CURSOR
  3. EXCEPTION
  4. PROCEDURE

Answer: C) EXCEPTION

Explanation:

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

112. How many types of exceptions are there?

  1. 2
  2. 3
  3. 4
  4. 5

Answer: A) 2

Explanation:

There are 2 types of exceptions.

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

  1. System-defined
  2. User-defined
  3. Both A. and B.
  4. None of the above

Answer: C) Both A. and B.

Explanation:

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

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

  1. RISE
  2. ROSE
  3. RAISE
  4. RINSE

Answer: C) RAISE

Explanation:

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

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

  1. Users can explicitly raise an exception by using a RAISE statement
  2. RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.
  3. Both A. and B.
  4. None of the above

Answer: C) 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.

116. What is the syntax of User-defined exceptions?

  1. DECLARE my-exception EXCEPTION;
  2. DECLARE EXCEPTION;
  3. DECLARE my-exception;
  4. EXCEPTION;

Answer: A) DECLARE my-exception EXCEPTION;

Explanation:

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

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

  1. DECLARE
  2. WHEN
  3. CLOSE
  4. END

Answer: C) CLOSE

Explanation:

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

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

  1. ACCESS_INTO_NULL
  2. NO_DATA_FOUND
  3. NOT_LOGGED_ON
  4. VALUE_ERROR

Answer: B) NO_DATA_FOUND

Explanation:

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

119. When ACCESS_INTO_NULL exception does is raised?

  1. A unique index column with duplicate values is raised when this error occurs.
  2. An invalid username or password is used by a program to connect to a database.
  3. An automatic assignment of a value to a NULL object raises this exception.
  4. It is raised when more than one row is returned by a SELECT INTO statement.

Answer: C) 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.

120. 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?

  1. INVALID_CURSOR
  2. DUP_VAL_ON_INDEX
  3. VALUE_ERROR
  4. CASE_NOT_FOUND

Answer: D) 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.

Go to Link

Getting Info...

Post a Comment

Cookie Consent
We serve cookies on this site to analyze traffic, remember your preferences, and optimize your experience.
More Details
Oops!
It seems there is something wrong with your internet connection. Please connect to the internet and start browsing again.
AdBlock Detected!
We have detected that you are using adblocking plugin in your browser.
The revenue we earn by the advertisements is used to manage this website, we request you to whitelist our website in your adblocking plugin.