For a 3-year experienced PL/SQL Developer, interview questions would focus on intermediate concepts, optimization techniques, and practical experience. Here are some of the most common interview questions tailored for someone with 3 years of PL/SQL experience:

1. What are the different types of PL/SQL blocks?

Answer: PL/SQL supports three types of blocks:

1. Anonymous Blocks: Blocks that are not named and cannot be stored in the database. These are executed once and discarded.

2. Named Blocks: Blocks that are stored in the database, such as procedures, functions, packages, and triggers.

3. Trigger Blocks: Special kind of PL/SQL block executed in response to certain events in the database.

1. What is a MERGE statement, and when do you use it?

Answer: The MERGE statement is used to perform INSERT, UPDATE, or DELETE operations in a single statement. It is commonly used for upserts—when you want to insert a row if it doesn’t exist, or update the row if it does.

Example:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.column1 = s.column1
WHEN NOT MATCHED THEN
INSERT (t.id, t.column1) VALUES (s.id, s.column1);

1. Explain the differences between ROWNUM and ROW_NUMBER()?

Answer:

ROWNUM: It is a pseudocolumn in Oracle that returns the row number for each row in the result set, starting with 1. However, its values are assigned before any sorting operation.

ROW_NUMBER(): It is an analytical function that assigns a unique number to each row in the result set, based on the specified ORDER BY clause, and it works after sorting the result.

1. What is a cursor, and what are the different types of cursors in PL/SQL?

Answer:

A cursor is a pointer to the result set of a query. It allows row-by-row processing of the result set.

Types of Cursors:

Implicit Cursor: Automatically created by Oracle when an SQL statement is executed. For example, DML operations (INSERT, UPDATE, DELETE).

Explicit Cursor: Defined and controlled by the programmer, allowing for more complex query operations and row-by-row handling.

1. What is the difference between TRUNCATE and DELETE?

Answer:

DELETE: Removes rows one at a time and logs individual row deletions, which means it can be rolled back. It also allows a WHERE clause to delete specific rows.

TRUNCATE: Removes all rows in a table without logging individual row deletions, making it faster. It cannot be rolled back and resets any auto-increment counters.

1. How do you handle exceptions in PL/SQL? Can you raise custom exceptions?

Answer: Exceptions are handled using the EXCEPTION block. PL/SQL provides predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, etc. You can also raise custom exceptions using the RAISE statement.

Example:

DECLARE
insufficient_balance EXCEPTION;
BEGIN
-- Business logic
IF balance < 1000 THEN
RAISE insufficient_balance;
END IF;
EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Insufficient balance in the account');
END;

1. What are packages in PL/SQL, and why are they used?

Answer: A package is a group of related procedures, functions, variables, and other elements. They are used to modularize code and encapsulate logic. Using packages improves performance because package code is loaded once into memory when the package is first called.

1. What are materialized views, and how are they different from views?

Answer:

Materialized View: A database object that stores the result of a query physically. It can be refreshed periodically to reflect changes in the base tables.

View: A logical representation of a query result set that doesn’t store data itself but retrieves it from the base tables at query time.

1. What are the different types of joins in PL/SQL?

Answer:

INNER JOIN: Returns rows that have matching values in both tables.

LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table return NULL.

RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. Non-matching rows from the left table return NULL.

FULL OUTER JOIN: Returns all rows when there is a match in either table.

CROSS JOIN: Returns the Cartesian product of the two tables.

1. What are bulk binds in PL/SQL, and how do they improve performance?

Answer: Bulk binds are used to improve performance when a PL/SQL block needs to process multiple rows of data by reducing context switches between PL/SQL and SQL engines. The FORALL and BULK COLLECT statements are used for this.

Example of BULK COLLECT:

DECLARE
TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
l_emp_tab t_emp_tab;
BEGIN
SELECT * BULK COLLECT INTO l_emp_tab FROM employees WHERE department_id = 10;
END;

1. Explain the concept of PRAGMA EXCEPTION_INIT?

Answer: PRAGMA EXCEPTION_INIT is used to associate an Oracle error number with a user-defined exception, so that you can handle specific Oracle errors using the EXCEPTION block.

Example:

DECLARE
e_invalid_number EXCEPTION;
PRAGMA EXCEPTION_INIT(e_invalid_number, -1722); -- Associate ORA-01722 with the exception
BEGIN
-- Some logic that might raise ORA-01722
EXCEPTION
WHEN e_invalid_number THEN
DBMS_OUTPUT.PUT_LINE('Invalid number encountered');
END;

1. What is a trigger, and what are the different types of triggers in PL/SQL?

Answer: A trigger is a stored procedure that is automatically executed in response to certain events on a table or view. Types of triggers include:

Before Trigger: Executes before the DML event.

After Trigger: Executes after the DML event.

Row-Level Trigger: Executes once for each row affected by the DML event.

Statement-Level Trigger: Executes once for the entire DML statement, regardless of the number of rows affected.

1. What are collections in PL/SQL, and what are the types?

Answer: Collections are data types that allow you to store multiple elements. There are three types in PL/SQL:

Associative Arrays (Index-by Tables): Indexed by strings or integers.

Nested Tables: Unbounded size, can be sparse.

VARRAYs: Fixed size, elements can be referenced using indices.

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

Answer:

Function: Must return a value and can be called in SQL statements.

Procedure: Does not have to return a value and is primarily used for performing tasks rather than computing values.

1. What is dynamic SQL, and how do you execute it in PL/SQL?

Answer: Dynamic SQL allows the execution of SQL statements that are constructed at runtime. It is used for cases where you don't know the exact structure of the SQL query at compile time. EXECUTE IMMEDIATE is used to execute dynamic SQL.

