PL/SQL interview questions and answers

Table of contents

• Explain the concept of a PL/SQL block and its structure.
• How do you handle exceptions in PL/SQL?
• Advantages of stored procedures and functions in PL/SQL.
• Describe the PL/SQL packages and their advantages.
• Improve the performance of a PL/SQL program?
• Differences between SQL and PL/SQL
• What is the use of WHERE CURRENT OF in cursors?
• What are SQL functions?
• What is a Sub Query? Describe its Types
• What are Joins and how many types of Joins are there?
• COMMIT, ROLLBACK, and SAVEPOINT statements in PL/SQL

1. What is PL/SQL and how does it differ from SQL?

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension to SQL. It allows you to write programs that include procedural logic, such as loops, conditionals (IF statements), and exception handling, alongside SQL commands. PL/SQL is used for building powerful, reusable code blocks like procedures, functions, triggers, and packages, which can be stored in the database.

SQL (Structured Query Language), on the other hand, is a standard language for querying and manipulating data in relational databases. SQL is primarily declarative, focusing on what to retrieve or modify (using SELECT, INSERT, UPDATE, DELETE), but lacks procedural constructs like loops, variables, and control statements.

SQL Example: This SQL query fetches the salary of an employee from the employees table.

SELECT salary FROM employees WHERE employee_id = 101;

PL/SQL Example: In this PL/SQL block, we not only query the salary but also add business logic to process and update it.

DECLARE
  emp_salary NUMBER;
