PL SQL MCQ Questions

Correct Answer: B. Procedural Language/Structured Query Language. PL/SQL stands for Procedural Language extension to the Structured Query Language.

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

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

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

Correct Answer: B. Compilation, Execution. PL/SQL code is first compiled into bytecode, and then the bytecode is executed by the PL/SQL engine.

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

Correct Answer: C. To handle runtime errors. The EXCEPTION section contains handlers for errors that occur during the execution of the BEGIN section.

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

Correct Answer: B. NUMBER. The NUMBER data type is suitable for storing numeric values like age.

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

Correct Answer: C. BOOLEAN. The BOOLEAN data type stores logical values: TRUE, FALSE, or NULL.

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

Correct Answer: B. Using dot notation (.). You access a field within a record using the format record_name.field_name.

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

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

Correct Answer: C. CLOB. CLOB (Character Large Object) is designed to store large blocks of character data.

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

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

Correct Answer: A. 20. Multiplication (*) has higher precedence than addition (+), so 5 * 2 is evaluated first (10), then 10 + 10 equals 20.

Correct Answer: B. **. The double asterisk (**) is used for raising a number to a power.

Correct Answer: B. !=. Both != and <> are valid operators for “not equal to” in PL/SQL.

Correct Answer: A. TRUE. x < 10 (5 < 10) is TRUE. y > 5 (10 > 5) is TRUE. TRUE AND TRUE evaluates to TRUE.

Correct Answer: C. ||. The double pipe (||) operator is used for string concatenation.

Correct Answer: D. NOT. The NOT operator has the highest precedence among the logical and relational operators shown.

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

Correct Answer: D. All of the above. IF-THEN-ELSIF-ELSE allows sequential condition testing, and CASE statements (both simple and searched) also evaluate multiple conditions.

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

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

Correct Answer: A. END CASE. Every CASE statement must conclude with END CASE;.

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

Correct Answer: A. LOOP. The basic LOOP...EXIT WHEN...END LOOP; structure checks the exit condition after executing the loop body at least once.

Correct Answer: C. FOR loop. The FOR loop_counter IN lower_bound..upper_bound LOOP syntax is specifically designed for iterating over a numeric range.

Correct Answer: A. To immediately terminate the loop. EXIT (often used with WHEN) causes control to pass to the statement immediately following the END LOOP.

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

Correct Answer: B. A loop that contains another loop within its code block. This allows for iterating through combinations or multi-dimensional structures.

Correct Answer: B. Cursor FOR loop. The FOR record_name IN cursor_name LOOP syntax implicitly opens, fetches from, and closes a cursor.

Correct Answer: C. This will result in a syntax error. The EXIT WHEN statement requires a boolean condition to determine when to exit.

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

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

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

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

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

Correct Answer: C. Declaring the cursor. You must first declare the cursor in the DECLARE section, associating it with a SELECT statement.

Correct Answer: A. USING. If the cursor declaration includes parameters, you pass arguments using the USING clause within the OPEN statement.

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

Correct Answer: A. An exception is raised. Attempting to FETCH from a closed cursor typically raises an INVALID_CURSOR exception.

Correct Answer: C. To modularize code and improve reusability. Procedures encapsulate a sequence of actions, making code organized, reusable, and easier to maintain.

Correct Answer: B. OUT. An OUT parameter allows the procedure to return a value back to the calling environment through that parameter.

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

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

Correct Answer: B. RETURN. The RETURN statement specifies the value that the function sends back to the caller.

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

Correct Answer: B. By using the procedure’s name followed by a semicolon. For example: procedure_name(parameter1, parameter2);.

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

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

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

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

Correct Answer: B. TOO_MANY_ROWS. This is raised by a SELECT INTO statement that returns more than one row.

Correct Answer: A. Using the EXCEPTION keyword in the DECLARE section. You declare it like exception_name EXCEPTION;.

Correct Answer: B. RAISE. The RAISE exception_name; statement is used to signal an error condition.

Correct Answer: B. To create custom error conditions specific to your application logic. They allow you to signal specific business rule violations or application-specific errors.

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

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

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

Correct Answer: A. A collection of related procedures, functions, types, cursors, variables, and other PL/SQL constructs, grouped together as a logical unit.

Correct Answer: D. All of the above. Packages offer modularity, reusability, information hiding (public/private), potential performance gains (loading into memory), and easier dependency management.

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

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

Correct Answer: A. By using the package name followed by the procedure or function name, separated by a dot (.). For example: package_name.procedure_name(parameters);.

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

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

Correct Answer: D. All of the above. Triggers are used for enforcing complex business rules, auditing modifications, maintaining data consistency, logging events, and more.

Correct Answer: A. DML trigger. These triggers are specifically associated with data manipulation language operations on tables or views.

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

Correct Answer: A. BEFORE. A BEFORE trigger executes before the triggering DML statement starts operating on the data.

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

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

Correct Answer: B. The original values of the row before the DML statement. Available in UPDATE and DELETE triggers fired FOR EACH ROW.

Correct Answer: A. The new values of the row after the DML statement. Available in INSERT and UPDATE triggers fired FOR EACH ROW.

Correct Answer: B. Yes, triggers can call stored procedures. This is a common practice to encapsulate complex logic invoked by the trigger.

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

Correct Answer: A. Using the TABLE OF clause. The syntax is TYPE type_name IS TABLE OF element_type;.

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

Correct Answer: B. Using array indices enclosed in parentheses. For example: nested_table_variable(index).

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

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

Correct Answer: B. A runtime exception is raised. Attempting to access or assign an element beyond the defined limit typically raises a SUBSCRIPT_BEYOND_COUNT exception.

Correct Answer: A. Using the COUNT attribute. varray_variable.COUNT returns the current number of elements in the VARRAY. LIMIT returns the maximum declared size.

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

Correct Answer: D. All of the above. They provide fast key-based lookup and are very flexible for mapping keys to values.

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

Correct Answer: C. Both numeric (PLS_INTEGER, BINARY_INTEGER) and string (VARCHAR2, STRING) values. The key type is specified in the INDEX BY clause.

Correct Answer: B. By assigning a value to an element using its key. For example: assoc_array_variable(key_value) := element_value;.

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

Correct Answer: B. A composite data type that groups together related variables (fields) of potentially different data types. Records represent a structure.

Correct Answer: C. Using the RECORD keyword followed by the field definitions. Syntax: TYPE record_type_name IS RECORD (field1 datatype1, field2 datatype2, ...);.

Correct Answer: B. Using dot notation (.). You reference a field using record_variable_name.field_name.

Correct Answer: B. Yes, records can be nested to create complex data structures. A field within a record can be of another record type.

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

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