As organizations rely on robust, high-performance databases for mission-critical applications, recruiters must identify PL/SQL professionals who can build efficient, secure, and scalable database logic. PL/SQL is widely used in enterprise systems, banking, ERP platforms, and large-scale transactional applications where performance and data integrity are paramount.
This resource, "100+ PL/SQL Interview Questions and Answers," is tailored for recruiters to simplify the evaluation process. It covers a wide range of topics—from PL/SQL fundamentals to advanced database programming, including procedures, functions, packages, triggers, and exception handling.
Whether you're hiring PL/SQL Developers, Database Developers, Oracle Developers, or Backend Engineers, this guide enables you to assess a candidate’s:
For a streamlined assessment process, consider platforms like WeCP, which allow you to:
Save time, enhance your hiring process, and confidently hire PL/SQL professionals who can build reliable, efficient, and enterprise-ready database solutions from day one.
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension of SQL.
SQL is a declarative language used for data manipulation and querying, whereas PL/SQL is a procedural language that adds programming constructs like loops, conditions, variables, functions, procedures, and exception handling.
Key differences:
In summary:
SQL works with data; PL/SQL works with data and logic together, enabling full-fledged programmability inside the Oracle database.
PL/SQL provides a powerful set of features that make it ideal for building enterprise-grade applications inside Oracle databases.
Major features include:
In short:
PL/SQL offers a complete programming environment inside the Oracle database with performance, modularity, and error-handling capabilities.
A PL/SQL block is the fundamental unit of code in PL/SQL.
Every program in PL/SQL—functions, procedures, triggers, or anonymous scripts—is written as a block.
Structure of a PL/SQL block:
DECLARE -- Optional
-- variable declarations
BEGIN -- Mandatory
-- executable statements
EXCEPTION -- Optional
-- error-handling statements
END; -- Mandatory
Detailed components:
Why blocks?
They allow modular programming, nesting, and maintain structured flow.
Blocks can be nested inside other blocks too.
PL/SQL supports two types of blocks: anonymous and named.
Example:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END;
Example procedure:
CREATE OR REPLACE PROCEDURE greet IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END;
AspectAnonymous BlockNamed BlockNameNoYesStored in DBNoYesReusabilityOne-timeReusableInvocationRuns immediatelyCalled explicitlyIdeal ForTesting, temporary tasksBusiness logic, APIs
A variable in PL/SQL is a memory location used to store data temporarily during execution.
Variables must be declared in the DECLARE section.
Syntax:
variable_name datatype [NOT NULL] [:= initial_value];
Example:
DECLARE
l_name VARCHAR2(50);
l_age NUMBER := 25;
l_status VARCHAR2(10) NOT NULL := 'Active';
BEGIN
...
END;
%TYPE or %ROWTYPE.:=.Data types define what kind of data a variable can store (numbers, characters, dates, collections, etc.).
CREATE TYPEPurpose:
Data types ensure data validity, optimize storage, and maintain compatibility with SQL types.
The DECLARE section is optional and comes at the beginning of a PL/SQL block.
It is used to define everything needed before running the code.
Example:
DECLARE
l_count NUMBER;
CURSOR c1 IS SELECT * FROM employees;
BEGIN
...
END;
Without the declare section, you cannot define storage or custom objects.
The BEGIN section is the mandatory executable part of a PL/SQL block.
Example:
BEGIN
l_salary := l_salary + 1000;
INSERT INTO audit_log VALUES (...);
END;
The BEGIN section defines what the program does.
The EXCEPTION section is used for handling runtime errors gracefully.
It catches errors that occur in the BEGIN section and enables corrective action.
Syntax:
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
WHEN OTHERS THEN
...
END;
The EXCEPTION section is optional but strongly recommended for production code.
Built-in PL/SQL functions are ready-made functions provided by Oracle to perform commonly used operations.
These functions improve productivity and reduce the need to write repetitive code.
UPPER, LOWER, SUBSTR, INSTR, REPLACE, LTRIM, RTRIMABS, ROUND, CEIL, FLOOR, POWER, MODSYSDATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAYTO_CHAR, TO_NUMBER, TO_DATE, CASTSUM, AVG, COUNT, MIN, MAXNVL, NVL2, COALESCE, DECODEUSER, UID, SYS_CONTEXTBuilt-in functions are frequently used in both SQL and PL/SQL for data transformation and processing.
In Oracle, CHAR and VARCHAR2 are character data types, but they behave differently in terms of storage, padding, and performance.
CHAR(10) and store 'ABC', Oracle pads the remaining space with 7 blank spaces.VARCHAR2(10) will store only the actual character length ('ABC' = 3 bytes).FeatureCHARVARCHAR2LengthFixedVariableStorageAllocates maximum size alwaysAllocates only actual sizePaddingPads with spacesNo paddingPerformanceFaster for fixed-length fieldsMore efficient for varying sizesUse casesFixed fieldsMost text fields
A TYPE in PL/SQL is a user-defined data structure used to define customized data types such as records, collections (arrays), object types, and more.
You can define TYPE in two places:
Used only inside that block.
Example:
DECLARE
TYPE emp_record IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100)
);
l_emp emp_record;
BEGIN
...
END;
Stored in the database and reusable across programs.
Example:
CREATE TYPE number_list AS VARRAY(10) OF NUMBER;
A constant in PL/SQL is a variable whose value cannot change once assigned.
It protects data integrity by ensuring that certain values remain fixed during execution.
You must include the keyword CONSTANT, and assign an initial value.
Syntax:
constant_name CONSTANT datatype := value;
Example:
DECLARE
pi CONSTANT NUMBER := 3.14159;
max_retries CONSTANT INTEGER := 3;
BEGIN
...
END;
A comment in PL/SQL is used to document code for readability and maintainability.
The compiler ignores comments — they do not affect execution.
-- (double hyphen)-- This is a single-line comment
Multi-line comment:
Uses /* ... */
Example:
/*
This is a
multi-line comment
*/
The assignment operator in PL/SQL is:
:=
It assigns a value to a variable or constant (if constant is declared with its initial value).
DECLARE
l_total NUMBER;
BEGIN
l_total := 100;
l_total := l_total + 50;
END;
The assignment operator is essential for setting and updating variable values.
These are conditional control statements used to execute different blocks of code based on logical conditions.
Executes code only if a condition is true.
IF salary > 5000 THEN
bonus := 1000;
END IF;
Runs one branch if condition is true, another if false.
IF salary > 5000 THEN
bonus := 1000;
ELSE
bonus := 500;
END IF;
Used for multiple conditions.
IF salary > 10000 THEN
bonus := 2000;
ELSIF salary > 5000 THEN
bonus := 1000;
ELSE
bonus := 500;
END IF;
A loop in PL/SQL is a control structure that repeats a sequence of statements multiple times until a condition is met.
Each type serves different use cases and provides flexible repetition control.
A FOR loop is a loop that iterates over a predefined range of integers.
Oracle automatically handles loop control, increment, and exit.
FOR counter IN start_value .. end_value LOOP
-- statements
END LOOP;
Example:
FOR i IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
FOR i IN REVERSE 10 .. 1 LOOP
...
END LOOP;
A WHILE loop executes as long as a given condition is true.
The condition is evaluated before each iteration.
WHILE <condition> LOOP
-- statements
END LOOP;
Example:
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;
A simple loop is the most basic form of loop in PL/SQL.
It repeatedly executes its statements until explicitly exited using EXIT or EXIT WHEN.
LOOP
-- statements
EXIT WHEN <condition>;
END LOOP;
Example:
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
END;
A cursor in PL/SQL is a memory area or pointer that Oracle uses to store the result of a SQL query and manage the process of fetching rows one at a time.
Whenever a SQL statement (like SELECT) is executed, Oracle creates a cursor internally to track:
SQL queries can return multiple rows, but PL/SQL can process only one row at a time directly.
A cursor bridges this gap by enabling row-by-row processing.
Cursors play a crucial role in programs that need to process multiple rows sequentially or perform logic per row.
Implicit cursors are automatically created by Oracle whenever a SQL statement is executed within a PL/SQL block, provided that no explicit cursor is defined.
You don't need to declare, open, fetch, or close them — Oracle manages everything.
BEGIN
UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;
Here, Oracle uses an implicit cursor for the UPDATE command.
Implicit cursors are ideal for single-row queries or simple DML operations.
Explicit cursors are manually declared by the programmer for queries that return multiple rows.
They provide fine control over the retrieval process and allow row-by-row iteration and logic application.
CURSOR c1 IS SELECT * FROM employees;
Open the cursorExecutes the query.
OPEN c1;
Fetch dataRetrieves rows one at a time.
FETCH c1 INTO l_emp;
Close the cursorReleases memory and resources.
CLOSE c1;
Explicit cursors enable detailed, controlled data handling in PL/SQL.
The %FOUND attribute is used with both implicit and explicit cursors to determine whether the last SQL operation returned at least one row or successfully processed a row.
%FOUND = TRUE if a row is returned.%FOUND = TRUE if one or more rows were affected.%FOUND = TRUE after fetching a row successfully.UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee updated');
END IF;
Example (explicit cursor):
FETCH c1 INTO l_rec;
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Row fetched');
END IF;
%NOTFOUND is the opposite of %FOUND.
LOOP
FETCH c1 INTO l_rec;
EXIT WHEN c1%NOTFOUND;
-- process row
END LOOP;
Example (DML operation):
DELETE FROM employees WHERE department_id = 999;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No records deleted');
END IF;
%ROWCOUNT returns the number of rows affected or processed by the SQL statement or cursor so far.
UPDATE employees SET salary = salary + 200 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
Example (explicit cursor):
FETCH c1 INTO l_rec;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || ' rows fetched so far');
%ISOPEN checks whether a cursor is open.
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
A procedure is a named PL/SQL block that performs one or more actions but does not return a value directly.
CREATE OR REPLACE PROCEDURE increase_salary (
p_emp_id NUMBER
) AS
BEGIN
UPDATE employees
SET salary = salary + 500
WHERE employee_id = p_emp_id;
END;
Procedures form the backbone of enterprise PL/SQL applications.
A function is similar to a procedure but must return a single value using the RETURN keyword.
CREATE OR REPLACE FUNCTION get_salary (
p_emp_id NUMBER
) RETURN NUMBER AS
l_salary NUMBER;
BEGIN
SELECT salary INTO l_salary
FROM employees
WHERE employee_id = p_emp_id;
RETURN l_salary;
END;
Functions and procedures are similar but serve different purposes.
FeatureProcedureFunctionReturn valueCannot return a value directlyMust return exactly one valueUsage in SQLCannot be used in SQLCan be used in SQL (with restrictions)ParametersIN, OUT, IN OUTMostly IN (OUT not allowed in SQL)PurposePerform actionsPerform computations and return resultsReturn mechanismOUT params or DB changesRETURN statementBehavior in SELECTNot allowedAllowed if function is deterministic and free of side effects
CALL update_salary(101);
Function:
SELECT get_salary(101) FROM dual;
An exception in PL/SQL is an event or error condition that disrupts the normal flow of a program. Exceptions help you handle runtime errors gracefully without abruptly stopping program execution.
When an error occurs in the BEGIN section of a block, control is transferred to the EXCEPTION section, where the error can be handled.
BEGIN
SELECT salary INTO l_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
Exceptions ensure programs handle errors in a controlled and predictable manner.
Predefined exceptions are built-in Oracle exceptions that automatically handle common errors.
Oracle provides names for these exceptions so developers can easily reference them.
ExceptionReasonNO_DATA_FOUNDSELECT…INTO returns no rowsTOO_MANY_ROWSSELECT…INTO returns more than one rowZERO_DIVIDEDivision by zeroINVALID_NUMBERConversion error (e.g., 'ABC' to NUMBER)CURSOR_ALREADY_OPENAttempt to open an open cursorDUP_VAL_ON_INDEXViolation of unique constraint
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
Predefined exceptions significantly reduce boilerplate error handling.
A user-defined exception is an exception that a programmer creates to handle custom business logic or error conditions not covered by Oracle’s predefined exceptions.
RAISEDECLARE
ex_low_salary EXCEPTION;
l_salary NUMBER := 1000;
BEGIN
IF l_salary < 2000 THEN
RAISE ex_low_salary;
END IF;
EXCEPTION
WHEN ex_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary is below minimum threshold');
END;
User-defined exceptions allow precise, domain-specific error management.
The RAISE statement explicitly triggers an exception.
It can raise both predefined and user-defined exceptions.
RAISE exception_name;
Example:
DECLARE
ex_invalid_age EXCEPTION;
BEGIN
IF age < 0 THEN
RAISE ex_invalid_age;
END IF;
EXCEPTION
WHEN ex_invalid_age THEN
DBMS_OUTPUT.PUT_LINE('Invalid age entered');
END;
Useful for logging before passing error outward:
EXCEPTION
WHEN OTHERS THEN
log_error(SQLERRM);
RAISE;
END;
The RAISE statement provides full control over exception handling.
A package in PL/SQL is a group of related procedures, functions, variables, constants, cursors, and other elements stored together as a single unit.
Think of a package as an organizational container for modular program components.
CREATE OR REPLACE PACKAGE employee_pkg AS
PROCEDURE raise_salary(p_id NUMBER);
FUNCTION get_salary(p_id NUMBER) RETURN NUMBER;
END employee_pkg;
Packages help structure enterprise applications cleanly and efficiently.
A PL/SQL package has two main parts:
Example:
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE hire_employee(...);
FUNCTION get_salary(id NUMBER) RETURN NUMBER;
END emp_pkg;
This acts like an interface.
Example:
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE hire_employee(...) IS
BEGIN
...
END;
FUNCTION get_salary(id NUMBER) RETURN NUMBER IS
BEGIN
...
END;
END emp_pkg;
Packages improve code structure and maintainability.
A trigger is a stored PL/SQL block that automatically executes (fires) in response to specific database events.
CREATE OR REPLACE TRIGGER emp_audit_trg
AFTER INSERT ON employees
BEGIN
INSERT INTO audit_table VALUES (...);
END;
Triggers help automate enforcement of business rules and keep data consistent.
Triggers can fire before or after the triggering event occurs.
Example:
BEFORE INSERT ON employees
Example:
AFTER UPDATE ON employees
BEFORE TriggerAFTER TriggerFire before DMLFire after DMLModify :NEW valuesCannot modify :NEW valuesGood for validationGood for auditing
BEFORE and AFTER triggers allow granular control over database actions.
A DML trigger is a trigger that runs when a DML statement (INSERT, UPDATE, DELETE) is executed on a table or view.
FOR EACH ROW)CREATE OR REPLACE TRIGGER emp_bi_trg
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.created_date := SYSDATE;
END;
Triggers extend the database’s capability with rule-based automation.
A sequence in Oracle is a database object that generates a series of unique numbers.
It is commonly used to create primary key values or auto-incrementing IDs.
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE;
Using a sequence:
INSERT INTO employees (id, name)
VALUES (emp_seq.NEXTVAL, 'John');
Sequences ensure reliable, concurrent generation of unique values.
PL/SQL offers numerous benefits that make it a powerful choice for building database-centric enterprise applications on Oracle. Its advantages span performance, reliability, maintainability, and integration capabilities.
PL/SQL is deeply integrated with SQL, allowing:
This allows efficient and seamless interaction with Oracle data.
PL/SQL supports modular blocks:
This promotes clean, maintainable code.
PL/SQL supports:
These drastically reduce SQL–PL/SQL engine context switching.
PL/SQL’s rich exception model improves reliability:
Using packages, procedures, and functions, developers can:
PL/SQL allows:
Packages help hide sensitive details.
PL/SQL code runs the same across:
Features like packages, comments, local subprograms, and modular design make PL/SQL easy to maintain over long-term enterprise use.
PL/SQL provides performance, reliability, modularity, security, and tight SQL integration — making it one of the most robust languages for database application development.
The PL/SQL engine architecture defines how PL/SQL code is compiled, optimized, and executed inside Oracle.
PL/SQL consists of two main components:
PL/SQL block is parsed and compiled into:
Oracle stores the compiled version to reduce future compilation.
During execution:
This separation avoids unnecessary interpretation and improves performance.
Every time PL/SQL hands a statement to the SQL engine, a context switch occurs.
Too many context switches cause performance degradation.
PL/SQL engine executes procedural components, while SQL engine handles data operations. Their interaction defines PL/SQL performance and behavior.
Composite data types allow storing multiple values in a single variable.
Unlike scalar types (NUMBER, VARCHAR2), composite types can hold structured or grouped data elements.
Collections allow storing multiple rows or values in memory.
PL/SQL supports:
Composite data types are essential for representing multiple values and structured data efficiently within PL/SQL.
A RECORD type is a composite data type that groups related fields into a single logical unit, similar to a row in a table or a struct in C.
Defined in the DECLARE section.
TYPE emp_rec IS RECORD (
id NUMBER,
name VARCHAR2(100),
salary NUMBER
);
l_emp emp_rec;
Based on a table’s row structure.
l_emp employees%ROWTYPE;
Based on a cursor return structure.
CURSOR c1 IS SELECT * FROM employees;
l_rec c1%ROWTYPE;
RECORDs help model real-world entities directly inside PL/SQL.
A TABLE type, also called an associative array or index-by table, is a collection that stores data using unique keys (indexes).
It behaves like a dynamic array or dictionary.
TYPE salary_table IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
l_salary salary_table;
Usage:
l_salary(1) := 5000;
l_salary(10) := 6000;
Associative arrays are highly efficient and memory-optimized.
A nested table is a collection type in PL/SQL that supports unbounded numbers of elements. It behaves like a “table inside a table.”
TYPE number_list IS TABLE OF NUMBER;
l_list number_list := number_list(10, 20, 30);
EXTEND – add elementsDELETE – remove elementsCOUNT, FIRST, LAST, etc.Nested tables support powerful object-relational modeling in Oracle.
A VARRAY (Variable-size array) is a collection type with an upper bound.
Unlike nested tables, VARRAYs have a fixed maximum size.
TYPE names_array IS VARRAY(5) OF VARCHAR2(50);
l_names names_array := names_array('Aman', 'Ravi', 'John');
VARRAYs ensure controlled memory usage and preserve element ordering.
BULK COLLECT is a PL/SQL feature used to fetch multiple rows from the SQL engine into PL/SQL collections in a single call.
It reduces context switching, which dramatically improves performance for large datasets.
TYPE emp_list IS TABLE OF employees%ROWTYPE;
l_emps emp_list;
SELECT * BULK COLLECT INTO l_emps FROM employees;
BULK COLLECT is one of the most important performance features in PL/SQL.
FORALL is a PL/SQL statement used to perform bulk DML operations (INSERT, UPDATE, DELETE) using collections.
FORALL i IN 1 .. l_ids.COUNT
DELETE FROM employees WHERE employee_id = l_ids(i);
FORALL is essential for high-performance PL/SQL batch operations.
Both improve performance, but they serve different purposes.
Used to fetch multiple rows from SQL to PL/SQL collections at once.
Used to execute DML statements (INSERT, UPDATE, DELETE) in bulk.
FeatureBULK COLLECTFORALLPurposeFetch dataPerform DMLWorks withSELECTINSERT, UPDATE, DELETEImprovesQuery speedDML speedUses collections?YesYesReduces context switching?YesYes
A cursor FOR loop is a PL/SQL construct that simplifies the process of iterating through the result set of a cursor. It combines the declaration, opening, fetching, and closing of a cursor into a single, automatic operation.
This eliminates the need for manual cursor management.
CURSOR c1 IS
SELECT employee_id, salary FROM employees;
FOR r IN c1 LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_id || ' - ' || r.salary);
END LOOP;
FOR r IN (SELECT employee_id, salary FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_id || ' - ' || r.salary);
END LOOP;
r)Cursor FOR loops are the most convenient way to iterate through multi-row query results in PL/SQL, reducing boilerplate code and preventing cursor management errors.
In PL/SQL, parameters allow data to be passed into and out of procedures. They make procedures flexible and reusable.
There are three types of parameter modes (IN, OUT, and IN OUT), but the syntax for passing them is the same.
CREATE OR REPLACE PROCEDURE update_salary (
p_emp_id NUMBER,
p_amount NUMBER
) AS
BEGIN
UPDATE employees
SET salary = salary + p_amount
WHERE employee_id = p_emp_id;
END;
Calling the procedure (inside PL/SQL):
BEGIN
update_salary(101, 500);
END;
Calling the procedure (from SQL*Plus or other tools):
EXEC update_salary(101, 500);
Named notation:
update_salary(p_emp_id => 101, p_amount => 500);
Positional parameters must appear before named ones.
Procedures can accept input and produce output via parameters, enabling modular and reusable program components.
PL/SQL procedures and functions can accept arguments through different parameter modes. Each mode defines how data flows between the caller and the subprogram.
Example:
p_emp_id IN NUMBER
Example:
p_total OUT NUMBER
Example:
p_balance IN OUT NUMBER
ModePassed InReturned OutModifiable?INYesNoNoOUTNoYesYesIN OUTYesYesYes
These parameter modes help define how data flows between calling programs and PL/SQL subprograms, enabling flexible APIs.
PL/SQL allows parameters to have default values, making them optional when calling the procedure.
CREATE OR REPLACE PROCEDURE add_employee (
p_name VARCHAR2,
p_dept_id NUMBER DEFAULT 10,
p_status VARCHAR2 DEFAULT 'ACTIVE'
) AS
BEGIN
INSERT INTO employees (name, dept_id, status)
VALUES (p_name, p_dept_id, p_status);
END;
add_employee('Aman', 20, 'ACTIVE');
Skipping optional parameters:
add_employee('Rahul');
Using named notation:
add_employee(p_name => 'Neha', p_status => 'INACTIVE');
Default parameters make procedures more convenient and reduce the need for multiple overloaded versions.
Triggers can execute stored procedures just like PL/SQL blocks. This is often done for logging, auditing, or additional validations.
CREATE OR REPLACE PROCEDURE log_update (
p_emp_id NUMBER
) AS
BEGIN
INSERT INTO emp_log (emp_id, log_date)
VALUES (p_emp_id, SYSDATE);
END;
Trigger calling the procedure:
CREATE OR REPLACE TRIGGER emp_update_trg
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
log_update(:NEW.employee_id);
END;
:OLD and :NEWCalling a procedure inside a trigger is common for modularity, reusability, and cleaner trigger design.
Function overloading refers to defining multiple functions (or procedures) with the same name but different:
Oracle determines which version to call based on the parameter match.
CREATE OR REPLACE PACKAGE math_pkg AS
FUNCTION add_num(a NUMBER, b NUMBER) RETURN NUMBER;
FUNCTION add_num(a NUMBER, b NUMBER, c NUMBER) RETURN NUMBER;
END math_pkg;
Overloading enables creating flexible and cleaner PL/SQL APIs for various use cases.
PRAGMA AUTONOMOUS_TRANSACTION marks a PL/SQL block as an independent transaction, separate from the main calling transaction.
This means:
CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log(msg, log_time)
VALUES (p_msg, SYSDATE);
COMMIT;
END;
Autonomous transactions allow parallel transactions inside PL/SQL without affecting the main logic flow.
PRAGMA EXCEPTION_INIT is used to associate a user-defined exception with a specific Oracle error code.
This allows the programmer to handle Oracle errors using meaningful names instead of numeric codes.
DECLARE
ex_dup EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dup, -00001); -- unique constraint violation
BEGIN
...
EXCEPTION
WHEN ex_dup THEN
DBMS_OUTPUT.PUT_LINE('Duplicate record detected');
END;
EXCEPTION_INIT bridges the gap between Oracle error codes and user-friendly exception names.
PRAGMA SERIALLY_REUSABLE instructs Oracle to release package state once a call is completed.
It is used for memory optimization, especially in environments like Oracle Shared Server.
CREATE OR REPLACE PACKAGE session_pkg
PRAGMA SERIALLY_REUSABLE IS
g_counter NUMBER := 0;
END session_pkg;
This pragma helps in reducing memory footprint by releasing package state after each execution cycle.
A mutating table error occurs when a row-level trigger tries to:
ORA-04091: table is mutating, trigger/function may not see it
During a row-level DML trigger, the table is in an unstable state.
Oracle prevents inconsistent reads, so querying the same table raises an error.
CREATE OR REPLACE TRIGGER emp_trg
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
SELECT COUNT(*) INTO v_cnt FROM employees; -- mutating!
END;
Move logic from row-level to statement-level.
Available from 11g onwards.
Store data in package variables, then process after trigger completes.
Mutating table errors occur when row-level triggers interact with the triggering table.
Proper design using compound triggers or other techniques is required to avoid this issue.
A mutating table error (ORA-04091) occurs when a row-level trigger attempts to read or modify the same table that is currently being modified by the triggering statement. This happens because the table is in an interim, unstable state.
To resolve this, Oracle provides several strategies:
Move the logic from a row-level trigger (FOR EACH ROW) to a statement-level trigger.
Statement-level triggers fire once per statement, when the table is in a stable state.
Compound triggers allow code to run before, after, and during row operations, capturing row-level data and processing it later at statement-end.
Example:
CREATE OR REPLACE TRIGGER emp_ct
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
changed_rows emp_tab_type := emp_tab_type();
BEFORE EACH ROW IS
BEGIN
changed_rows.extend;
changed_rows(changed_rows.count) := :NEW;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
-- Safe to query or modify employees here
END AFTER STATEMENT;
END;
Instead of querying the mutating table inside the trigger, store necessary values in package variables during row-level execution, then process them in a statement-level trigger.
Store intermediate data in a temporary table and process it after the row-level triggers complete.
A mutating table error is resolved by avoiding queries or DML operations on the same table in a row-level trigger and using compound triggers, statement-level triggers, or temporary structures instead.
Triggers can fire once per statement or once per row, depending on how they are defined.
FOR EACH ROW
:OLD and :NEW values.Example:
CREATE OR REPLACE TRIGGER emp_row_trg
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.last_updated := SYSDATE;
END;
:OLD or :NEW.Example:
CREATE OR REPLACE TRIGGER emp_stmt_trg
AFTER UPDATE ON employees
BEGIN
INSERT INTO audit_tbl VALUES ('Employees updated');
END;
FeatureRow-Level TriggerStatement-Level TriggerTrigger FrequencyOnce per rowOnce per DML statementAccess to OLD/NEWYesNoPerformanceSlower for large updatesFasterCommon UseValidationsAuditing, loggingMutating Table RiskHighNone
Row-level triggers focus on individual rows; statement-level triggers focus on entire DML operations.
INSTEAD OF triggers are used specifically for views, especially complex views that are not inherently updatable.
They allow DML operations (INSERT, UPDATE, DELETE) on views by defining what should happen instead of the default DML.
Complex views (with joins, aggregations, DISTINCT, GROUP BY) are not directly updatable.
CREATE OR REPLACE TRIGGER emp_dept_io_trg
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
INSERT INTO employees(emp_id, emp_name, dept_id)
VALUES(:NEW.emp_id, :NEW.emp_name, :NEW.dept_id);
END;
INSTEAD OF triggers make non-updatable views behave like fully editable, logical representations of data.
DBMS_OUTPUT is a built-in Oracle package used to:
Prints a line of text.
DBMS_OUTPUT.PUT_LINE('Hello World');
Enables output buffering.
Retrieves buffered output (used in external environments).
DBMS_OUTPUT is a developer-friendly debugging tool, not meant for production auditing.
DBMS_SCHEDULER is Oracle’s advanced job scheduling package, used to automate:
It replaces the older DBMS_JOB with more powerful capabilities.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'daily_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN process_orders; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2',
enabled => TRUE
);
END;
DBMS_SCHEDULER is a powerful automation framework for enterprise-grade database operations.
Dynamic SQL is SQL that is constructed and executed at runtime, rather than hard-coded into PL/SQL.
It is used when:
sql_stmt := 'SELECT * FROM ' || table_name;
EXECUTE IMMEDIATE sql_stmt;
Dynamic SQL makes PL/SQL flexible and adaptable for dynamic operations but must be used carefully to avoid SQL injection.
EXECUTE IMMEDIATE is the simplest way to run dynamic SQL in PL/SQL.
It allows the execution of:
EXECUTE IMMEDIATE 'DELETE FROM employees';
Bind Variables:
EXECUTE IMMEDIATE
'UPDATE employees SET salary = salary + :1 WHERE emp_id = :2'
USING p_amount, p_emp_id;
Fetching results:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees'
INTO l_count;
EXECUTE IMMEDIATE is the foundation of dynamic SQL execution in PL/SQL.
DBMS_SQL is a low-level, highly flexible package used to execute dynamic SQL.
It supports SQL operations that EXECUTE IMMEDIATE cannot handle easily.
cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_id, sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(cursor_id, 1, l_value);
rows := DBMS_SQL.EXECUTE(cursor_id);
DBMS_SQL.FETCH_ROWS(cursor_id);
FeatureEXECUTE IMMEDIATEDBMS_SQLSimplicitySimpleComplexFlexibilityModerateVery highDynamic number of columnsNoYesPerformanceFasterSlower
DBMS_SQL is used for highly dynamic SQL requirements, while EXECUTE IMMEDIATE handles most common cases.
Debugging PL/SQL involves identifying issues, analyzing execution flow, and validating logic.
Print variable values, checkpoints, and messages.
Provides:
Use frameworks like:
Use detailed exception blocks:
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
These built-in tools allow profiling and deep debugging.
To debug SQL performance inside PL/SQL.
PL/SQL debugging uses a blend of logging, tools, tracing, and exception monitoring to diagnose logic and performance issues.
The PL/SQL optimizer is the component of the PL/SQL compiler that improves performance by transforming code during compilation.
PL/SQL supports levels 0, 1, 2, 3 (default: 2).
Higher optimization:
Removes unreachable code branches.
Automatically embeds small subprograms into calling code to reduce overhead.
Compiles PL/SQL into native machine code for faster execution.
Reduces context switching by optimizing SQL inside PL/SQL.
Precomputes constant expressions (e.g., 2+3 is optimized to 5).
PL/SQL optimizer analyzes and rewrites code to run faster, making PL/SQL execution more efficient without changing program behavior.
The NOCOPY hint is a performance optimization technique used with OUT and IN OUT parameters of procedures/functions. By default, Oracle uses pass-by-value semantics, meaning it creates a copy of the parameter before passing it to the subprogram and again when returning it.
This copying is slow for large collections, records, and objects.
PROCEDURE process_data(p_list IN OUT NOCOPY number_table) IS
BEGIN
-- Do some work on large data
END;
If the procedure raises an exception, the calling program may receive partially modified data, because no copy exists.
NOCOPY is a powerful performance hint but must be used carefully to avoid inconsistent data states.
SAVEPOINT and COMMIT are transaction control commands, but they serve different purposes.
Example:
SAVEPOINT sp1;
Example:
COMMIT;
FeatureSAVEPOINTCOMMITEnds transaction?❌ No✔️ YesRolls back?Partial rollbackFull rollback not possible after commitLocks released?❌ No✔️ YesUse caseFine-grained controlFinalize changes
SAVEPOINT allows finer control within a transaction; COMMIT finalizes the whole transaction.
Example:
ROLLBACK;
Example:
ROLLBACK TO sp1;
FeatureROLLBACK TO SAVEPOINTROLLBACKReverts changes?PartialFullEnds transaction?❌ No✔️ YesReleases locks?❌ No✔️ YesUse caseUndo recent mistakeCancel entire transaction
ROLLBACK cancels all changes; ROLLBACK TO SAVEPOINT cancels only part of the changes.
A database link (DBLINK) is an Oracle schema object that enables a user to access objects in a remote database.
It allows SQL operations across databases—even across servers.
CREATE DATABASE LINK remote_db
CONNECT TO hr IDENTIFIED BY hrpwd
USING 'remote_tns';
Using a DB Link:
SELECT * FROM employees@remote_db;
DB links enable seamless interaction between distributed Oracle databases.
An autonomous transaction is an independent transaction that runs inside a parent transaction but commits or rolls back separately.
Used when you need to perform work that must be saved regardless of the outcome of the main transaction.
CREATE OR REPLACE PROCEDURE log_action(p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO action_log VALUES (p_msg, SYSDATE);
COMMIT; -- independent commit
END;
Autonomous transactions provide granular control over critical logging and auditing tasks.
Oracle provides two major date/time data types:
Example:
DATE '2024-11-27'
Example:
TIMESTAMP '2024-11-27 10:35:15.123456'
FeatureDATETIMESTAMPFractional seconds❌ No✔️ YesTime zone support❌ No✔️ Yes (TZ versions)PrecisionSecondsUp to nanosecondsPreferred forBusiness datesHigh-precision logging
Use DATE for simple date-time values; use TIMESTAMP for high-precision applications like auditing and logging.
These are null-handling functions used to manage NULL values gracefully.
Returns replacement if expr is NULL.
NVL(salary, 0)
Returns different values based on whether expr is NULL.
NVL2(comm, 'Has Comm', 'No Comm')
Returns the first non-NULL expression among many.
COALESCE(phone1, phone2, phone3)
FunctionMeaningBest forNVLReplace NULL with valueSimple replacementsNVL2Different actions for NULL and NOT NULLConditional logicCOALESCEFirst non-null from listAdvanced null handling
COALESCE is the most powerful; NVL and NVL2 offer simpler alternatives for null logic.
PL/SQL supports several conditional operators to make decisions inside expressions.
Acts like a simplified IF-ELSE.
DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
More powerful and readable than DECODE.
CASE
WHEN salary > 10000 THEN 'High'
WHEN salary > 5000 THEN 'Medium'
ELSE 'Low'
END
Used for NULL-based conditions (discussed earlier).
Returns NULL if two values are equal.
NULLIF(a, b)
Conditional operators simplify decision-making and data transformations directly in SQL/PLSQL expressions.
Object types (Oracle Objects) allow Object-Oriented Programming (OOP) inside PL/SQL.
CREATE TYPE emp_obj AS OBJECT (
emp_id NUMBER,
name VARCHAR2(100),
MEMBER PROCEDURE display
);
Using the object:
DECLARE
l_emp emp_obj := emp_obj(101, 'Aman');
BEGIN
l_emp.display;
END;
Object types enable robust, scalable, object-oriented data modeling inside Oracle PL/SQL.
A PL/SQL package has two phases in its lifecycle:
When the package is first referenced in a session:
The lifecycle of a package spans compilation, initialization, execution, and memory release—making packages powerful session-level stateful components in PL/SQL.
The PL/SQL execution model outlines how the Oracle database compiles and executes PL/SQL blocks. It involves several steps handled by different components within the Oracle Database Engine.
Oracle compiles the PL/SQL block into:
These representations are stored in the shared pool for reuse.
PL/SQL optimizer:
The optimization level determines how aggressive this phase is.
PL/SQL block is executed by:
The two engines interact, sometimes causing context switching.
The PL/SQL execution model is a sophisticated process involving compilation to DIANA/M-code, optimization, execution by dual engines, and structured exception handling.
A context switch occurs when control transfers between the PL/SQL engine and the SQL engine.
This happens because PL/SQL and SQL execute in different runtime environments.
In mixed code, such as:
FOR i IN 1 .. 1000 LOOP
SELECT salary INTO l_sal FROM employees WHERE emp_id = i;
END LOOP;
Every SQL statement forces:
Context switching is a major performance bottleneck. Reducing context switches is crucial for high-performance PL/SQL programming.