BEGIN
  -- Retrieve the current salary
  SELECT salary INTO emp_salary FROM employees WHERE employee_id = 101;
  
  -- Apply a 10% raise to the salary
  emp_salary := emp_salary * 1.1;
  
  -- Update the salary in the database
  UPDATE employees SET salary = emp_salary WHERE employee_id = 101;

  -- Output the new salary
  DBMS_OUTPUT.PUT_LINE('New Salary: ' || emp_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;


In the PL/SQL example:

We declare a variable emp_salary.

We retrieve the salary, apply a raise, and then update it in the employees table.

The block also includes exception handling to deal with potential errors, like an employee not being found.

2. Explain the concept of a PL/SQL block and its structure.

A PL/SQL block is the basic unit of a PL/SQL program. It consists of three main sections: the declarative section, the executable section, and the exception-handling section. The structure of a PL/SQL block is as follows:

Declarative Section (optional): This section is used to declare variables, constants, and other objects that will be used in the block. It starts with the keyword DECLARE.

Executable Section (mandatory): This section contains the actual PL/SQL code to be executed. It starts with the keyword BEGIN and ends with the keyword END.

Exception-Handling Section (optional): This section is used to handle exceptions or errors that occur during the execution of the executable section. It starts with the keyword EXCEPTION.

Example structure:

DECLARE
   -- Declarations (variables, constants, etc.)
BEGIN
   -- Executable statements
EXCEPTION
   -- Exception handling statements
END;

3. What are the different types of PL/SQL collections? Explain each type briefly.

PL/SQL supports three types of collections: associative arrays (index-by tables), nested tables, and VARRAYs (variable-size arrays).

Associative Arrays (Index-by Tables): These are key-value pairs where each element is indexed by a unique key, which can be either a string or an integer. They are similar to hash tables and are useful for temporary storage of data.

TYPE employees_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
employees employees_type;

Nested Tables: These are similar to database tables and can be used to store an unordered set of elements. They can be stored in database columns and can be nested within other collections.

TYPE nested_table_type IS TABLE OF VARCHAR2(100);
nested_table nested_table_type;

VARRAYs (Variable-Size Arrays): These are arrays with a fixed maximum size specified at the time of declaration. They are useful when the number of elements is known and fixed.

TYPE varray_type IS VARRAY(10) OF VARCHAR2(100);
varray varray_type;

4. How do you handle exceptions in PL/SQL?

In PL/SQL, exceptions are handled using the EXCEPTION section of a PL/SQL block. This section allows you to define specific actions to take when certain errors or exceptions occur during the execution of the block. Exceptions can be predefined (such as NO_DATA_FOUND or TOO_MANY_ROWS) or user-defined.

Here’s the general structure for handling exceptions:

BEGIN
   -- Executable statements
EXCEPTION
   WHEN exception_name1 THEN
      -- Actions to take when exception_name1 occurs
   WHEN exception_name2 THEN
      -- Actions to take when exception_name2 occurs
   WHEN OTHERS THEN
      -- Actions to take for all other exceptions
END;

In this example, the ZERO_DIVIDE exception is specifically handled, while any other unexpected exceptions are caught by the WHEN OTHERS clause.

5. What is a cursor in PL/SQL? Differentiate between implicit and explicit cursors.

A cursor in PL/SQL is a pointer to a result set of a query. It allows you to fetch and process each row of the result set one at a time. There are two types of cursors in PL/SQL: implicit cursors and explicit cursors.

Implicit Cursors: These are automatically created by Oracle for all SQL SELECT statements that return a single row, as well as for INSERT, UPDATE, and DELETE statements. You do not need to declare or open implicit cursors; they are managed by PL/SQL. For example, a SELECT INTO statement uses an implicit cursor.

BEGIN
SELECT column_name INTO variable_name FROM table_name WHERE condition;
END;

Explicit Cursors: These are defined by the programmer for queries that return multiple rows. You need to declare, open, fetch, and close explicit cursors explicitly. Explicit cursors provide more control over the context area and are used for more complex query operations.

DECLARE
   CURSOR cursor_name IS SELECT column_name FROM table_name WHERE condition;
   variable_name table_name.column_name%TYPE;
BEGIN
   OPEN cursor_name;
   FETCH cursor_name INTO variable_name;
   WHILE cursor_name%FOUND LOOP
      -- Process each row
      FETCH cursor_name INTO variable_name;
   END LOOP;
   CLOSE cursor_name;
END;

6. Explain the use and advantages of stored procedures and functions in PL/SQL.

Stored procedures and functions are PL/SQL blocks that are stored in the database and can be executed as needed. They allow for modular programming, code reuse, and improved performance.

Stored Procedures:

These are PL/SQL blocks that perform a specific task or a set of tasks. They do not return a value but can have output parameters to return multiple values. Stored procedures are useful for encapsulating complex business logic and operations.

CREATE OR REPLACE PROCEDURE procedure_name (param1 IN data_type, param2 OUT data_type) IS
BEGIN
   -- Procedure body
END;

Functions:

These are similar to stored procedures but are designed to return a single value. Functions can be used in SQL statements wherever an expression is allowed.

CREATE OR REPLACE FUNCTION function_name (param1 IN data_type) RETURN return_data_type IS
BEGIN
   -- Function body
   RETURN value;
END;

Advantages:

1. Modularity: Procedures and functions promote modularity by breaking down complex programs into smaller, manageable, and reusable components.
2. Reusability: Once created, stored procedures and functions can be reused in multiple programs, reducing redundancy and maintenance effort.
3. Performance: Executing stored procedures and functions can be faster than running individual SQL statements because they are precompiled and optimized by the database.
4. Security: Access to data can be controlled through stored procedures and functions, providing an additional layer of security.
5. Maintainability: Changes can be made in a single place (the procedure or function) without affecting the overall application, making maintenance easier.

7. What are triggers in PL/SQL and when would you use them?

Triggers are special types of stored procedures that are automatically executed, or “triggered,” in response to specific events on a particular table or view. They can be used to enforce business rules, validate data, audit changes, and maintain data integrity.

Triggers can be categorized based on the following criteria:

Event: Triggers can fire in response to events like INSERT, UPDATE, and DELETE.

Timing: Triggers can be defined to execute BEFORE or AFTER the triggering event.

Level: Triggers can be defined at the statement level (once per triggering statement) or the row level (once per affected row).

Example of a BEFORE INSERT row-level trigger:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
   -- Trigger body
   :NEW.column_name := value; -- Modify the new row being inserted
END;

8. Describe the PL/SQL packages and their advantages.

PL/SQL packages are a collection of related procedures, functions, variables, cursors, and other PL/SQL constructs grouped together as a single unit. A package consists of two parts: the specification (spec) and the body. The specification declares the public elements that can be accessed from outside the package, while the body contains the implementation of these elements along with any private elements that are not accessible from outside.

Example Structure:

Package Specification:

CREATE OR REPLACE PACKAGE package_name IS
   PROCEDURE procedure_name(param IN data_type);
   FUNCTION function_name(param IN data_type) RETURN return_data_type;
END package_name;

Package Body:

CREATE OR REPLACE PACKAGE BODY package_name IS
   PROCEDURE procedure_name(param IN data_type) IS
   BEGIN
      -- Procedure implementation
   END procedure_name;
   
   FUNCTION function_name(param IN data_type) RETURN return_data_type IS
   BEGIN
      -- Function implementation
      RETURN value;
   END function_name;
END package_name;

Advantages:

1. Modularity: Packages allow for grouping related objects, making the code easier to manage and understand.
2. Encapsulation: Packages hide the implementation details and expose only the necessary components, enhancing security and encapsulation.
3. Reusability: Once created, packages can be reused across different applications and sessions.
4. Performance: Packages improve performance by reducing the number of context switches between the PL/SQL engine and the SQL engine. They also support session-level stateful features.
5. Maintainability: Changes can be made in a single place (the package) without affecting other parts of the application, making maintenance easier.

9. What is dynamic SQL in PL/SQL and when would you use it?

Dynamic SQL refers to the construction and execution of SQL statements at runtime, as opposed to static SQL, which is predefined at compile time. Dynamic SQL is useful when the exact structure of a SQL statement is not known until runtime or when you need to execute a wide variety of SQL statements dynamically.

Dynamic SQL can be executed using:

EXECUTE IMMEDIATE: For single-row queries or DML statements.

DBMS_SQL Package: For complex or multi-row queries.

Example using EXECUTE IMMEDIATE:

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

Example using DBMS_SQL:

DECLARE
   cursor_handle INTEGER;
   sql_stmt VARCHAR2(1000) := 'SELECT first_name, last_name FROM employees WHERE department_id = :dept_id';
BEGIN
   cursor_handle := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor_handle, sql_stmt, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(cursor_handle, ':dept_id', dept_id);
   DBMS_SQL.EXECUTE(cursor_handle);
   -- Fetch and process rows
   DBMS_SQL.CLOSE_CURSOR(cursor_handle);
END;

When to Use Dynamic SQL:

1. When the SQL statement structure is not known until runtime.
2. When you need to construct SQL statements based on user input or application logic.
3. When performing operations that require dynamic table or column names.
4. When executing DDL statements, which are not allowed in static PL/SQL.

Dynamic SQL provides flexibility but should be used judiciously due to potential security risks such as SQL injection.

10. How can you improve the performance of a PL/SQL program?

Improving the performance of a PL/SQL program involves several strategies, including optimizing SQL statements, efficient use of PL/SQL constructs, and minimizing resource consumption. Here are some key techniques:

1. Optimize SQL Statements: Ensure that SQL statements are written efficiently, using indexes, avoiding full table scans, and using appropriate join methods.
2. Use Bulk Operations: When dealing with large data sets, use bulk operations like BULK COLLECT and FORALL to minimize context switches between SQL and PL/SQL engines.

DECLARE
   TYPE num_tab IS TABLE OF NUMBER;
   l_numbers num_tab;
BEGIN
   SELECT column_name BULK COLLECT INTO l_numbers FROM table_name;
   FORALL i IN l_numbers.FIRST..l_numbers.LAST
      INSERT INTO another_table (column_name) VALUES (l_numbers(i));
END;

11. Does PL/SQL used in Salesforce?

PL/SQL (Procedural Language/Structured Query Language) is not used in Salesforce. Salesforce uses its own programming languages and technologies for database operations and business logic. Here are the primary languages and technologies used in Salesforce, which are often discussed in Salesforce interview questions:

1. Apex: Apex is Salesforce’s proprietary programming language, similar to Java, designed for writing business logic and managing data transactions within the Salesforce platform. It is used for creating triggers, controllers, and custom business logic.
2. SOQL (Salesforce Object Query Language): SOQL is similar to SQL but is specifically designed for querying data stored in Salesforce objects. It allows users to retrieve data from Salesforce databases.
3. SOSL (Salesforce Object Search Language): SOSL is used for performing text searches across multiple objects in Salesforce. It is useful for finding records based on text searches.
4. Visualforce: Visualforce is a framework used for creating custom user interfaces in Salesforce. It allows developers to create pages with custom layouts and styles.
5. Lightning Components and LWC (Lightning Web Components): These are modern frameworks for building dynamic web applications on the Salesforce platform. They provide a more flexible and efficient way to create user interfaces.

12. What is a PL/SQL cursor?

A cursor in PL/SQL is a pointer that allows you to retrieve and manipulate data from a result set one row at a time. It is used to process multiple rows from a SQL query in PL/SQL, as SQL operations in PL/SQL typically deal with single rows.

There are two types of cursors:

1. Implicit cursor: Automatically created by Oracle when a DML operation (like INSERT, UPDATE, DELETE) or a SELECT INTO query is executed.
2. Explicit cursor: Defined explicitly by the user to handle queries that return more than one row, allowing the rows to be processed individually.

13. What are the Differences between SQL and PL/SQL?

14. What is a Literal? Give an example of where it can be used?

A literal is a constant value that is not stored in a variable but is used directly in PL/SQL or SQL code. Literals can be numeric, string, or date values.

Example:
In the query

SELECT * FROM employees WHERE employee_id = 100;

the value 100 is a numeric literal.

Example of a string literal:

'John Doe', as in SELECT * FROM employees WHERE first_name = 'John';.

15. What is the use of WHERE CURRENT OF in cursors?

The WHERE CURRENT OF clause in PL/SQL is used in conjunction with an explicit cursor to refer to the current row being processed by the cursor. It allows you to perform DML operations (such as UPDATE or DELETE) on the row that the cursor is currently pointing to.

Example: If you’re fetching rows from a cursor and want to update the current row, you can write UPDATE employees SET salary = salary * 1.1 WHERE CURRENT OF employee_cursor;.

16. What is a PL/SQL Trigger? Give some examples of when “Triggers” are used.

A PL/SQL trigger is a stored procedure that automatically executes (or “fires”) in response to specific events on a particular table or view. Triggers can be defined to execute before or after operations like INSERT, UPDATE, or DELETE.

Example of use cases:

Enforcing business rules: A trigger can check whether certain conditions are met before data modification occurs.

Auditing: You can use triggers to log changes to a table.

Cascading actions: When deleting a row in a parent table, a trigger can automatically delete rows in the child table.

17. What is the difference between SQL and iSQL*Plus?

18. Define the order of precedence used in executing SQL statements?

19. What are SQL functions? Describe in brief different types of SQL functions.

SQL functions are built-in functions that perform specific operations on data and return results. These functions can be applied in queries to manipulate or retrieve information from databases.

Types of SQL Functions:

1. Aggregate Functions: Perform operations on a group of rows and return a single result.
   Examples: COUNT(), SUM(), AVG(), MAX(), MIN().
2. Scalar Functions: Operate on individual values and return a single result.
   Examples: UPPER(), LOWER(), LENGTH(), ROUND(), SYSDATE().

20. What is a Dual Table? Explain with an example.

The Dual table is a special one-row, one-column table in Oracle databases that is used to execute queries that don’t require data from a table. It’s often used for selecting system values or calculating expressions.

Example:

SELECT SYSDATE FROM DUAL;

This query returns the current date and time from the system without querying a specific user table.

21. How should comments be written in PL/SQL code?

In PL/SQL, comments can be written in two ways:

1. Single-line comments: Use -- before the comment.
   Example:
   -- This is a single-line comment
SELECT * FROM employees;
2. Multi-line comments: Use /* */ to enclose the comment.
   Example:
   /* This is a multi-line comment */
SELECT * FROM employees;

22. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)?