Example:

DECLARE
sql_stmt VARCHAR2(1000);
BEGIN
sql_stmt := 'UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10';
EXECUTE IMMEDIATE sql_stmt;
END;

1. What are the different types of exceptions in PL/SQL?

Answer:

Predefined Exceptions: Standard Oracle errors such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE.

User-defined Exceptions: Custom exceptions raised by the user.

1. What are autonomous transactions, and how are they useful?

Answer: An autonomous transaction is a transaction that runs independently of the main transaction and can commit or roll back changes without affecting the parent transaction. They are useful in logging, auditing, and error handling.

**Example
Answer (continued):

Example of an autonomous transaction:

CREATE OR REPLACE PROCEDURE log_error (p_error_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log (error_message, log_time)
VALUES (p_error_msg, SYSDATE);
COMMIT; -- Commit the autonomous transaction
END log_error;

1. What is a WITH clause, and why is it used?

Answer: The WITH clause, also known as a Common Table Expression (CTE), is used to define a temporary result set that can be referenced multiple times within a query. It improves readability and can be used to simplify complex queries, especially when performing multiple joins or subqueries.

Example:

WITH dept_salary AS (
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT d.department_name, ds.total_salary
FROM departments d
JOIN dept_salary ds ON d.department_id = ds.department_id;

1. Explain the FORALL statement and how it differs from a regular FOR loop in PL/SQL.

Answer: The FORALL statement is used to perform bulk operations on collections. It minimizes context switching between the PL/SQL and SQL engines, making it more efficient than a regular FOR loop, especially when performing large INSERT, UPDATE, or DELETE operations.

Example:

DECLARE
TYPE numlist IS TABLE OF NUMBER;
emp_ids numlist := numlist(1001, 1002, 1003);
BEGIN
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_ids(i);
END;

1. How do you optimize PL/SQL performance, especially when handling large datasets?

Answer: Performance optimization in PL/SQL can be done through:

Use of bulk operations (BULK COLLECT and FORALL) to minimize context switches.

Use of bind variables to avoid hard parsing of SQL statements.

Avoiding unnecessary cursors and using implicit cursors whenever possible.

Using EXISTS or IN for filtering rows rather than fetching data and performing operations in PL/SQL.

Indexing the database tables appropriately to speed up queries.

Limiting data retrieval by filtering data in SQL, not PL/SQL.

Using materialized views for complex and frequently run queries.

1. What is the difference between BULK COLLECT and FORALL?

Answer:

BULK COLLECT: It is used to fetch multiple rows into a collection in a single fetch operation, thereby reducing the number of context switches between PL/SQL and SQL engines.

FORALL: It is used to perform bulk INSERT, UPDATE, or DELETE operations using collections, again minimizing context switching.

1. What are hints in Oracle SQL, and how do they affect query execution?

Answer: Hints are suggestions to the Oracle SQL optimizer on how to execute a query. They can influence decisions such as which index to use, which join method to employ, or how to parallelize a query. Hints are provided using the /*+ hint */ syntax.

Example:

SELECT /*+ INDEX(employees emp_idx) */ * FROM employees WHERE employee_id = 1001;

1. How do you debug a PL/SQL procedure or function?

Answer:

Using DBMS_OUTPUT: Print intermediate results or variable values using the DBMS_OUTPUT.PUT_LINE statement.

PL/SQL Developer: Use tools like PL/SQL Developer, SQL Developer, or Toad to set breakpoints and step through the code.

EXCEPTION handling: Use the EXCEPTION block to catch and display error messages.

DBMS_TRACE: A package that allows tracing the execution of PL/SQL programs.

Oracle SQL Developer: It provides an integrated debugging tool to step through code and inspect variables.

1. What is the use of the OUT parameter in a procedure or function?

Answer: The OUT parameter is used to return values from a procedure or function. Unlike IN parameters, which only pass values into a procedure or function, OUT parameters allow for data to be passed back to the calling environment.

Example:

CREATE OR REPLACE PROCEDURE get_employee_name (
p_employee_id IN NUMBER,
p_employee_name OUT VARCHAR2
) AS
BEGIN
SELECT first_name || ' ' || last_name INTO p_employee_name
FROM employees WHERE employee_id = p_employee_id;
END;

1. What is the difference between %TYPE and %ROWTYPE?

Answer:

%TYPE: It is used to declare a variable that takes the data type of a column or another variable.

%ROWTYPE: It is used to declare a variable that takes the row structure of a table or a cursor.

Example:

DECLARE
v_salary employees.salary%TYPE;
v_employee employees%ROWTYPE;
BEGIN
-- v_salary is of the same type as the salary column in employees table
-- v_employee is of the same structure as a row in the employees table
END;

1. What is a mutating trigger, and how do you resolve it?

Answer: A mutating trigger occurs when a trigger tries to query or modify the same table that invoked the trigger, resulting in an inconsistent data state.

Solutions: - Use an autonomous transaction to avoid the mutating error. - Switch to using AFTER triggers instead of BEFORE triggers. - Store necessary data in package variables and process the changes after the triggering statement completes.

1. What are analytic functions in Oracle, and can you give an example?

Answer: Analytic functions perform calculations over a set of rows related to the current row. They differ from aggregate functions because they can return multiple rows for each group, and they do not cause rows to be grouped into a single output row.

Example of RANK:

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

These questions should give you a good idea of the topics commonly covered in PL/SQL interviews for candidates with 3 years of experience. Interviewers typically focus on your understanding of core PL/SQL features, your ability to optimize and handle large data sets, and your practical experience with database management and development.