PL SQL Interview Question

Top 50 PL/SQL (Procedural Language/Structured Query Language) interview questions along with answers and examples. 

1. What is PL/SQL?

   - Answer: PL/SQL is a procedural language designed specifically for the Oracle Database. It integrates SQL statements with procedural constructs, allowing developers to create robust and efficient database applications.

2. Explain the difference between SQL and PL/SQL.

   - Answer: SQL is a query language used to interact with databases, while PL/SQL is a procedural language that extends SQL to include procedural constructs like loops and conditional statements.

3. What is a stored procedure?

   - Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It is stored in the database and can be invoked by applications.

4. How do you declare variables in PL/SQL?

   - Answer: Variables in PL/SQL are declared using the

DECLARE keyword. Example:

  DECLARE

     emp_name VARCHAR2(50);

     emp_salary NUMBER(8,2);

  BEGIN

     -- Statements

  END;

 

5. Explain the use of cursors in PL/SQL.

   - Answer: Cursors in PL/SQL are used to process the result set of a query. There are two types: explicit and implicit cursors. Example:

  DECLARE

     CURSOR emp_cursor IS SELECT employee_name FROM employees;

     emp_record emp_cursor%ROWTYPE;

  BEGIN

     OPEN emp_cursor;

     LOOP

        FETCH emp_cursor INTO emp_record;

        EXIT WHEN emp_cursor%NOTFOUND;

        -- Process the record

     END LOOP;

     CLOSE emp_cursor;

  END;

 

6. What is an exception in PL/SQL?

   - Answer: An exception is a runtime error that occurs during the execution of a PL/SQL block. Exceptions are handled using the EXCEPTION section in PL/SQL blocks. Example:

  BEGIN

     -- Statements

  EXCEPTION

     WHEN others THEN

        -- Handle the exception

  END;

 

7. Explain the purpose of triggers in PL/SQL.

   - Answer: Triggers in PL/SQL are special types of stored procedures that are automatically executed (or "triggered") in response to events such as INSERT, UPDATE, or DELETE on a specific table.

8. What is the difference between a function and a procedure?

   - Answer: A function returns a value, while a procedure does not. Functions are typically used for computations, and procedures are used for performing an action.

9. How do you handle NULL values in PL/SQL?

   - Answer: NULL values can be handled using the

IS NULL or IS NOT NULL conditions in IF statements or WHERE clauses. Example:

  IF emp_salary IS NULL THEN

     -- Handle NULL value

  END IF;

 

10. Explain the concept of packages in PL/SQL.

- Answer: Packages are logical structures that encapsulate related procedures, functions, variables, and other PL/SQL constructs. They help organize and modularize code.

11. What is a trigger in PL/SQL, and how is it different from a stored procedure?

- Answer: A trigger is a set of instructions that are automatically executed (or "triggered") in response to specific events on a particular table or view. Unlike stored procedures, triggers are associated with events rather than being explicitly called.

12. Explain the use of the FORALL statement in PL/SQL.

- Answer: The FORALL statement in PL/SQL is used for bulk processing of collections, allowing multiple DML (Data Manipulation Language) operations to be performed in a single statement. Example:

 

   FORALL i IN indices_of_collection

      INSERT INTO my_table VALUES my_collection(i);

  

13. What is dynamic SQL in PL/SQL, and how is it implemented?

- Answer: Dynamic SQL allows the generation and execution of SQL statements at runtime. It is implemented using the EXECUTE IMMEDIATE statement. Example:

 

   EXECUTE IMMEDIATE 'SELECT * FROM my_table';

  

14. Explain the purpose of the BULK COLLECT feature in PL/SQL.

- Answer: BULK COLLECT is used to fetch multiple rows at once into a collection, reducing context switches between the PL/SQL and SQL engines and improving performance. Example:

 

   DECLARE

      TYPE emp_collection IS TABLE OF employees%ROWTYPE;

      emp_data emp_collection;

   BEGIN

      SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;

      -- Process emp_data collection

   END;

  

15. How do you raise a user-defined exception in PL/SQL?

- Answer: User-defined exceptions can be raised using the RAISE statement. Example:

 

   DECLARE

      my_exception EXCEPTION;

   BEGIN

      RAISE my_exception;

   EXCEPTION

      WHEN my_exception THEN

         -- Handle the user-defined exception

   END;

  

16. Explain the difference between a function-based and a statement-based trigger.

- Answer: A function-based trigger is executed for each row affected by the triggering statement, while a statement-based trigger is executed once regardless of the number of affected rows.

17. How do you use the RETURNING INTO clause in PL/SQL?

