PL SQL MCQ Questions. Explore PL/SQL: its meaning, advantages over basic SQL, and real uses. Understand compilation, block structure (DECLARE, BEGIN, EXCEPTION), data types (NUMBER, VARCHAR2, BOOLEAN, CLOB), operators, and control structures (IF, CASE, LOOP). Learn about cursors, procedures, functions, exceptions, packages, triggers, and collections (VARRAY, nested table, associative array), plus records.
PL SQL MCQ Questions Topics:
Introduction to PL/SQL (MCQ 1–5)
PL/SQL Block Structure (MCQ 6–7)
Lexical Units and Data Types (MCQ 8–18)
Operators in PL/SQL (MCQ 19–25)
Conditional Statements in PL/SQL (MCQ 26–30)
Loops in PL/SQL (MCQ 31–38)
Introduction to Cursors (MCQ 39–42)
Explicit Cursors in PL/SQL (MCQ 43–46)
Procedures in PL/SQL (MCQ 47–50)
Functions in PL/SQL (MCQ 51–56)
Exception Handling in PL/SQL (MCQ 57–64)
Packages in PL/SQL (MCQ 65–70)
Triggers in PL/SQL (MCQ 71–80)
Nested Tables in PL/SQL (MCQ 81–84)
VARRAYs in PL/SQL (MCQ 85–88)
Associative Arrays (Index-by Tables) in PL/SQL (MCQ 89–94)
Records in PL/SQL (MCQ 95–100)
PL SQL MCQ Questions – Mock Online Test
Question 1: PL/SQL is an extension of SQL. What does PL/SQL stand for?
A. Practical Language/Structured Query Language
B. Procedural Language/Structured Query Language
C. Primary Language/Structured Query Language
D. Peripheral Language/Structured Query Language
B. Procedural Language/Structured Query Language. PL/SQL stands for Procedural Language extension to the Structured Query Language.
Question 2: Which of the following is NOT a benefit of using PL/SQL over standard SQL?
A. Improved performance through reduced network traffic
B. Enhanced security features
C. Increased code complexity
D. Support for procedural programming constructs
C. Increased code complexity. PL/SQL aims to reduce code complexity by allowing for modularization and code reuse, although poorly written PL/SQL can be complex.
Question 3: PL/SQL is commonly used in which of the following real-world applications?
A. Web development
B. Data warehousing
C. Database administration
D. All of the above
D. All of the above. PL/SQL is versatile and used in various applications involving Oracle databases, including web backends, data warehousing processes, and database administration tasks.
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.
B. It sends SQL statements to the SQL engine for execution. PL/SQL integrates procedural logic with SQL, sending SQL commands to the database’s SQL engine.
Question 5: What is the correct order of the PL/SQL compilation and execution process?
A. Execution, Compilation
B. Compilation, Execution
C. Interpretation, Execution
D. Execution, Interpretation
B. Compilation, Execution. PL/SQL code is first compiled into bytecode, and then the bytecode is executed by the PL/SQL engine.
Question 6: Which section of a PL/SQL block is optional?
A. DECLARE
B. BEGIN
C. EXCEPTION
D. Both A and C
D. Both A and C. Both the DECLARE and EXCEPTION sections are optional in a PL/SQL block. Only the BEGIN and END keywords defining the executable section are mandatory.
Question 7: What is the purpose of the EXCEPTION section in a PL/SQL block?
A. To define variables and constants
B. To execute the main code logic
C. To handle runtime errors
D. To declare the block structure
C. To handle runtime errors. The EXCEPTION section contains handlers for errors that occur during the execution of the BEGIN section.
Question 8: Which of the following is NOT a lexical unit in PL/SQL?
A. Identifier
B. Keyword
C. Variable
D. Delimiter
C. Variable. Variables are program constructs used to store data, but they are composed of lexical units (like identifiers). Lexical units are the fundamental syntax elements like identifiers, keywords, literals, delimiters, and comments.
Question 9: Which data type would you use to store a person’s age in PL/SQL?
A. VARCHAR2
B. NUMBER
C. DATE
D. BOOLEAN
B. NUMBER. The NUMBER data type is suitable for storing numeric values like age.
Question 10: What is the maximum length of a VARCHAR2 variable in PL/SQL?
A. 255 characters
B. 1000 characters
C. 4000 characters
D. 32767 characters
D. 32767 characters. Within PL/SQL, a VARCHAR2 variable can hold up to 32,767 bytes (or characters, depending on the database character set and configuration).
Question 11: Which data type is used to store logical values in PL/SQL?
A. CHAR
B. DATE
C. BOOLEAN
D. NUMBER
C. BOOLEAN. The BOOLEAN data type stores logical values: TRUE, FALSE, or NULL.
Question 12: What is a record in PL/SQL?
A. A collection of values of the same data type
B. A pointer to a row in a database table
C. A structured data type that groups together related variables of different data types
D. A variable that can store large objects
C. A structured data type that groups together related variables of different data types. Records allow you to treat related but potentially differently-typed data as a single unit.
Question 13: How do you access individual elements within a PL/SQL record?
A. Using array indices
B. Using dot notation (.)
C. Using the element’s name directly
D. Using the ACCESS keyword
B. Using dot notation (.). You access a field within a record using the format record_name.field_name.
Question 14: What is the purpose of indexing a table in PL/SQL?
A. To improve the performance of queries that access the table
B. To enforce data integrity
C. To define the structure of the table
D. To store large objects
A. To improve the performance of queries that access the table. Indexing is primarily a database concept (used within SQL executed by PL/SQL) to speed up data retrieval.
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.
B. Their size is fixed at the time of declaration. VARRAYs (Variable-size arrays) have a maximum size defined at creation and cannot exceed it.
Question 16: Which data type would you use to store a large text file in PL/SQL?
A. VARCHAR2
B. BLOB
C. CLOB
D. NUMBER
C. CLOB. CLOB (Character Large Object) is designed to store large blocks of character data.
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 variable
B. To make the variable a constant
C. To enforce a NOT NULL constraint
D. To define the data type of the variable
A. To assign a default value to the variable. If no value is explicitly assigned, the variable will take the specified default value upon declaration.
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 declaration
B. By using a CHECK constraint
C. By using a trigger
D. By using the CONSTRAINT keyword
A. By using the NOT NULL keyword in the variable declaration. You can specify NOT NULL to ensure the variable must always hold a value.
Question 19: What is the result of the following PL/SQL expression: 10 + 5 * 2?
A. 20
B. 30
C. 15
D. 12
A. 20. Multiplication (*) has higher precedence than addition (+), so 5 * 2 is evaluated first (10), then 10 + 10 equals 20.
Question 20: Which arithmetic operator is used for exponentiation in PL/SQL?
A. ^
B. **
C. %
D. $
B. **. The double asterisk (**) is used for raising a number to a power.
Question 21: Which relational operator means “not equal to” in PL/SQL?
A. =
B. !=
C. <>
D. ><
B. !=. Both != and <> are valid operators for “not equal to” in PL/SQL.
Question 22: What is the result of the following PL/SQL expression if x = 5 and y = 10: x < 10 AND y > 5?
A. TRUE
B. FALSE
C. NULL
D. Error
A. TRUE. x < 10 (5 < 10) is TRUE. y > 5 (10 > 5) is TRUE. TRUE AND TRUE evaluates to TRUE.
Question 23: Which operator is used to concatenate strings in PL/SQL?
A. +
B. &
C. ||
D. #
C. ||. The double pipe (||) operator is used for string concatenation.
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
D. NOT. The NOT operator has the highest precedence among the logical and relational operators shown.
Question 25: What is the result of the following PL/SQL expression: 5 + 3 * 2 - 4 / 2?
A. 9
B. 10
C. 5
D. 6
A. 9. Following standard operator precedence (Multiplication/Division before Addition/Subtraction): 3 * 2 = 6, 4 / 2 = 2. The expression becomes 5 + 6 - 2. Then, 5 + 6 = 11, and 11 - 2 = 9.
Question 26: Which conditional statement allows you to test multiple conditions in PL/SQL?
A. IF-THEN-ELSE
B. IF-THEN-ELSIF-ELSE
C. CASE
D. All of the above
D. All of the above. IF-THEN-ELSIF-ELSE allows sequential condition testing, and CASE statements (both simple and searched) also evaluate multiple conditions.
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 and all preceding ELSIF conditions are false
B. To handle exceptions
C. To define a loop
D. To terminate a loop
A. To execute a block of code if the initial IF condition and all preceding ELSIF conditions are false. It allows testing additional conditions if the previous ones were not met.
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.
A. A simple CASE statement compares an expression to specific values, while a searched CASE statement evaluates a series of boolean conditions. The simple CASE uses a selector, while the searched CASE evaluates independent WHEN clauses.
Question 29: Which keyword is used to terminate a CASE statement in PL/SQL?
A. END CASE
B. END IF
C. END LOOP
D. END
A. END CASE. Every CASE statement must conclude with END CASE;.
Question 30: How many ELSE clauses can an IF statement have?
A. Only one
B. As many as needed
C. None
D. Two
A. Only one. An IF statement (including IF-THEN-ELSIF) can have at most one ELSE clause, which executes if none of the preceding conditions are TRUE.
Question 31: Which loop in PL/SQL executes a block of code at least once, even if the loop condition is initially false?
A. LOOP
B. WHILE loop
C. FOR loop
D. None of the above
A. LOOP. The basic LOOP...EXIT WHEN...END LOOP; structure checks the exit condition after executing the loop body at least once.
Question 32: Which loop is best suited for iterating over a specific range of numbers in PL/SQL?
A. LOOP
B. WHILE loop
C. FOR loop
D. None of the above
C. FOR loop. The FOR loop_counter IN lower_bound..upper_bound LOOP syntax is specifically designed for iterating over a numeric range.
Question 33: What is the purpose of the EXIT statement in a loop?
A. To immediately terminate the loop
B. To skip the current iteration and proceed to the next
C. To pause the loop execution
D. To restart the loop
A. To immediately terminate the loop. EXIT (often used with WHEN) causes control to pass to the statement immediately following the END LOOP.
Question 34: What is the purpose of the CONTINUE statement in a loop?
A. To immediately terminate the loop
B. To skip the current iteration and proceed to the next
C. To pause the loop execution
D. To restart the loop
B. To skip the current iteration and proceed to the next. CONTINUE (often used with WHEN) bypasses the remaining statements in the current loop iteration and starts the next one.
Question 35: What is a nested loop in PL/SQL?
A. A loop that iterates over a cursor
B. A loop that contains another loop within its code block
C. A loop that has multiple exit conditions
D. A loop that uses the CONTINUE statement
B. A loop that contains another loop within its code block. This allows for iterating through combinations or multi-dimensional structures.
Question 36: Which type of FOR loop in PL/SQL iterates over the rows returned by a database query?
A. Numeric FOR loop
B. Cursor FOR loop
C. WHILE loop
D. Simple loop
B. Cursor FOR loop. The FOR record_name IN cursor_name LOOP syntax implicitly opens, fetches from, and closes a cursor.
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.
C. This will result in a syntax error. The EXIT WHEN statement requires a boolean condition to determine when to exit.
Question 38: Which of the following is NOT a valid loop control statement in PL/SQL?
A. EXIT
B. CONTINUE
C. GOTO
D. EXIT WHEN
C. GOTO. While PL/SQL has a GOTO statement for transferring control to a labeled block, it’s generally discouraged and not considered a standard loop control statement like EXIT or CONTINUE.
Question 39: What is a cursor in PL/SQL?
A. A pointer to a row in a database table
B. A mechanism for retrieving and processing multiple rows from a query
C. A data structure for storing large objects
D. A way to handle exceptions
B. A mechanism for retrieving and processing multiple rows from a query. Cursors provide a way to handle the result set of a SELECT statement, usually row by row.
Question 40: Why are cursors used in PL/SQL?
A. To improve the performance of queries
B. To enforce data integrity
C. To handle exceptions
D. To process data one row at a time
D. To process data one row at a time. When procedural logic needs to be applied individually to rows returned by a query, cursors are necessary.
Question 41: What is an implicit cursor in PL/SQL?
A. A cursor that is explicitly declared by the programmer
B. A cursor that is automatically created by PL/SQL for DML statements
C. A cursor that is used to fetch data from a stored procedure
D. A cursor that is used to handle exceptions
B. A cursor that is automatically created by PL/SQL for DML statements. PL/SQL uses implicit cursors (like SQL%FOUND, SQL%ROWCOUNT) automatically for INSERT, UPDATE, DELETE, and SELECT INTO statements.
Question 42: Which cursor attribute indicates the number of rows affected by a DML statement?
A.%FOUND
B.%NOTFOUND
C.%ROWCOUNT
D.%ISOPEN
C. %ROWCOUNT. For implicit cursors (SQL%), %ROWCOUNT gives the number of rows affected by the last DML statement. For explicit cursors, it gives the number of rows fetched so far.
Question 43: What is the first step in working with an explicit cursor in PL/SQL?
A. Opening the cursor
B. Fetching data from the cursor
C. Declaring the cursor
D. Closing the cursor
C. Declaring the cursor. You must first declare the cursor in the DECLARE section, associating it with a SELECT statement.
Question 44: Which clause is used to pass values to a cursor when it is opened?
A.USING
B.WHERE
C.VALUES
D.PARAMETERS
A. USING. If the cursor declaration includes parameters, you pass arguments using the USING clause within the OPEN statement.
Question 45: What is the purpose of the Workspace statement in relation to cursors?
A. To declare the cursor
B. To open the cursor
C. To retrieve data from the cursor
D. To close the cursor
C. To retrieve data from the cursor. The Workspace statement retrieves the next row from the cursor’s active set into specified variables or a record.
Question 46: What happens if you try to fetch data from a cursor that is closed?
A. An exception is raised
B. The Workspace statement is ignored
C. The last fetched row is returned again
D. The cursor is automatically reopened
A. An exception is raised. Attempting to FETCH from a closed cursor typically raises an INVALID_CURSOR exception.
Question 47: What is the main purpose of procedures in PL/SQL?
A. To return a single value
B. To handle exceptions
C. To modularize code and improve reusability
D. To define data structures
C. To modularize code and improve reusability. Procedures encapsulate a sequence of actions, making code organized, reusable, and easier to maintain.
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
B. OUT. An OUT parameter allows the procedure to return a value back to the calling environment through that parameter.
Question 49: What is the scope of a local variable declared within a procedure?
A. The entire database
B. The current session
C. The procedure in which it is declared
D. The package that contains the procedure
C. The procedure in which it is declared. Local variables exist only within the execution scope of the procedure (or block) where they are declared.
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.
B. A function must return a value, while a procedure does not have to. Functions are designed to compute and return a single value via the RETURN keyword.
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
B. RETURN. The RETURN statement specifies the value that the function sends back to the caller.
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 allowe
D. D. It depends on the return type of the function.
B. No, a function cannot have OUT parameters. Functions return values via the RETURN clause. While technically possible in some older versions or specific contexts with workarounds, standard practice and design dictate functions use RETURN, not OUT parameters.
Question 53: How do you call a procedure within another PL/SQL block?
A. By using the EXECUTE keyword
B. By using the procedure’s name followed by a semicolon
C. By using the CALL keyword
D. By using the RUN keyword
B. By using the procedure’s name followed by a semicolon. For example: procedure_name(parameter1, parameter2);.
Question 54: How do you call a function within a SQL statement?
A. By using the function’s name in the WHERE clause
B. By using the function’s name in the SELECT list
C. By using the CALL keyword
D. By using the EXECUTE keyword
B. By using the function’s name in the SELECT list. Functions designed to be called from SQL can be used like built-in SQL functions, often in the SELECT list or WHERE clause, e.g., SELECT package_name.function_name(column) FROM table;.
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.
A. It is perfectly acceptable to call a procedure from within a function. However, if the function is intended to be called from SQL, the called procedure must not violate purity rules (e.g., performing DML without autonomy).
Question 56: When calling a procedure with IN OUT parameters, what must be provided in the call statement?
A. A constant value
B. A literal value
C. A variable
D. Any of the above
C. A variable. An IN OUT parameter needs a variable in the calling code to both receive the initial value and accept the potentially modified value back from the procedure.
Question 57: What is the NO_DATA_FOUND exception in PL/SQL?
A. An exception raised when a SELECT statement returns no rows
B. An exception raised when an INSERT statement violates a unique constraint
C. An exception raised when a variable is not initialized
D. An exception raised when a division by zero occurs
A. An exception raised when a SELECT statement returns no rows. Specifically, when a SELECT INTO statement finds no rows to populate the target variables.
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
B. TOO_MANY_ROWS. This is raised by a SELECT INTO statement that returns more than one row.
Question 59: How do you declare a user-defined exception in PL/SQL?
A. Using the EXCEPTION keyword in the DECLARE section
B. Using the RAISE keyword in the BEGIN section
C. Using the WHEN OTHERS clause in the EXCEPTION section
D. You cannot declare user-defined exceptions in PL/SQL
A. Using the EXCEPTION keyword in the DECLARE section. You declare it like exception_name EXCEPTION;.
Question 60: Which keyword is used to explicitly raise an exception in PL/SQL?
A.THROW
B.RAISE
C.EXCEPTION
D.SIGNAL
B. RAISE. The RAISE exception_name; statement is used to signal an error condition.
Question 61: What is the purpose of user-defined exceptions in PL/SQL?
A. To handle predefined exceptions more effectively
B. To create custom error conditions specific to your application logic
C. To replace the need for exception handling blocks
D. To improve code readability
B. To create custom error conditions specific to your application logic. They allow you to signal specific business rule violations or application-specific errors.
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
A. WHEN OTHERS. The WHEN OTHERS THEN clause acts as a catch-all for any exception not explicitly handled by preceding WHEN clauses in the same block.
Question 63: What does it mean to propagate an exception in PL/SQL?
A. To handle the exception in the current block
B. To re-raise the exception in a higher-level block
C. To ignore the exception
D. To log the exception to a file
B. To re-raise the exception in a higher-level block. If an exception handler decides it cannot fully resolve the error, it can use RAISE; to pass the same exception up the call stack.
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 ignore
D. D. The program enters an infinite loop.
B. The program terminates with an error message. The unhandled exception propagates out of the PL/SQL block, usually resulting in an error reported to the calling application or user.
Question 65: What is a package in PL/SQL?
A. A collection of related procedures, functions, and variables
B. A standalone stored procedure
C. A data structure for storing large objects
D. A mechanism for handling exceptions
A. A collection of related procedures, functions, types, cursors, variables, and other PL/SQL constructs, grouped together as a logical unit.
Question 66: What are the benefits of using packages in PL/SQL?
A. Improved code organization and modularity
B. Enhanced code reusability
C. Better data security (through private elements)
D. All of the above
D. All of the above. Packages offer modularity, reusability, information hiding (public/private), potential performance gains (loading into memory), and easier dependency management.
Question 67: What is the purpose of the package specification in PL/SQL?
A. To define the private elements of the package
B. To implement the procedures and functions of the package
C. To declare the public interface of the package
D. To initialize package variables
C. To declare the public interface of the package. The specification acts like a header file, declaring the elements (procedures, functions, types, variables) that are accessible from outside the package.
Question 68: What is the purpose of the package body in PL/SQL?
A. To declare the public elements of the package
B. To provide the implementation for the procedures and functions declared in the package specification
C. To define the package dependencies
D. To grant execute privileges on the package
B. To provide the implementation for the procedures and functions declared in the package specification. It contains the actual code for the public subprograms and can also contain private declarations and implementations.
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 name
C. By using the EXECUTE keyword followed by the package name and procedure or function name
D. By using the CALL keyword followed by the package name and procedure or function name
A. By using the package name followed by the procedure or function name, separated by a dot (.). For example: package_name.procedure_name(parameters);.
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.
B. It will result in a compilation error. Private elements declared within the package body are not visible and cannot be accessed directly from outside the package.
Question 71: What is a trigger in PL/SQL?
A. A stored procedure that is executed manually
B. A mechanism for handling exceptions
C. A block of code that is automatically executed in response to a database event
D. A data structure for storing large objects
C. A block of code that is automatically executed in response to a database event. These events are typically DML operations (INSERT, UPDATE, DELETE) on a specific table, or system events.
Question 72: What are some common use cases for triggers in PL/SQL?
A. Enforcing data integrity
B. Auditing database changes
C. Implementing complex security rules
D. All of the above
D. All of the above. Triggers are used for enforcing complex business rules, auditing modifications, maintaining data consistency, logging events, and more.
Question 73: Which type of trigger is fired in response to DML statements (INSERT, UPDATE, DELETE)?
A. DML trigger
B. Instead of trigger
C. System trigger
D. Event trigger
A. DML trigger. These triggers are specifically associated with data manipulation language operations on tables or views.
Question 74: What is the purpose of an “instead of” trigger in PL/SQL?
A. To replace the default action of a DML statement
B. To execute before a DML statement
C. To execute after a DML statement
D. To handle exceptions raised by a DML statement
A. To replace the default action of a DML statement. They are typically used on non-updatable views to provide the logic for how DML operations should affect the underlying base tables.
Question 75: Which keyword specifies that a trigger should fire before the triggering event?
A.BEFORE
B.AFTER
C.INSTEAD OF
D.DURING
A. BEFORE. A BEFORE trigger executes before the triggering DML statement starts operating on the data.
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.
B. The trigger will fire once for each row affected by the DML statement. This is known as a row-level trigger, allowing access to :OLD and :NEW values.
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
D. ON COMMIT. While database actions can occur upon commit (like materialized view refreshes), ON COMMIT is not a direct trigger definition type in the same way as BEFORE/AFTER/INSTEAD OF DML or system event triggers.
Question 78: What does the :OLD pseudo-record refer to in a trigger?
A. The new values of the row after the DML statement
B. The original values of the row before the DML statement
C. The values of the row in a temporary table
D. The values of the row in a parent table
B. The original values of the row before the DML statement. Available in UPDATE and DELETE triggers fired FOR EACH ROW.
Question 79: What does the :NEW pseudo-record refer to in a trigger?
A. The new values of the row after the DML statement
B. The original values of the row before the DML statement
C. The values of the row in a temporary table
D. The values of the row in a parent table
A. The new values of the row after the DML statement. Available in INSERT and UPDATE triggers fired FOR EACH ROW.
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.
B. Yes, triggers can call stored procedures. This is a common practice to encapsulate complex logic invoked by the trigger.
Question 81: What is a nested table in PL/SQL?
A. A table that is stored within another table
B. A collection that can store a variable number of elements of the same data type
C. A data type for storing large objects
D. A mechanism for handling exceptions
B. A collection that can store a variable number of elements of the same data type. Nested tables are unbounded collections (no predefined maximum size) and can be sparse.
Question 82: How do you declare a nested table in PL/SQL?
A. Using the TABLE OF clause
B. Using the VARRAY keyword
C. Using the RECORD keyword
D. Using the NESTED TABLE keyword
A. Using the TABLE OF clause. The syntax is TYPE type_name IS TABLE OF element_type;.
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 (unbounded), while a VARRAY has a fixed maximum 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.
B. A nested table can be resized dynamically (unbounded), while a VARRAY has a fixed maximum size. Nested tables are stored as separate database objects if used as table columns, while VARRAYs can be stored inline.
Question 84: How do you access an element in a nested table?
A. Using dot notation (.)
B. Using array indices enclosed in parentheses
C. Using the element’s name directly
D. Using the ACCESS keyword
B. Using array indices enclosed in parentheses. For example: nested_table_variable(index).
Question 85: What is a VARRAY in PL/SQL?
A. A collection that can store a variable number of elements of different data types
B. A collection that can store a fixed maximum number of elements of the same data type
C. A data type for storing large objects
D. A mechanism for handling exceptions
B. A collection that can store a fixed maximum number of elements of the same data type. The maximum size is specified during type declaration.
Question 86: How do you declare a VARRAY in PL/SQL?
A. Using the TABLE OF clause
B. Using the VARRAY keyword followed by the size and data type of the elements
C. Using the RECORD keyword
D. Using the NESTED TABLE keyword
B. Using the VARRAY keyword followed by the size and data type of the elements. The syntax is TYPE type_name IS VARRAY(max_size) OF element_type;.
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 resize
D. D. The first element in the VARRAY is overwritten.
B. A runtime exception is raised. Attempting to access or assign an element beyond the defined limit typically raises a SUBSCRIPT_BEYOND_COUNT exception.
Question 88: How do you determine the number of elements currently in a VARRAY?
A. Using the COUNT function
B. Using the SIZE attribute
C. Using the LENGTH attribute
D. Using the LIMIT attribute
A. Using the COUNT attribute. varray_variable.COUNT returns the current number of elements in the VARRAY. LIMIT returns the maximum declared size.
Question 89: What is an associative array (index-by table) in PL/SQL?
A. A collection that uses numeric indices to access its elements
B. A collection that uses strings or numbers (usually VARCHAR2 or PLS_INTEGER) as keys to access its elements
C. A data type for storing large objects
D. A mechanism for handling exceptions
B. A collection that uses strings or numbers (usually VARCHAR2 or PLS_INTEGER) as keys to access its elements. They are similar to hash maps or dictionaries in other languages.
Question 90: What are some advantages of using associative arrays in PL/SQL?
A. Efficient retrieval of elements using keys
B. Sparsely populated collections (keys don’t need to be sequential)
C. Flexible key data types (strings or integers)
D. All of the above
D. All of the above. They provide fast key-based lookup and are very flexible for mapping keys to values.
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
C. INDEX BY. The syntax is TYPE type_name IS TABLE OF element_type INDEX BY key_type; (e.g., INDEX BY PLS_INTEGER or INDEX BY VARCHAR2(100)).
Question 92: What types of keys can be used with associative arrays?
A. Only numeric values
B. Only string values
C. Both numeric (PLS_INTEGER, BINARY_INTEGER) and string (VARCHAR2, STRING) values
D. Any PL/SQL data type
C. Both numeric (PLS_INTEGER, BINARY_INTEGER) and string (VARCHAR2, STRING) values. The key type is specified in the INDEX BY clause.
Question 93: How do you add an element to an associative array?
A. Using the ADD method
B. By assigning a value to an element using its key
C. Using the PUT method
D. Using the INSERT keyword
B. By assigning a value to an element using its key. For example: assoc_array_variable(key_value) := element_value;.
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
A. NO_DATA_FOUND. When referencing a key that has not been assigned a value in an associative array, NO_DATA_FOUND is raised. (SUBSCRIPT_BEYOND_COUNT is typically for nested tables/VARRAYs when using an index outside the populated range).
Question 95: What is a record in PL/SQL?
A. A collection of elements of the same data type
B. A composite data type that groups together related variables (fields) of potentially different data types
C. A data type for storing large objects
D. A mechanism for handling exceptions
B. A composite data type that groups together related variables (fields) of potentially different data types. Records represent a structure.
Question 96: How do you declare a record in PL/SQL?
A. Using the TABLE OF clause
B. Using the VARRAY keyword
C. Using the RECORD keyword followed by the field definitions
D. Using the NESTED TABLE keyword
C. Using the RECORD keyword followed by the field definitions. Syntax: TYPE record_type_name IS RECORD (field1 datatype1, field2 datatype2, ...);.
Question 97: How do you access individual fields within a record?
A. Using array indices
B. Using dot notation (.)
C. Using the field’s name directly
D. Using the ACCESS keyword
B. Using dot notation (.). You reference a field using record_variable_name.field_name.
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.
B. Yes, records can be nested to create complex data structures. A field within a record can be of another record type.
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 table or cursor
B. To declare a record with a variable number of fields
C. To declare a record that can store large objects
D. To declare a record that can be used as an exception
A. To declare a record with the same structure as a row in a database table or cursor. This ensures the record has fields matching the table/cursor columns.
Question 100: How do you assign values to the fields of a record?
A. By using the := assignment operator for each field individually
B. By using the SELECT INTO statement to populate the record from a database query
C. By assigning one compatible record variable to another
D. All of the above
D. All of the above. You can assign field by field (record.field := value;), populate the entire record from a query (SELECT ... INTO record FROM ...;), or assign a whole record variable to another (record1 := record2;).
