Here are advanced PL/SQL interview questions along with precise and clear answers. These questions focus on more complex PL/SQL concepts, performance optimization, and practical application scenarios.

Advanced PL/SQL Interview Questions and Answers

What is the difference between a procedure and a function in PL/SQL? When would you use each?

Answer:

Procedure: A procedure is a subprogram that performs an action but does not return a value. Use it for operations like inserting, updating, or deleting data.

Function: A function returns a single value and can be used in SQL expressions. Use it when you need to compute and return a value.

Explain the concept of cursors in PL/SQL. What are implicit and explicit cursors?

Answer:

Cursor: A pointer to a context area that holds the result set of a query.

Implicit Cursor: Automatically created by Oracle for single SQL statements (e.g., SELECT INTO).

Explicit Cursor: Defined by the programmer for more complex queries that return multiple rows. You must declare, open, fetch, and close explicit cursors.

What are bulk collect and FORALL statements in PL/SQL? How do they improve performance?

Answer:

BULK COLLECT: A method to retrieve multiple rows in a single context switch between SQL and PL/SQL, which improves performance by reducing context switches.

FORALL: A PL/SQL statement that allows you to perform bulk DML operations (INSERT, UPDATE, DELETE) on collections, minimizing the number of context switches and speeding up processing.

DECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
emp_records emp_tab;
BEGIN
-- Bulk Collect
SELECT * BULK COLLECT INTO emp_records FROM employees;

-- FORALL
FORALL i IN emp_records.FIRST .. emp_records.LAST
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_records(i).employee_id;

END;

How do you implement exception handling in PL/SQL? Provide an example of a custom exception.

Answer:

Exception handling in PL/SQL is done using the EXCEPTION block, which allows you to handle runtime errors gracefully.

Custom Exception Example:

DECLARE
insufficient_funds EXCEPTION;
balance NUMBER := 100;
withdrawal_amount NUMBER := 150;
BEGIN
IF withdrawal_amount > balance THEN
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Insufficient funds for withdrawal.');
END;

What is a package in PL/SQL, and what are its components? Why are packages beneficial?

Answer:

A package is a schema object that groups related PL/SQL types, variables, and subprograms (procedures and functions) into a single unit.

Components:

Specification: Defines the public interface of the package (public procedures, functions, variables).

Body: Contains the implementation of the procedures and functions defined in the specification.

Benefits: Packages enhance modularity, improve security (by hiding implementation details), and can improve performance through better compilation.

What are database triggers in PL/SQL? Explain the different types and when to use them.

Answer:

Database Trigger: A stored PL/SQL code that automatically executes in response to certain events on a table or view.

Types:

Row-level Trigger: Executes for each row affected by the triggering event (e.g., INSERT, UPDATE, DELETE).

Statement-level Trigger: Executes once for the entire triggering event, regardless of the number of rows affected.

Use Case: Implementing business rules, enforcing data integrity, and auditing changes.

How can you improve the performance of PL/SQL code?

Answer:

Use bulk operations (BULK COLLECT and FORALL) for large data manipulations.

Minimize context switches by reducing the number of SQL statements within loops.

Use proper indexing strategies to speed up query execution.

Avoid unnecessary computations by using PL/SQL functions for repetitive calculations.

Use PRAGMA directives to optimize compiler behavior.

Explain the use of the autonomous transaction in PL/SQL. When would you use it?

Answer:

An autonomous transaction allows a PL/SQL block to execute independently of the main transaction. Changes made by the autonomous transaction can be committed or rolled back without affecting the main transaction.

Use Case: Logging or auditing actions that should occur regardless of the success or failure of the main transaction.

CREATE OR REPLACE PROCEDURE log_transaction IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log_table (log_message) VALUES ('Transaction occurred');
COMMIT;
END;

What is the difference between the ‘NULL’ and ‘NOT FOUND’ in PL/SQL?

Answer:

NULL: Represents the absence of a value in a variable or column. A variable can be assigned a NULL value explicitly.

NOT FOUND: An exception raised when a SELECT INTO statement does not return any rows. It indicates that the desired data could not be found.

How do you manage and handle performance tuning in PL/SQL? What tools do you use?

Answer:

Performance tuning involves analyzing query execution plans, identifying bottlenecks, and optimizing SQL queries.

Tools:

Oracle SQL Tuning Advisor: Provides recommendations for SQL optimization.

EXPLAIN PLAN: Analyzes how Oracle executes a SQL statement.

DBMS_PROFILER: Helps identify performance issues in PL/SQL code by profiling execution times.

Conclusion

These advanced PL/SQL interview questions and answers provide a comprehensive overview of critical PL/SQL concepts, allowing candidates to articulate their knowledge and experience effectively in interviews.

Advanced PL/SQL interview Questions