- Answer: The RETURNING INTO clause is used to retrieve values from DML operations into PL/SQL variables. Example:

 

   DECLARE

      v_employee_name employees.employee_name%TYPE;

   BEGIN

      INSERT INTO employees VALUES (1, 'John Doe') RETURNING employee_name INTO v_employee_name;

      -- Process v_employee_name

   END;

  

18. What is the purpose of the AUTONOMOUS_TRANSACTION pragma in PL/SQL?

- Answer: The AUTONOMOUS_TRANSACTION pragma allows a transaction to be independent of the calling transaction, enabling the use of COMMIT and ROLLBACK within the autonomous transaction.

19. Explain the NOCOPY hint in PL/SQL parameters.

- Answer: The NOCOPY hint is used to indicate that the actual parameter should not be copied to the formal parameter, reducing memory usage and improving performance.

20. How do you handle exceptions in PL/SQL when using bulk operations?

- Answer: Bulk exceptions can be handled using the

FORALL statement with the SAVE EXCEPTIONS

clause, allowing continued processing even if some rows fail. Example:

 

   DECLARE

      ex EXCEPTION;

      PRAGMA EXCEPTION_INIT(ex, -24381);

   BEGIN

      FORALL i IN indices_of_collection

         INSERT INTO my_table VALUES my_collection(i);

   EXCEPTION

      WHEN ex THEN

         -- Handle bulk exceptions

   END;

  

21. What is a sequence in PL/SQL, and how is it used?

- Answer: A sequence is a database object used to generate unique numeric values. It is often used to generate primary key values for tables. Example:

 

   CREATE SEQUENCE emp_id_seq START WITH 1 INCREMENT BY 1;

  

22. Explain the difference between a function and a procedure in terms of return types.

- Answer: A function must return a value, whereas a procedure does not. Functions use the RETURN statement to return a value to the caller.

23. How do you use the RAISE_APPLICATION_ERROR procedure in PL/SQL?

- Answer: The RAISE_APPLICATION_ERROR procedure is used to generate a user-defined exception with a custom error message and error code. Example:

 

   RAISE_APPLICATION_ERROR(-20001, 'Custom error message');

  

24. What is the purpose of the UTL_FILE package in PL/SQL?

- Answer: The UTL_FILE package is used for reading from and writing to operating system files. It provides procedures to open, read, write, and close files.

25. Explain the use of the DBMS_OUTPUT package in PL/SQL.

- Answer: The DBMS_OUTPUT package is used for displaying debugging information during PL/SQL execution. It includes procedures like PUT_LINE to print output.

26. How do you handle transactions in PL/SQL?

- Answer: Transactions in PL/SQL are handled using the COMMIT and ROLLBACK statements. Transactions ensure the atomicity of database operations.

27. What is the purpose of the DBMS_SCHEDULER package in PL/SQL?

- Answer: The DBMS_SCHEDULER package is used for managing and scheduling jobs, such as running stored procedures or executing SQL scripts, at specified times.

28. Explain the concept of a cursor in PL/SQL and its types.

- Answer: A cursor is a pointer to a private SQL area that holds information about the processing of a SELECT or DML statement. Types include implicit, explicit, and parameterized cursors.

29. How do you use the EXECUTE IMMEDIATE statement in PL/SQL?

- Answer: The EXECUTE IMMEDIATE statement is used to execute dynamic SQL statements. It is often used when the SQL statement is not known until runtime.

30. What is the purpose of the DBMS_SQL package in PL/SQL?

- Answer: The DBMS_SQL package provides a low-level interface for dynamic SQL execution. It allows dynamic creation and execution of SQL statements.

31. Explain the concept of a trigger in PL/SQL and its types.

- Answer: A trigger is a set of instructions that are automatically executed in response to specific events in a database. Types include DML triggers (BEFORE or AFTER INSERT/UPDATE/DELETE) and statement triggers.

32. How can you handle exceptions in PL/SQL?

- Answer: Exceptions in PL/SQL are handled using the

EXCEPTION section. You can catch specific exceptions using WHEN clauses or use the generic OTHERS clause to catch any unhandled exceptions.

33. What is a pragma in PL/SQL?

- Answer: A pragma is a compiler directive that provides additional information to the compiler. For example, AUTONOMOUS_TRANSACTION is a pragma that allows a transaction to be independent.

34. Explain the purpose of the DBMS_ASSERT package in PL/SQL.

- Answer: The DBMS_ASSERT package is used for validating and sanitizing input values to prevent SQL injection attacks. It includes procedures like ENQUOTE_LITERAL and ENQUOTE_NAME.

35. How do you use the SAVE EXCEPTIONS clause with FORALL in PL/SQL?