• COUNT(*): Returns the total number of rows in a result set, including rows with NULL values.
• COUNT(expression): Returns the number of non-NULL values in a specific column or expression.
• COUNT(DISTINCT expression): Returns the count of unique non-NULL values in the specified column.

23. What is a Sub Query? Describe its Types.

A Subquery is a query nested inside another SQL query, which can be used to retrieve data that will be used in the main query.

Types of Subqueries:

1. Single-row Subquery: Returns a single row and is often used with =, <, or > operators.
   Example:
   SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
2. Multiple-row Subquery: Returns multiple rows and is used with operators like IN, ANY, or ALL.
   Example: SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

24. What is a MERGE statement?

The MERGE statement in PL/SQL is used to perform INSERT, UPDATE, or DELETE operations in a single statement, depending on whether a matching record exists in the target table. It’s often used in data warehousing to synchronize tables.

Example:

MERGE INTO employees e USING new_employees n ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN UPDATE SET e.salary = n.salary
WHEN NOT MATCHED THEN INSERT (employee_id, name, salary)
VALUES (n.employee_id, n.name, n.salary);

25. What are Joins and how many types of Joins are there?

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

Types of Joins:

1. Inner Join: Returns records that have matching values in both tables.
2. Left (Outer) Join: Returns all records from the left table and the matched records from the right table. Unmatched rows are filled with NULL.
3. Right (Outer) Join: Returns all records from the right table and the matched records from the left table. Unmatched rows from the left are filled with NULL.
4. Full (Outer) Join: Returns all records when there is a match in either table, and fills NULL for missing matches in both tables.
5. Cross Join: Returns the Cartesian product of the two tables, where each row of the first table is combined with all rows of the second table.

