Backend interview prep

Start

PL/SQL Interview Questions

Master Oracle PL/SQL with interview-focused questions covering PL/SQL Blocks, Variables, Loops, Cursors, Procedures, Functions, Packages, Exception Handling, Collections, Bulk Collect, FORALL, Dynamic SQL, and enterprise best practices.

Introduction

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension to SQL.

SQL is excellent for

  • Retrieving Data
  • Updating Data
  • Managing Tables

PL/SQL adds

  • Variables
  • Loops
  • Conditions
  • Procedures
  • Functions
  • Exception Handling
  • Packages
  • Cursors

PL/SQL is widely used in enterprise applications for

  • Banking
  • ERP
  • Payroll
  • Healthcare
  • Insurance

PL/SQL Architecture

flowchart LR

Application --> PLSQLBlock

PLSQLBlock --> SQLEngine

PLSQLBlock --> PLSQLEngine

SQLEngine --> OracleDatabase

1. What is PL/SQL?

Answer

PL/SQL is Oracle's procedural programming language built on top of SQL.

It combines

  • SQL
  • Variables
  • Control Statements
  • Loops
  • Exception Handling

into one programming language.

2. Why is PL/SQL used?

PL/SQL provides

  • Better Performance
  • Reduced Network Calls
  • Business Logic
  • Error Handling
  • Reusable Code

SQL vs PL/SQL

SQL PL/SQL
Declarative Procedural
Executes One Statement Executes Program Blocks
No Loops Supports Loops
No Variables Variables Supported
No Exception Handling Exception Handling Available

3. What is a PL/SQL Block?

A PL/SQL program is written inside a block.

Structure

DECLARE

BEGIN

EXCEPTION

END;

PL/SQL Block

flowchart TD

DECLARE --> BEGIN --> EXCEPTION --> END

4. What is the DECLARE section?

DECLARE contains

  • Variables
  • Constants
  • Cursors
  • User-defined Types

Example

DECLARE
    v_salary NUMBER;

5. What is the BEGIN section?

Contains executable statements.

Example

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello');
END;

6. What is the EXCEPTION section?

Handles runtime errors.

Example

EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not Found');

7. What are Variables?

Variables temporarily store data.

Example

DECLARE
    v_name VARCHAR2(100);

8. What are Constants?

Constant values cannot be changed.

Example

v_tax CONSTANT NUMBER := 18;

9. What is IF Statement?

Conditional execution.

Example

IF salary > 50000 THEN
   DBMS_OUTPUT.PUT_LINE('Senior');
END IF;

10. What loops are available?

PL/SQL supports

  • LOOP
  • WHILE LOOP
  • FOR LOOP

FOR LOOP

FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

Loop Flow

flowchart TD

Start --> Condition

Condition

Condition -- Yes --> Loop

Loop --> Condition

Condition

Condition -- No --> End

11. What is a Cursor?

Cursor stores query results.

Allows row-by-row processing.

Cursor Flow

flowchart LR

SQL --> Cursor --> Rows --> PLSQL

12. Types of Cursors?

  • Implicit Cursor
  • Explicit Cursor

13. What is an Implicit Cursor?

Oracle automatically creates it

for

  • INSERT
  • UPDATE
  • DELETE
  • SELECT INTO

14. What is an Explicit Cursor?

Created manually by the programmer.

Example

CURSOR emp_cursor IS
SELECT employee_id
FROM employees;

15. Cursor Lifecycle

Steps

Declare

↓

Open

↓

Fetch

↓

Close

16. What is a Procedure?

Procedure performs an action.

Example

CREATE PROCEDURE hello_proc
AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello');
END;

17. What is a Function?

Returns a value.

Example

CREATE FUNCTION get_bonus
RETURN NUMBER
IS
BEGIN
   RETURN 1000;
END;

Procedure vs Function

Procedure Function
Performs Task Returns Value
No Mandatory Return Return Required
Can Have OUT Parameters Used in Expressions

18. What are Parameters?

Parameter types

  • IN
  • OUT
  • IN OUT

19. What is Exception Handling?

Used to manage runtime errors.

Common exceptions

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • ZERO_DIVIDE
  • DUP_VAL_ON_INDEX

Exception Flow

flowchart LR

BEGIN --> Statement