- Answer: The SAVE EXCEPTIONS clause allows you to continue processing even if some rows fail in a

FORALL statement. You can then inspect the exceptions using the SQL%BULK_EXCEPTIONS collection.

36. What is the purpose of the PRAGMA EXCEPTION_INIT in PL/SQL?

- Answer: The PRAGMA EXCEPTION_INIT is used to associate an exception name with an Oracle error code. It allows you to raise a user-defined exception based on a specific Oracle error.

37. Explain the use of the NO_DATA_FOUND exception in PL/SQL.

- Answer: NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. It is commonly used in exception handling to handle situations where no data is retrieved.

38. How do you use the CASE statement in PL/SQL?

- Answer: The CASE statement is used for conditional logic in PL/SQL. It can be used in both simple and searched forms. Example:

  

    CASE

       WHEN condition1 THEN

          -- statements

       WHEN condition2 THEN

          -- statements

       ELSE

          -- statements

    END CASE;

   

39. Explain the purpose of the UTL_HTTP package in PL/SQL.

- Answer: The UTL_HTTP package is used for making HTTP requests from within PL/SQL. It provides procedures for sending HTTP requests and receiving responses.

40. What is the use of the RETURNING INTO clause in DML statements?

- Answer: The RETURNING INTO clause is used to return values from DML operations (e.g., INSERT, UPDATE) into PL/SQL variables. Example:

  

    INSERT INTO employees VALUES (1, 'John Doe') RETURNING employee_name INTO v_employee_name;

   

41. Explain the use of the BULK COLLECT and FORALL together in PL/SQL.

- Answer: Using BULK COLLECT and FORALL together in PL/SQL enables efficient bulk processing of data, reducing the number of context switches between the PL/SQL and SQL engines. Example:

  

    DECLARE

       TYPE emp_collection IS TABLE OF employees%ROWTYPE;

       emp_data emp_collection;

    BEGIN

       SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;

       FORALL i IN INDICES OF emp_data

          UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_data(i).employee_id;

    END;

   

42. What is the purpose of the PRAGMA EXCEPTION_INIT in PL/SQL?

- Answer: The PRAGMA EXCEPTION_INIT is used to associate a user-defined exception with a specific Oracle error code. It allows for more granular exception handling in PL/SQL programs.

43. Explain the use of the DBMS_METADATA package in PL/SQL.

- Answer: The DBMS_METADATA package is used to retrieve metadata information about database objects. It provides procedures for extracting DDL (Data Definition Language) statements for objects.

44. How do you use the VARRAY type in PL/SQL?

- Answer: A VARRAY (Variable Array) is a variable-size array type in PL/SQL. It is defined using the VARRAY keyword and can be used to store a collection of elements. Example:

  

    DECLARE

       TYPE my_varray IS VARRAY(3) OF VARCHAR2(20);

       names my_varray := my_varray('John', 'Alice', 'Bob');

    BEGIN

       -- Access elements of the VARRAY

       DBMS_OUTPUT.PUT_LINE(names(1)); -- Output: John

    END;

   

45. What is an autonomous transaction in PL/SQL?

- Answer: An autonomous transaction is an independent transaction within the scope of another transaction. It allows for separate commit and rollback operations, making it useful for logging or auditing.

46. Explain the purpose of the DBMS_APPLICATION_INFO package in PL/SQL.

- Answer: The DBMS_APPLICATION_INFO package is used to set additional information about the application in the Oracle database. It can be helpful for monitoring and tracing purposes.

47. How do you use the CONTINUE statement in a PL/SQL loop?

- Answer: The CONTINUE statement is used to skip the rest of the current iteration of a loop and proceed to the next iteration. Example:

  

    FOR i IN 1..10 LOOP

       IF i = 5 THEN

          CONTINUE; -- Skip iteration when i equals 5

       END IF;

       -- Process other iterations

    END LOOP;

   

48. What is the purpose of the UTL_MAIL package in PL/SQL?

- Answer: The UTL_MAIL package is used for sending email from within PL/SQL. It provides procedures for composing and sending emails.

49. Explain the use of the DBMS_XMLGEN package in PL/SQL.

- Answer: The DBMS_XMLGEN package is used for generating XML documents from SQL queries. It provides procedures to convert query results into XML format.

50. How do you use the RAISE statement to raise a predefined exception in PL/SQL?

- Answer: The RAISE statement is used to raise predefined exceptions in PL/SQL. Example:

  

    DECLARE

       v_balance NUMBER := 100;

    BEGIN

       IF v_balance < 0 THEN

          RAISE VALUE_ERROR; -- Raise predefined VALUE_ERROR exception

       END IF;

       -- Other statements

    END;