26. Explain all Joins used in Oracle 8i?

Oracle 8i supports the following joins:

1. Inner Join: Combines records from two tables when there is a match in the joined column.
   Example:SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
2. Left Outer Join: Returns all records from the left table and matched records from the right table.
   Example:SELECT e.name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
3. Right Outer Join: Returns all records from the right table and matched records from the left table.
   Example:SELECT e.name, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
4. Full Outer Join: Returns all records when there is a match in either table.
   Example:SELECT e.name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;

27. What is the Difference between implicit cursor and explicit cursor?

28. What are the differences between ROLLBACK and ROLLBACK TO statements in PL/SQL?

ROLLBACK: Undoes all changes made by the current transaction and returns the database to its previous state before the transaction started.

ROLLBACK TO: Rolls back the transaction only to a specific SAVEPOINT created earlier, leaving the transaction open but undoing changes after the specified savepoint.

29. What is the purpose of COMMIT, ROLLBACK, and SAVEPOINT statements in PL/SQL?

• COMMIT: Saves all the changes made during the current transaction to the database permanently.
• ROLLBACK: Undoes changes made in the current transaction, reverting the database to its previous state before the transaction started.
• SAVEPOINT: Creates a point within a transaction to which you can later roll back using the ROLLBACK TO statement, allowing partial rollback without undoing the entire transaction.

30. What is the purpose of the WHEN condition in the trigger?

The WHEN condition in a PL/SQL trigger is used to specify an additional condition that must be met before the trigger fires. It acts like a filter, ensuring that the trigger is only executed for rows that satisfy the specified condition.

Example:

CREATE OR REPLACE TRIGGER salary_update 
BEFORE UPDATE ON employees FOR EACH ROW 
WHEN (new.salary > old.salary) 
BEGIN -- Trigger logic here END; 

In this example, the trigger will only execute if the new salary value is greater than the old salary value.