Oracle PL/SQL Interview Questions Challenge Quiz Quiz

This quiz examines key Oracle PL/SQL concepts with practical and scenario-based questions tailored for interviews. Enhance your foundational understanding of PL/SQL development, syntax, triggers, cursors, and data handling through approachable, real-world examples.

1. PL/SQL Block Structure

   Which section of a PL/SQL block is mandatory and must contain at least one executable statement?

   1. EXCEPTION
   2. BEGIN
   3. COMMIT
   4. DECLARE

   Explanation: The BEGIN section is the only mandatory part of a PL/SQL block and must include at least one executable statement; even a NULL statement suffices. The DECLARE section is optional and used for declaring variables. EXCEPTION is also optional and handles exceptions. COMMIT is not a section of a PL/SQL block, but a command for saving transactions.

2. Cursor Types

   Which type of cursor is created automatically by PL/SQL when processing SQL statements like SELECT INTO, INSERT, UPDATE, or DELETE?

   1. Dynamic cursor
   2. Static cursor
   3. Explicit cursor
   4. Implicit cursor

   Explanation: Implicit cursors are automatically created by PL/SQL whenever a SQL statement is executed. Explicit cursors need to be defined and managed by the programmer. Dynamic and static cursors are not standard PL/SQL terms; dynamic may refer to runtime SQL but is not a default cursor type. Static cursor could be a distractor, as PL/SQL doesn't officially categorize cursors this way.

3. Exception Handling

   In a PL/SQL block, which keyword is used to define the exception handling section for catching errors?

   1. ERROR
   2. EXCEPTION
   3. CATCH
   4. RESCUE

   Explanation: The EXCEPTION keyword marks the beginning of the exception handling section within a PL/SQL block. ERROR and CATCH are not valid PL/SQL keywords; CATCH is used in other languages like Python or Java. RESCUE is also incorrect and more related to other programming languages' exception handling.

4. Variable Declaration

   Which of the following is the correct way to declare a variable named salary as a NUMBER in PL/SQL?

   1. salary NUMBER;
   2. DECLARE salary NUMBER;
   3. NUMBER salary;
   4. VARIABLE salary NUMBER;

   Explanation: The correct syntax for declaring a variable in PL/SQL is to specify the variable name followed by the data type, separated by a space. NUMBER salary; is incorrect syntax because the data type comes after the variable identifier. DECLARE is used to open the declaration section, not to declare individual variables. VARIABLE is used in SQL*Plus, not in PL/SQL blocks.

5. Trigger Timing

   If you want a trigger to execute before data is inserted into a table, which timing option should you specify?

   1. PRE
   2. INSTEAD OF
   3. BEFORE
   4. AFTER

   Explanation: The BEFORE timing option ensures the trigger runs before the triggering statement is executed, allowing you to make changes or checks. AFTER triggers run after the statement; INSTEAD OF is used for certain view triggers, not table triggers. PRE is not a valid PL/SQL timing keyword.

6. String Concatenation

   In PL/SQL, which operator is used to concatenate two character strings?

   1. u0026
   2. +
   3. CONCAT
   4. ||

   Explanation: The double pipe symbol (||) is used for string concatenation in PL/SQL. The plus sign (+) is used for numerical addition, not string joining. u0026 is used for substitution variables in SQL*Plus, not concatenation. CONCAT is a function, not an operator, in SQL.

7. Data Types

   Which PL/SQL data type should you use to store fixed-length character strings, such as a 10-character employee code?

   1. CHAR
   2. VARCHAR2
   3. NUMBER
   4. CCHAR

   Explanation: CHAR stores fixed-length character data, making it suitable for things like codes which always have the same number of characters. VARCHAR2 stores variable-length strings and can waste space if used for fixed-length values. NUMBER is for numeric values. CCHAR is not a valid PL/SQL data type.

8. FOR Loop Syntax

   Which of the following is a correct example of a basic FOR loop in PL/SQL that prints numbers 1 to 3?

   1. FOR (i=1; iu003C=3; i++) { PRINT i }
   2. FOR i := 1 TO 3 PRINT i END;
   3. LOOP FOR i = 1 TO 3 OUTPUT i END LOOP;
   4. FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;

   Explanation: In PL/SQL, the correct syntax uses FOR variable IN lower..upper LOOP ... END LOOP. The provided example uses this structure. The second option resembles C-style for loops, which are not valid in PL/SQL. The third and fourth options have incorrect syntax and keywords for PL/SQL.

9. Parameter Modes

   Which parameter mode in a PL/SQL procedure allows data to be passed both into and back from the procedure?

   1. OUT
   2. IN OUT
   3. IN
   4. RETURN

   Explanation: IN OUT mode lets a parameter accept a value when the procedure is called and return a new value when the procedure ends. IN mode only passes data in, not out. OUT is for sending data out of the procedure only; it doesn't bring a value in. RETURN is used for functions, not as a parameter mode.

10. Record Type Declaration

    What is the keyword to declare a composite variable that can hold a row structure with multiple fields in PL/SQL?

    1. RECORD
    2. STRUCT
    3. ROWTYPE
    4. TABLE

    Explanation: RECORD is used to define a composite variable with multiple fields, similar to a row structure. STRUCT is a common term in other programming languages but not in PL/SQL. TABLE is used for collections, not for record structures. ROWTYPE is used for variables based on entire rows of tables or cursors, not for custom composite structures.