PL SQL MCQ Questions

PL SQL MCQ Questions. These 100 Multiple Choice Questions on PL/SQL will help you score full marks in your exam & interview.

1. Introduction to PL/SQL

1.1 What is PL/SQL?

Question 1: PL/SQL is an extension of SQL. What does PL/SQL stand for?

A. Practical Language/Structured Query LanguageB. Procedural Language/Structured Query LanguageC. Primary Language/Structured Query LanguageD. Peripheral Language/Structured Query Language

Show Explanation

Correct Answer: B. Procedural Language/Structured Query Language (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 2: Which of the following is NOT a benefit of using PL/SQL over standard SQL?

A. Improved performance through reduced network trafficB. Enhanced security featuresC. Increased code complexityD. Support for procedural programming constructs

Show Explanation

Correct Answer: C. Increased code complexity. PL/SQL actually reduces code complexity by allowing for modularization and code reuse. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 3:  PL/SQL is commonly used in which of the following real-world applications?

A. Web developmentB. Data warehousingC. Database administrationD. All of the above

Show Explanation

Correct Answer: D. All of the above (PL SQL MCQ Questions by Top100MCQ.com)

   

1.2 PL/SQL Architecture

Question 4: How does PL/SQL interact with the Oracle database?

A. It bypasses the SQL engine and directly accesses data files.B. It sends SQL statements to the SQL engine for execution.C. It modifies the database schema without using SQL.D. It operates independently of the Oracle database.

Show Explanation

Correct Answer: B. It sends SQL statements to the SQL engine for execution. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 5: What is the correct order of the PL/SQL compilation and execution process?

A. Execution, CompilationB. Compilation, ExecutionC. Interpretation, ExecutionD. Execution, Interpretation

Show Explanation

Correct Answer: B. Compilation, Execution (PL SQL MCQ Questions by Top100MCQ.com)

   

1.3 PL/SQL Block Structure

Question 6: Which section of a PL/SQL block is optional?

A. DECLAREB. BEGINC. EXCEPTIOND. None of the above

Show Explanation

Correct Answer: C. EXCEPTION (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 7: What is the purpose of the EXCEPTION section in a PL/SQL block?

A. To define variables and constantsB. To execute the main code logicC. To handle runtime errorsD. To declare the block structure

Show Explanation

Correct Answer: C. To handle runtime errors (PL SQL MCQ Questions by Top100MCQ.com)

   

1.4 Lexical Units

Question 8: Which of the following is NOT a lexical unit in PL/SQL?

A. IdentifierB. KeywordC. VariableD. Delimiter

Show Explanation

Correct Answer: C. Variable. While variables are important in PL/SQL, they are not considered lexical units. Lexical units are the basic building blocks of the language’s syntax, such as identifiers, delimiters, literals, and comments. (PL SQL MCQ Questions by Top100MCQ.com)

   

2. Data Types and Variables

2.1 Scalar Data Types

Question 9: Which data type would you use to store a person’s age in PL/SQL?

A. VARCHAR2B. NUMBERC. DATED. BOOLEAN

Show Explanation

Correct Answer: B. NUMBER (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 10: What is the maximum length of a VARCHAR2 variable in PL/SQL?

A. 255 charactersB. 1000 charactersC. 4000 charactersD. 32767 characters

Show Explanation

Correct Answer: D. 32767 characters (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 11:  Which data type is used to store logical values in PL/SQL?

A. CHARB. DATEC. BOOLEAND. NUMBER

Show Explanation

Correct Answer: C. BOOLEAN (PL SQL MCQ Questions by Top100MCQ.com)

   

2.2 Composite Data Types

Question 12: What is a record in PL/SQL?

A. A collection of values of the same data typeB. A pointer to a row in a database tableC. A structured data type that groups together related variables of different data typesD. A variable that can store large objects

Show Explanation

Correct Answer: C. A structured data type that groups together related variables of different data types (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 13: How do you access individual elements within a PL/SQL record?

A. Using array indicesB. Using dot notation (.)C. Using the element’s name directlyD. Using the `ACCESS` keyword

Show Explanation

Correct Answer: B. Using dot notation (.) (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 14: What is the purpose of indexing a table in PL/SQL?

A. To improve the performance of queries that access the tableB. To enforce data integrityC. To define the structure of the tableD. To store large objects

Show Explanation

Correct Answer: A. To improve the performance of queries that access the table (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 15:  Which of the following is true about VARRAYS in PL/SQL?

A. They can store elements of different data types.B. Their size is fixed at the time of declaration.C. They can be resized dynamically.D. They are used to store large objects.

Show Explanation

Correct Answer: B. Their size is fixed at the time of declaration. (PL SQL MCQ Questions by Top100MCQ.com)

   

2.3 LOB Datatypes

Question 16:  Which data type would you use to store a large text file in PL/SQL?

A. VARCHAR2B. BLOBC. CLOBD. NUMBER

Show Explanation

Correct Answer: C. CLOB (PL SQL MCQ Questions by Top100MCQ.com)

   

2.4 Variable Declaration and Initialization

Question 17: What is the purpose of the `DEFAULT` keyword when declaring a variable in PL/SQL?

A. To assign a default value to the variableB. To make the variable a constantC. To enforce a NOT NULL constraintD. To define the data type of the variable

Show Explanation

Correct Answer: A. To assign a default value to the variable (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 18: How do you enforce a NOT NULL constraint on a PL/SQL variable?

A. By using the `NOT NULL` keyword in the variable declarationB. By using a CHECK constraintC. By using a triggerD. By using the `CONSTRAINT` keyword

Show Explanation

Correct Answer: A. By using the `NOT NULL` keyword in the variable declaration (PL SQL MCQ Questions by Top100MCQ.com)

   

3. Operators and Expressions

3.1 Arithmetic Operators

Question 19: What is the result of the following PL/SQL expression: `10 + 5 * 2`?

A. 20B. 30C. 15D. 12

Show Explanation

Correct Answer: A. 20. Multiplication (`*`) has higher precedence than addition (`+`). (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 20: Which arithmetic operator is used for exponentiation in PL/SQL?

A. ^B. **C.   %D.   $

Show Explanation

Correct Answer: B. **. (PL SQL MCQ Questions by Top100MCQ.com)

   

3.2 Relational Operators

Question 21: Which relational operator means “not equal to” in PL/SQL?

A.   =B.   !=C.   D.   ><

Show Explanation

Correct Answer: B. != and C. Both are valid ways to express “not equal to” in PL/SQL. (PL SQL MCQ Questions by Top100MCQ.com)

   

3.3 Logical Operators

Question 22: What is the result of the following PL/SQL expression if `x = 5` and `y = 10`: `x < 10 AND y > 5`?

A. TRUEB. FALSEC. NULLD. Error

Show Explanation

Correct Answer: A. TRUE. Both conditions (`x 5`) are true, and the `AND` operator requires both conditions to be true for the entire expression to be true. (PL SQL MCQ Questions by Top100MCQ.com)

   

3.4 String Operators

Question 23: Which operator is used to concatenate strings in PL/SQL?

A.   +B.   &C.   ||D.   #

Show Explanation

Correct Answer: C. || (PL SQL MCQ Questions by Top100MCQ.com)

   

3.5 Operator Precedence

Question 24:  In the absence of parentheses, which operation is performed first in the following PL/SQL expression: `NOT x > 5 AND y = 10`?

A.   `>`B.   `AND`C.   `=`D.   `NOT`

Show Explanation

Correct Answer: D. `NOT`. Logical NOT has the highest precedence in this expression. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 25: What is the result of the following PL/SQL expression: `5 + 3 * 2 – 4 / 2`?

A. 9B. 10C. 5D. 6

Show Explanation

Correct Answer: A. 9.  Following order of operations: `3 * 2 = 6`, `4 / 2 = 2`, `5 + 6 = 11`, `11 – 2 = 9`. (PL SQL MCQ Questions by Top100MCQ.com)

   

4. Control Structures

4.1 Conditional Statements

Question 26: Which conditional statement allows you to test multiple conditions in PL/SQL?

A. IF-THEN-ELSEB. IF-THEN-ELSIF-ELSEC. CASED. All of the above

Show Explanation

Correct Answer: D. All of the above.  Both `IF-THEN-ELSIF-ELSE` and `CASE` statements allow for testing multiple conditions. (PL SQL MCQ Questions by Top100MCQ.com)

Read Also:

• REACT JS MCQ Questions 100 MCQ
• Design and Analysis of Algorithm DAA MCQ
• DBMS MCQ Questions for Placement

   

Question 27: What is the purpose of the `ELSIF` clause in an IF statement?

A. To execute a block of code if the initial IF condition is falseB. To handle exceptionsC. To define a loopD. To terminate a loop

Show Explanation

Correct Answer: A. To execute a block of code if the initial IF condition is false (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 28: What is the difference between a simple CASE statement and a searched CASE statement in PL/SQL?

A. A simple CASE statement compares an expression to specific values, while a searched CASE statement evaluates a series of boolean conditions.B. A simple CASE statement can only be used with numeric data types, while a searched CASE statement can be used with any data type.C. A simple CASE statement is more efficient than a searched CASE statement.D. There is no difference between the two.

Show Explanation

Correct Answer: A. A simple CASE statement compares an expression to specific values, while a searched CASE statement evaluates a series of boolean conditions. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 29:  Which keyword is used to terminate a CASE statement in PL/SQL?

A. END CASEB. END IFC. END LOOPD. END

Show Explanation

Correct Answer: A. END CASE (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 30:  How many `ELSE` clauses can an IF statement have?

A. Only oneB. As many as neededC. NoneD. Two

Show Explanation

Correct Answer: A. Only one (PL SQL MCQ Questions by Top100MCQ.com)

   

4.2 Loops

Question 31: Which loop in PL/SQL executes a block of code at least once, even if the loop condition is initially false?

A. LOOPB. WHILE loopC. FOR loopD. None of the above

Show Explanation

Correct Answer: A. LOOP. The basic `LOOP` statement executes the code within its block before checking the condition for exiting the loop. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 32: Which loop is best suited for iterating over a specific range of numbers in PL/SQL?

A. LOOPB. WHILE loopC. FOR loopD. None of the above

Show Explanation

Correct Answer: C. FOR loop.  The `FOR loop` is designed for iterating over a predefined sequence of numbers. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 33: What is the purpose of the `EXIT` statement in a loop?

A. To immediately terminate the loopB. To skip the current iteration and proceed to the nextC. To pause the loop executionD. To restart the loop

Show Explanation

Correct Answer: A. To immediately terminate the loop (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 34: What is the purpose of the `CONTINUE` statement in a loop?

A. To immediately terminate the loopB. To skip the current iteration and proceed to the nextC. To pause the loop executionD. To restart the loop

Show Explanation

Correct Answer: B. To skip the current iteration and proceed to the next (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 35: What is a nested loop in PL/SQL?

A. A loop that iterates over a cursorB. A loop that contains another loop within its code blockC. A loop that has multiple exit conditionsD. A loop that uses the `CONTINUE` statement

Show Explanation

Correct Answer: B. A loop that contains another loop within its code block (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 36: Which type of FOR loop in PL/SQL iterates over the rows returned by a database query?

A. Numeric FOR loopB. Cursor FOR loopC. WHILE loopD.   Simple loop

Show Explanation

Correct Answer: B. Cursor FOR loop (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 37:  What happens if you omit the `WHEN` condition in a `EXIT WHEN` statement within a loop?

A. The loop will terminate immediately.B. The loop will continue indefinitely.C.   This will result in a syntax error.D. The loop will execute only once.

Show Explanation

Correct Answer: C. This will result in a syntax error. A `WHEN` condition is required to specify the condition for exiting the loop. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 38: Which of the following is NOT a valid loop control statement in PL/SQL?

A. EXITB. CONTINUEC. GOTOD. EXIT WHEN

Show Explanation

Correct Answer: C. GOTO. While some languages use `GOTO` for loop control, PL/SQL does not. It relies on `EXIT`, `CONTINUE`, and `EXIT WHEN`. (PL SQL MCQ Questions by Top100MCQ.com)

   

5. Working with Cursors

5.1 What are Cursors?

Question 39: What is a cursor in PL/SQL?

A. A pointer to a row in a database tableB. A mechanism for retrieving and processing multiple rows from a queryC. A data structure for storing large objectsD. A way to handle exceptions

Show Explanation

Correct Answer: B. A mechanism for retrieving and processing multiple rows from a quer (PL SQL MCQ Questions by Top100MCQ.com)

  y

Question 40: Why are cursors used in PL/SQL?

A. To improve the performance of queriesB. To enforce data integrityC. To handle exceptionsD. To process data one row at a time

Show Explanation

Correct Answer: D. To process data one row at a time (PL SQL MCQ Questions by Top100MCQ.com)

   

5.2 Implicit Cursors

Question 41: What is an implicit cursor in PL/SQL?

A. A cursor that is explicitly declared by the programmerB. A cursor that is automatically created by PL/SQL for DML statementsC. A cursor that is used to fetch data from a stored procedureD. A cursor that is used to handle exceptions

Show Explanation

Correct Answer: B. A cursor that is automatically created by PL/SQL for DML statements (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 42: Which cursor attribute indicates the number of rows affected by a DML statement?

A. `%FOUND`B. `%NOTFOUND`C. `%ROWCOUNT`D. `%ISOPEN`

Show Explanation

Correct Answer: C. `%ROWCOUNT` (PL SQL MCQ Questions by Top100MCQ.com)

   

5.3 Explicit Cursors

Question 43: What is the first step in working with an explicit cursor in PL/SQL?

A. Opening the cursorB. Fetching data from the cursorC. Declaring the cursorD. Closing the cursor

Show Explanation

Correct Answer: C. Declaring the cursor (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 44: Which clause is used to pass values to a cursor when it is opened?

A. `USING`B. `WHERE`C. `VALUES`D. `PARAMETERS`

Show Explanation

Correct Answer: A. `USING` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 45: What is the purpose of the `FETCH` statement in relation to cursors?

A. To declare the cursorB. To open the cursorC. To retrieve data from the cursorD. To close the cursor

Show Explanation

Correct Answer: C. To retrieve data from the cursor (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 46: What happens if you try to fetch data from a cursor that is closed?

A. An exception is raisedB. The `FETCH` statement is ignoredC. The last fetched row is returned againD. The cursor is automatically reopened

Show Explanation

Correct Answer: A. An exception is raised (PL SQL MCQ Questions by Top100MCQ.com)

   

6. Procedures and Functions

6.1 Creating Procedures

Question 47: What is the main purpose of procedures in PL/SQL?

A. To return a single valueB. To handle exceptionsC. To modularize code and improve reusabilityD. To define data structures

Show Explanation

Correct Answer: C. To modularize code and improve reusabilit (PL SQL MCQ Questions by Top100MCQ.com)

  y

Question 48: Which keyword is used to specify that a procedure parameter is an output parameter?

A. `IN`B. `OUT`C. `IN OUT`D. `RETURN`

Show Explanation

Correct Answer: B. `OUT` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 49: What is the scope of a local variable declared within a procedure?

A. The entire databaseB. The current sessionC. The procedure in which it is declaredD. The package that contains the procedure

Show Explanation

Correct Answer: C. The procedure in which it is declared (PL SQL MCQ Questions by Top100MCQ.com)

   

6.2 Creating Functions

Question 50: What is the main difference between a procedure and a function in PL/SQL?

A. A procedure can have parameters, while a function cannot.B. A function must return a value, while a procedure does not have to.C. A function can be called from SQL statements, while a procedure cannot.D. There is no difference between the two.

Show Explanation

Correct Answer: B. A function must return a value, while a procedure does not have to. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 51: Which keyword is used to return a value from a function in PL/SQL?

A. `OUT`B. `RETURN`C. `IN OUT`D. `VALUE`

Show Explanation

Correct Answer: B. `RETURN` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 52: Can a function have OUT parameters?

A. Yes, a function can have any number of OUT parameters.B. No, a function cannot have OUT parameters.C. Yes, but only one OUT parameter is allowed.D. It depends on the return type of the function.

Show Explanation

Correct Answer: B. No, a function cannot have OUT parameters. Functions are primarily designed to return a single value using the `RETURN` statement. (PL SQL MCQ Questions by Top100MCQ.com)

Read Also:

• Machine Learning MCQ Questions and Answers 
• 100 JavaScript MCQ Questions and Answers
• Fundamentals of Computer MCQs

   

6.3 Calling Procedures and Functions

Question 53: How do you call a procedure within another PL/SQL block?

A. By using the `EXECUTE` keywordB. By using the procedure’s name followed by a semicolonC. By using the `CALL` keywordD. By using the `RUN` keyword

Show Explanation

Correct Answer: B. By using the procedure’s name followed by a semicolon (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 54: How do you call a function within a SQL statement?

A. By using the function’s name in the `WHERE` clauseB. By using the function’s name in the `SELECT` listC. By using the `CALL` keywordD. By using the `EXECUTE` keyword

Show Explanation

Correct Answer: B. By using the function’s name in the `SELECT` list (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 55:  What happens if you try to call a procedure from within a function?

A. It is perfectly acceptable to call a procedure from within a function.B. It will result in a compilation error.C. It will cause a runtime exception.D. The procedure will be ignored.

Show Explanation

Correct Answer: A. It is perfectly acceptable to call a procedure from within a function. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 56: When calling a procedure with IN OUT parameters, what must be provided in the call statement?

A. A constant valueB. A literal valueC. A variableD. Any of the above

Show Explanation

Correct Answer: C. A variable.  IN OUT parameters require a variable so that the procedure can modify the value and return it to the calling block. (PL SQL MCQ Questions by Top100MCQ.com)

   

7. Exception Handling

7.1 Predefined Exceptions

Question 57: What is the `NO_DATA_FOUND` exception in PL/SQL?

A. An exception raised when a SELECT statement returns no rowsB. An exception raised when an INSERT statement violates a unique constraintC. An exception raised when a variable is not initializedD. An exception raised when a division by zero occurs

Show Explanation

Correct Answer: A. An exception raised when a SELECT statement returns no rows (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 58: Which predefined exception is raised when a DML statement attempts to modify more than one row when only one row is expected?

A. `NO_DATA_FOUND`B. `TOO_MANY_ROWS`C. `VALUE_ERROR`D. `INVALID_CURSOR`

Show Explanation

Correct Answer: B. `TOO_MANY_ROWS` (PL SQL MCQ Questions by Top100MCQ.com)

   

7.2 User-defined Exceptions

Question 59: How do you declare a user-defined exception in PL/SQL?

A. Using the `EXCEPTION` keyword in the `DECLARE` sectionB. Using the `RAISE` keyword in the `BEGIN` sectionC. Using the `WHEN OTHERS` clause in the `EXCEPTION` sectionD. You cannot declare user-defined exceptions in PL/SQL

Show Explanation

Correct Answer: A. Using the `EXCEPTION` keyword in the `DECLARE` section (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 60: Which keyword is used to explicitly raise an exception in PL/SQL?

A. `THROW`B. `RAISE`C. `EXCEPTION`D. `SIGNAL`

Show Explanation

Correct Answer: B. `RAISE` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 61:  What is the purpose of user-defined exceptions in PL/SQL?

A. To handle predefined exceptions more effectivelyB. To create custom error conditions specific to your application logicC. To replace the need for exception handling blocksD. To improve code readability

Show Explanation

Correct Answer: B. To create custom error conditions specific to your application logic (PL SQL MCQ Questions by Top100MCQ.com)

   

7.3 Exception Handling Blocks

Question 62: Which clause is used to catch all unhandled exceptions in an exception handling block?

A. `WHEN OTHERS`B. `CATCH ALL`C. `EXCEPTION_ALL`D. `HANDLE ALL`

Show Explanation

Correct Answer: A. `WHEN OTHERS` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 63: What does it mean to propagate an exception in PL/SQL?

A. To handle the exception in the current blockB. To re-raise the exception in a higher-level blockC. To ignore the exceptionD. To log the exception to a file

Show Explanation

Correct Answer: B. To re-raise the exception in a higher-level block (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 64: What happens if an exception is raised in a PL/SQL block and is not handled by an exception handler?

A. The program continues execution as if no exception occurred.B. The program terminates with an error message.C. The exception is ignored.D. The program enters an infinite loop.

Show Explanation

Correct Answer: B. The program terminates with an error message. (PL SQL MCQ Questions by Top100MCQ.com)

   

8. Packages

8.1 What are Packages?

Question 65: What is a package in PL/SQL?

A. A collection of related procedures, functions, and variablesB. A standalone stored procedureC. A data structure for storing large objectsD. A mechanism for handling exceptions

Show Explanation

Correct Answer: A. A collection of related procedures, functions, and variables (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 66: What are the benefits of using packages in PL/SQL?

A. Improved code organization and modularityB. Enhanced code reusabilityC. Better data securityD. All of the above

Show Explanation

Correct Answer: D. All of the above (PL SQL MCQ Questions by Top100MCQ.com)

   

8.2 Package Specification

Question 67: What is the purpose of the package specification in PL/SQL?

A. To define the private elements of the packageB. To implement the procedures and functions of the packageC. To declare the public interface of the packageD. To initialize package variables

Show Explanation

Correct Answer: C. To declare the public interface of the package (PL SQL MCQ Questions by Top100MCQ.com)

   

8.3 Package Body

Question 68: What is the purpose of the package body in PL/SQL?

A. To declare the public elements of the packageB. To provide the implementation for the procedures and functions declared in the package specificationC. To define the package dependenciesD. To grant execute privileges on the package

Show Explanation

Correct Answer: B. To provide the implementation for the procedures and functions declared in the package specification (PL SQL MCQ Questions by Top100MCQ.com)

   

8.4 Using Packages

Question 69: How do you access a procedure or function within a package in PL/SQL?

A. By using the package name followed by the procedure or function name, separated by a dot (.)B. By directly calling the procedure or function nameC. By using the `EXECUTE` keyword followed by the package name and procedure or function nameD. By using the `CALL` keyword followed by the package name and procedure or function name

Show Explanation

Correct Answer: A. By using the package name followed by the procedure or function name, separated by a dot (.) (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 70:  What happens if you try to access a private element of a package from outside the package?

A.   It is allowed as long as you have execute privileges on the package.B. It will result in a compilation error.C. It will cause a runtime exception.D. The private element will be treated as public.

Show Explanation

Correct Answer: B. It will result in a compilation error. Private elements are intentionally hidden and can only be accessed from within the package itself. (PL SQL MCQ Questions by Top100MCQ.com)

   

9. Triggers

9.1 What are Triggers?

Question 71: What is a trigger in PL/SQL?

A. A stored procedure that is executed manuallyB. A mechanism for handling exceptionsC. A block of code that is automatically executed in response to a database eventD. A data structure for storing large objects

Show Explanation

Correct Answer: C. A block of code that is automatically executed in response to a database event (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 72: What are some common use cases for triggers in PL/SQL?

A. Enforcing data integrityB. Auditing database changesC. Implementing complex security rulesD. All of the above

Show Explanation

Correct Answer: D. All of the above (PL SQL MCQ Questions by Top100MCQ.com)

   

9.2 Trigger Types

Question 73: Which type of trigger is fired in response to DML statements (INSERT, UPDATE, DELETE)?

A. DML triggerB. Instead of triggerC. System triggerD. Event trigger

Show Explanation

Correct Answer: A. DML trigger (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 74: What is the purpose of an “instead of” trigger in PL/SQL?

A. To replace the default action of a DML statementB. To execute before a DML statementC. To execute after a DML statementD. To handle exceptions raised by a DML statement

Show Explanation

Correct Answer: A. To replace the default action of a DML statement (PL SQL MCQ Questions by Top100MCQ.com)

   

9.3 Triggering Events

Question 75: Which keyword specifies that a trigger should fire before the triggering event?

A. `BEFORE`B. `AFTER`C. `INSTEAD OF`D. `DURING`

Show Explanation

Correct Answer: A. `BEFORE` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 76: What does the `FOR EACH ROW` clause indicate in a trigger definition?

A. The trigger will fire once for the entire DML statement.B. The trigger will fire once for each row affected by the DML statement.C. The trigger will fire only if multiple rows are affected by the DML statement.D. The trigger will fire only if a single row is affected by the DML statement.

Show Explanation

Correct Answer: B. The trigger will fire once for each row affected by the DML statement. (PL SQL MCQ Questions by Top100MCQ.com)

   

9.4 Creating Triggers

Question 77:  Which of the following is NOT a valid trigger type in PL/SQL?

A. `BEFORE INSERT`B. `AFTER DELETE`C. `INSTEAD OF UPDATE`D. `ON COMMIT`

Show Explanation

Correct Answer: D. `ON COMMIT`. While `ON COMMIT` is a valid concept in database systems, it’s not directly associated with the trigger types in PL/SQL. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 78: What does the `:OLD` pseudo-record refer to in a trigger?

A. The new values of the row after the DML statementB. The original values of the row before the DML statementC. The values of the row in a temporary tableD. The values of the row in a parent table

Show Explanation

Correct Answer: B. The original values of the row before the DML statement (PL SQL MCQ Questions by Top100MCQ.com)

Read Also:

• Photoshop MCQ Questions and Answers
• Machine Learning MCQ Questions and Answers 
• System Analysis and Design MCQ

   

Question 79: What does the `:NEW` pseudo-record refer to in a trigger?

A. The new values of the row after the DML statementB. The original values of the row before the DML statementC. The values of the row in a temporary tableD. The values of the row in a parent table

Show Explanation

Correct Answer: A. The new values of the row after the DML statement (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 80:  Can a trigger call a stored procedure?

A. No, triggers cannot call stored procedures.B. Yes, triggers can call stored procedures.C. Only DML triggers can call stored procedures.D. Only “instead of” triggers can call stored procedures.

Show Explanation

Correct Answer: B. Yes, triggers can call stored procedures. (PL SQL MCQ Questions by Top100MCQ.com)

   

10. Collections and Records

10.1 Nested Tables

Question 81: What is a nested table in PL/SQL?

A. A table that is stored within another tableB. A collection that can store a variable number of elements of the same data typeC. A data type for storing large objectsD. A mechanism for handling exceptions

Show Explanation

Correct Answer: B. A collection that can store a variable number of elements of the same data type (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 82: How do you declare a nested table in PL/SQL?

A. Using the `TABLE OF` clauseB. Using the `VARRAY` keywordC. Using the `RECORD` keywordD. Using the `NESTED TABLE` keyword

Show Explanation

Correct Answer: A. Using the `TABLE OF` clause (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 83:  What is the main difference between a nested table and a VARRAY in PL/SQL?

A. A nested table can store elements of different data types, while a VARRAY cannot.B. A nested table can be resized dynamically, while a VARRAY has a fixed size.C. A nested table can only be used within a package, while a VARRAY can be used anywhere.D. There is no difference between the two.

Show Explanation

Correct Answer: B. A nested table can be resized dynamically, while a VARRAY has a fixed size. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 84: How do you access an element in a nested table?

A. Using dot notation (.)B. Using array indices enclosed in parenthesesC. Using the element’s name directlyD. Using the `ACCESS` keyword

Show Explanation

Correct Answer: B. Using array indices enclosed in parentheses (PL SQL MCQ Questions by Top100MCQ.com)

   

10.2 VARRAYS

Question 85: What is a VARRAY in PL/SQL?

A. A collection that can store a variable number of elements of different data typesB. A collection that can store a fixed number of elements of the same data typeC. A data type for storing large objectsD. A mechanism for handling exceptions

Show Explanation

Correct Answer: B. A collection that can store a fixed number of elements of the same data type (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 86:  How do you declare a VARRAY in PL/SQL?

A. Using the `TABLE OF` clauseB. Using the `VARRAY` keyword followed by the size and data type of the elementsC. Using the `RECORD` keywordD. Using the `NESTED TABLE` keyword

Show Explanation

Correct Answer: B. Using the `VARRAY` keyword followed by the size and data type of the elements (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 87: What happens if you try to add more elements to a VARRAY than its declared size?

A. The extra elements are ignored.B. A runtime exception is raised.C. The VARRAY is automatically resized.D. The first element in the VARRAY is overwritten.

Show Explanation

Correct Answer: B. A runtime exception is raised. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 88: How do you determine the number of elements in a VARRAY?

A. Using the `COUNT` functionB. Using the `SIZE` attributeC. Using the `LENGTH` attributeD. Using the `LIMIT` attribute

Show Explanation

Correct Answer: B. Using the `SIZE` attribute (PL SQL MCQ Questions by Top100MCQ.com)

   

10.3 Associative Arrays (Index-By Tables)

Question 89: What is an associative array (index-by table) in PL/SQL?

A. A collection that uses numeric indices to access its elementsB. A collection that uses strings or numbers as keys to access its elementsC. A data type for storing large objectsD. A mechanism for handling exceptions

Show Explanation

Correct Answer: B. A collection that uses strings or numbers as keys to access its elements (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 90:  What are some advantages of using associative arrays in PL/SQL?

A. Efficient retrieval of elements using keysB. Sparsely populated collectionsC. Flexible key data typesD. All of the above

Show Explanation

Correct Answer: D. All of the above (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 91: Which keyword is used to declare an associative array in PL/SQL?

A. `TABLE OF`B. `VARRAY`C. `INDEX BY`D. `ASSOCIATIVE ARRAY`

Show Explanation

Correct Answer: C. `INDEX BY` (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 92: What types of keys can be used with associative arrays?

A. Only numeric valuesB. Only string valuesC. Both numeric and string valuesD. Any PL/SQL data type

Show Explanation

Correct Answer: C. Both numeric and string values (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 93: How do you add an element to an associative array?

A. Using the `ADD` methodB. By assigning a value to an element using its keyC. Using the `PUT` methodD. Using the `INSERT` keyword

Show Explanation

Correct Answer: B. By assigning a value to an element using its ke (PL SQL MCQ Questions by Top100MCQ.com)

  y

Question 94:  Which predefined exception is raised when you try to access an element in an associative array that does not exist?

A. `NO_DATA_FOUND`B. `TOO_MANY_ROWS`C. `SUBSCRIPT_BEYOND_COUNT`D. `COLLECTION_IS_NULL`

Show Explanation

Correct Answer:   C. `SUBSCRIPT_BEYOND_COUNT` (PL SQL MCQ Questions by Top100MCQ.com)

   

10.4 Records

Question 95: What is a record in PL/SQL?

A. A collection of elements of the same data typeB. A composite data type that groups together related variables of different data typesC. A data type for storing large objectsD. A mechanism for handling exceptions

Show Explanation

Correct Answer: B. A composite data type that groups together related variables of different data types (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 96: How do you declare a record in PL/SQL?

A. Using the `TABLE OF` clauseB. Using the `VARRAY` keywordC. Using the `RECORD` keyword followed by the field definitionsD. Using the `NESTED TABLE` keyword

Show Explanation

Correct Answer: C. Using the `RECORD` keyword followed by the field definitions (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 97: How do you access individual fields within a record?

A. Using array indicesB. Using dot notation (.)C. Using the field’s name directlyD. Using the `ACCESS` keyword

Show Explanation

Correct Answer: B. Using dot notation (.) (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 98: Can a record contain another record as a field?

A. No, records cannot be nested.B. Yes, records can be nested to create complex data structures.C. Only if the nested record is declared in a package.D. Only if the nested record has the same fields as the parent record.

Show Explanation

Correct Answer: B. Yes, records can be nested to create complex data structures. (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 99:  What is the purpose of the `%ROWTYPE` attribute in PL/SQL?

A. To declare a record with the same structure as a row in a database tableB. To declare a record with a variable number of fieldsC. To declare a record that can store large objectsD. To declare a record that can be used as an exception

Show Explanation

Correct Answer: A. To declare a record with the same structure as a row in a database table (PL SQL MCQ Questions by Top100MCQ.com)

   

Question 100: How do you assign values to the fields of a record?

A. By using the `:=` assignment operator for each field individuallyB. By using the `SELECT INTO` statement to populate the record from a database queryC. By using the `CONSTRUCTOR` methodD. All of the above

Show Explanation

Correct Answer: D. All of the above (PL SQL MCQ Questions by Top100MCQ.com)

   

Most Asked Important Questions on PL/SQL

Read Also: Fundamentals of Computer MCQs