Statement

Statement -- Success --> END

Statement

Statement -- Failure --> EXCEPTION --> HandleError

20. What is NO_DATA_FOUND?

Occurs when

SELECT INTO

returns no rows.

21. What is TOO_MANY_ROWS?

Occurs when

SELECT INTO

returns multiple rows.

22. What are Collections?

PL/SQL collections store multiple values.

Types

  • Associative Array
  • Nested Table
  • VARRAY

Collection Types

flowchart TD

Collections --> AssociativeArray

Collections --> NestedTable

Collections --> VARRAY

23. What is BULK COLLECT?

Fetches multiple rows

into memory

in one operation.

Example

SELECT employee_id
BULK COLLECT INTO ids
FROM employees;

Benefits

  • Faster Processing
  • Fewer Context Switches

24. What is FORALL?

Processes multiple DML statements

in one operation.

Example

FORALL i IN ids.FIRST..ids.LAST
    INSERT INTO emp_backup
    VALUES(ids(i));

BULK COLLECT + FORALL

flowchart LR

Database --> BulkCollect --> Memory --> FORALL --> Database

25. What is Dynamic SQL?

SQL generated during runtime.

Example

EXECUTE IMMEDIATE
'DELETE FROM employees WHERE employee_id=:1'
USING emp_id;

26. What is DBMS_OUTPUT?

Package used to display output.

Example

DBMS_OUTPUT.PUT_LINE('Hello');

27. Banking Example

Transfer Money

↓

Procedure

↓

Validate Balance

↓

Debit

↓

Credit

↓

Commit

28. Payroll Example

Function

↓

Calculate Bonus

↓

Return Bonus Amount

29. HR Example

Cursor

↓

Read Employees

↓

Update Salary

↓

Commit

30. Bulk Processing Example

Old Approach

1 Million

INSERTs

New Approach

BULK COLLECT

↓

FORALL

↓

Fast Processing

31. Common PL/SQL Mistakes

  • Ignoring exception handling
  • Fetching row-by-row unnecessarily
  • Not closing explicit cursors
  • Using dynamic SQL unnecessarily
  • Large commits
  • Missing bulk processing

Enterprise Best Practices

  • Write modular procedures and functions.
  • Use meaningful variable names.
  • Handle all expected exceptions.
  • Prefer BULK COLLECT for large queries.
  • Use FORALL for batch DML.
  • Avoid unnecessary context switching.
  • Keep procedures focused on one responsibility.
  • Minimize dynamic SQL.
  • Use packages for reusable business logic.
  • Test exception scenarios thoroughly.

Quick Revision

Topic Key Point
PL/SQL Oracle Programming Language
Block DECLARE → BEGIN → EXCEPTION → END
Variable Temporary Storage
Procedure Performs Task
Function Returns Value
Cursor Process Query Results
Implicit Cursor Automatic
Explicit Cursor Manual
Exception Error Handling
BULK COLLECT Bulk Fetch
FORALL Bulk DML
Dynamic SQL EXECUTE IMMEDIATE

Interview Tips

Interviewers frequently ask

  • What is PL/SQL?
  • SQL vs PL/SQL.
  • Explain a PL/SQL block.
  • Procedure vs Function.
  • Implicit vs Explicit Cursor.
  • Cursor lifecycle.
  • What is BULK COLLECT?
  • What is FORALL?
  • Explain Dynamic SQL.
  • Explain Exception Handling.

A strong interview answer is:

"PL/SQL extends SQL with procedural programming capabilities such as variables, loops, conditions, procedures, functions, cursors, and exception handling. It allows business logic to execute inside the Oracle Database, reducing network overhead and improving performance. For large data processing, BULK COLLECT and FORALL significantly reduce context switching between the PL/SQL engine and SQL engine."

Summary

PL/SQL is Oracle's procedural programming language that combines SQL with programming constructs such as variables, loops, cursors, procedures, functions, collections, and exception handling. Features like BULK COLLECT, FORALL, and Dynamic SQL enable developers to build high-performance, enterprise-grade database applications.

Understanding PL/SQL programming, cursor management, bulk processing, procedures, functions, and exception handling is essential for Oracle Developers, Database Engineers, DBAs, and Solution Architects working with Oracle-based enterprise systems.