169 questions — Grouped by Concept · Click to expand · + for code & details
PL/SQL stands for Procedural Language extensions to SQL.
PL/SQL combines the data manipulation power of SQL with the processing power of procedural languages.
Oracle Corporation developed PL/SQL to enhance the capabilities of SQL by adding constructs found in procedural languages.
Developers use PL/SQL for writing complex database applications that require conditional logic, looping, and more sophisticated data manipulation.
It is Oracle's procedural extension to SQL that combines SQL with procedural programming features.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Basic PL/SQL anonymous block
DECLARE
v_emp_name VARCHAR2(100);
v_salary NUMBER;
BEGIN
SELECT employee_name, salary
INTO v_emp_name, v_salary
FROM employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns $' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PL/SQL stands for Procedural Language extensions to SQL.
PL/SQL is a procedural language that allows developers to write complex programs with control structures, such as loops and conditions.
SQL is a declarative language used primarily for querying and managing relational databases.
PL/SQL supports variables, procedures, and functions, which are not available in SQL.
This makes PL/SQL more powerful for building applications that perform multiple operations in a single block of code.
Procedures do not return values.
Functions must return a value and can be used in SQL statements.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- SQL: declarative, single operation
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;
-- PL/SQL: procedural, multiple operations in one block
DECLARE
v_total_raise NUMBER := 0;
BEGIN
FOR emp IN (SELECT employee_id, salary FROM employees WHERE department_id = 10) LOOP
IF emp.salary < 50000 THEN
UPDATE employees SET salary = salary * 1.10 WHERE employee_id = emp.employee_id;
v_total_raise := v_total_raise + (emp.salary * 0.10);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total raise budget: $' || v_total_raise);
COMMIT;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMPLOYEE_NAME | SALARY |
|---|---|
| Alice Johnson | 50,000 |
| Carol White | 55,000 |
2 rows — Bob Smith, Dave Brown excluded by WHERE clause
PL/SQL is a procedural language that allows developers to write complex programs with control structures, such as loops and conditions.
The basic components of a PL/SQL block include the declaration section, the execution section, and the exception handling section.
The declaration section defines variables and constants.
The execution section contains the PL/SQL code that performs specific tasks.
The exception-handling section manages errors during the execution.
Every PL/SQL block starts with the BEGIN keyword and ends with the END keyword.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Complete PL/SQL block showing all 3 sections
DECLARE
-- Section 1: DECLARE (optional) - variables, constants, cursors
v_employee_id NUMBER := 101;
v_employee_name VARCHAR2(100);
v_salary NUMBER;
c_bonus_rate CONSTANT NUMBER := 0.10;
BEGIN
-- Section 2: BEGIN...END (mandatory) - executable statements
SELECT employee_name, salary
INTO v_employee_name, v_salary
FROM employees
WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_employee_name);
DBMS_OUTPUT.PUT_LINE('Salary: $' || v_salary);
DBMS_OUTPUT.PUT_LINE('Bonus: $' || (v_salary * c_bonus_rate));
EXCEPTION
-- Section 3: EXCEPTION (optional) - error handlers
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The basic components of a PL/SQL block include the declaration section, the execution section, and the exception handling section.
A simple PL/SQL program to display "Hello, World" is written using an anonymous block.
This program includes a BEGIN statement, followed by the DBMS_OUTPUT.PUT_LINE procedure to print the "Hello, World" message, and it ends with the END statement.
The DBMS_OUTPUT.ENABLE procedure must be called to view the output in some SQL environments.
-- Enable output (required in SQL*Plus / SQL Developer)
SET SERVEROUTPUT ON;
-- Simplest Hello World
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/
-- With ENABLE and multiple lines
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => 1000000);
DBMS_OUTPUT.PUT_LINE('Hello, World!');
DBMS_OUTPUT.PUT_LINE('Welcome to Oracle PL/SQL!');
DBMS_OUTPUT.PUT_LINE('Today is: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY'));
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
A simple PL/SQL program to display "Hello, World" is written using an anonymous block.
Nested blocks in PL/SQL refer to the ability to define a PL/SQL block inside another PL/SQL block.
Each nested block can have its own declaration, execution, and exception sections.
Nested blocks are used for structuring complex PL/SQL programs, and allow for independent exception handling and variable scope management within each block.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Nested blocks with independent exception handling
DECLARE
v_result NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer BEGIN');
-- Inner block 1
BEGIN
v_result := 100 / 0; -- causes ZERO_DIVIDE
EXCEPTION
WHEN ZERO_DIVIDE THEN
v_result := 0;
DBMS_OUTPUT.PUT_LINE('Inner block caught ZERO_DIVIDE - continuing');
END;
-- Inner block 2: its own declare section
DECLARE
v_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_name
FROM employees WHERE employee_id = 9999; -- not found
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inner block 2: employee not found - handled');
END;
DBMS_OUTPUT.PUT_LINE('Outer block continues. Result: ' || v_result);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Nested blocks in PL/SQL refer to the ability to define a PL/SQL block inside another PL/SQL block.
Limitations of PL/SQL include a dependency on the Oracle Database, which might not be suitable for environments using other database systems.
PL/SQL is less efficient for certain tasks that could be more effectively handled by other programming languages, particularly those involving complex computations or operating system-level access.
PL/SQL have performance limitations for large-scale data processing compared to dedicated ETL tools.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- PL/SQL is Oracle-only (cannot run on MySQL, PostgreSQL etc.)
-- The following code ONLY works in Oracle Database:
DECLARE
v_result NUMBER;
BEGIN
-- Oracle-specific: CONNECT BY hierarchical query
SELECT COUNT(*) INTO v_result
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
DBMS_OUTPUT.PUT_LINE('Org levels: ' || v_result);
-- DBMS_OUTPUT, DBMS_SCHEDULER etc. are Oracle-specific packages
DBMS_OUTPUT.PUT_LINE('This code is Oracle-only');
-- Large-scale data processing is better handled by dedicated ETL tools
-- PL/SQL is not ideal for:
-- 1. Complex computations (Python/Java are faster)
-- 2. OS-level access (limited to UTL_FILE)
-- 3. Offline processing (requires live DB connection)
-- 4. Very large ETL jobs (Oracle Data Integrator or Spark better)
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| LVL | EMP_ID | EMP_NAME | MANAGER_ID |
|---|---|---|---|
| 1 | 105 | CEO | NULL |
| 2 | 101 | Alice Johnson | 105 |
| 2 | 102 | Bob Smith | 105 |
| 3 | 103 | Carol White | 101 |
CONNECT BY traverses hierarchy — LEVEL = depth from root
Limitations of PL/SQL include a dependency on the Oracle Database, which might not be suitable for environments using other database systems.
The PRAGMA keyword in PL/SQL instructs the compiler to process certain instructions during the compilation process.
Developers use PRAGMA EXCEPTION_INIT to associate an exception name with an Oracle error number.
This action enables error handling to be more readable and maintainable.
PRAGMA AUTONOMOUS_TRANSACTION allows PL/SQL blocks to execute independently of the main transaction.
This keyword is essential for writing robust error handling and transaction control logic in PL/SQL.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- PRAGMA EXCEPTION_INIT: name a specific Oracle error code
DECLARE
e_fk_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_fk_violation, -2291); -- ORA-02291 integrity constraint
e_null_not_allowed EXCEPTION;
PRAGMA EXCEPTION_INIT(e_null_not_allowed, -1400); -- ORA-01400 cannot insert NULL
BEGIN
INSERT INTO employees(employee_id, employee_name, department_id)
VALUES (999, 'Test', 9999); -- invalid dept_id -> FK violation
EXCEPTION
WHEN e_fk_violation THEN
DBMS_OUTPUT.PUT_LINE('FK violation: check department_id exists');
WHEN e_null_not_allowed THEN
DBMS_OUTPUT.PUT_LINE('Null not allowed in a NOT NULL column');
END;
/
-- PRAGMA AUTONOMOUS_TRANSACTION
CREATE OR REPLACE PROCEDURE prc_log(p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log(message, ts) VALUES(p_msg, SYSDATE);
COMMIT;
END;
/
-- PRAGMA RESTRICT_REFERENCES (informational - enforces purity)
-- PRAGMA SERIALLY_REUSABLE (package state reset between calls)| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
The PRAGMA keyword in PL/SQL instructs the compiler to process certain instructions during the compilation process.
SQL is primarily a declarative language suited for interactive data processing but lacks procedural features like looping or condition testing.
Each SQL query operates individually, which can be time-intensive, and it does not incorporate error handling procedures.
In contrast, PL/SQL, an extension of SQL, is a procedural language that offers sophisticated features like conditional statements and looping.
It enhances efficiency by executing a block of statements collectively and incorporates customized error handling mechanisms.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
SQL is primarily a declarative language suited for interactive data processing but lacks procedural features like looping or condition testing.
Key characteristics of PL/SQL include its support for multi-application accessibility, portability across various operating systems with Oracle, the ability to write custom error handling routines, and improved transactional performance integrated with the Oracle data dictionary.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Key characteristics of PL/SQL include its support for multi-application accessibility, portability across various operating systems with Oracle, the ability to write custom error handling routines, and improved transactional performance integrated with the Oracle data dictionary.
In PL/SQL, comments can be single-line, initiated with '--', or multi-line, enclosed between '/' and '/'.
These are used to enhance code readability and are not executed as part of the program.
-- Single line comment
/* Multi-line
comment block */
DECLARE
v_x NUMBER := 10; -- inline comment
BEGIN
DBMS_OUTPUT.PUT_LINE(v_x);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
In PL/SQL, comments can be single-line, initiated with '--', or multi-line, enclosed between '/' and '/'.
The basic structure of PL/SQL includes a declaration section for variables and constants, an execution section where the logic is written, and an exception handling section to manage errors.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The basic structure of PL/SQL includes a declaration section for variables and constants, an execution section where the logic is written, and an exception handling section to manage errors.
The compilation process for PL/SQL includes syntax checking, semantic checking, generation of machine code, and optimization.
It transforms the high-level PL/SQL code into a format executable by the Oracle Database.
-- Compile package
ALTER PACKAGE pkg_emp COMPILE;
ALTER PACKAGE pkg_emp COMPILE BODY;
-- Check errors
SELECT name, line, text FROM user_errors
WHERE name = 'PKG_EMP' ORDER BY sequence;
-- SQL*Plus shortcut
SHOW ERRORS PACKAGE pkg_emp;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
The compilation process for PL/SQL includes syntax checking, semantic checking, generation of machine code, and optimization.
The DECLARE block in PL/SQL is pivotal for defining variables and exceptions in anonymous blocks or stand-alone procedures.
It precedes the BEGIN statement in a PL/SQL block, allowing for the declaration of variables, constants, and other data types.
This block is essential for ensuring that all necessary variables are initialized and available for use within the PL/SQL block.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- DECLARE is used in anonymous blocks
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/
-- NOT needed in named objects (procedures/functions already have it)
CREATE OR REPLACE PROCEDURE prc_test IS
v_count NUMBER; -- declaration section, no DECLARE keyword
BEGIN NULL; END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
The DECLARE block in PL/SQL is pivotal for defining variables and exceptions in anonymous blocks or stand-alone procedures.
Anonymous blocks are unnamed PL/SQL code blocks that are executed once and not stored in the database.
They start with DECLARE (optional) or BEGIN and end with END.
Because they are not stored, they cannot be called by name and are recompiled every time they run.
Subprograms (procedures and functions) are named, compiled once, stored in the database (in USER_SOURCE / DBA_SOURCE), can be called repeatedly, can accept parameters, and have security privileges assigned to them.
Anonymous blocks are used for one-off scripts, testing, and migration tasks.
-- Anonymous block: not stored, runs once
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Employee count: ' || v_count);
-- This block is gone after execution
END;
/
-- Subprogram: stored, reusable, callable by name
CREATE OR REPLACE PROCEDURE prc_show_count IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Employee count: ' || v_count);
END;
/
-- Stored in: SELECT * FROM user_source WHERE name='PRC_SHOW_COUNT'
-- Call anytime: EXEC prc_show_count;
-- Grant to others: GRANT EXECUTE ON prc_show_count TO hr_user;| OUTPUT / RESULT |
|---|
| Anonymous block: Employee count: 4 (runs once, not stored) |
| Stored procedure: saved in USER_SOURCE — callable anytime |
| EXEC prc_show_count Employee count: 4 |
| GRANT EXECUTE ON prc_show_count TO hr_user permission granted |
Anonymous blocks are unnamed PL/SQL code blocks that are executed once and not stored in the database.
Variables in PL/SQL are placeholders used to store temporary data that can be manipulated during the execution of a PL/SQL block.
Developers declare variables in the declaration section of a PL/SQL block.
Variables must be assigned a specific data type, and they can be initialized to a value at the time of declaration.
Variable names must begin with a letter and can include letters, digits, and underscores.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
DECLARE
-- Variable declarations with data types
v_emp_name VARCHAR2(100); -- variable-length string
v_salary NUMBER(10, 2) := 0; -- initialised to 0
v_hire_date DATE;
v_is_active BOOLEAN := TRUE;
-- %TYPE anchors variable to a column's type
v_dept_id employees.department_id%TYPE;
BEGIN
SELECT employee_name, salary, hire_date, department_id
INTO v_emp_name, v_salary, v_hire_date, v_dept_id
FROM employees
WHERE employee_id = 101;
IF v_is_active THEN
DBMS_OUTPUT.PUT_LINE('Employee : ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('Salary : $' || v_salary);
DBMS_OUTPUT.PUT_LINE('Hired : ' || TO_CHAR(v_hire_date,'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Dept : ' || v_dept_id);
END IF;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMPLOYEE_NAME | SALARY | DEPT_NAME |
|---|---|---|
| Alice Johnson | 50,000 | Engineering |
| Bob Smith | 62,000 | Marketing |
| Carol White | 55,000 | Engineering |
| Dave Brown | 70,000 | Marketing |
All 4 rows returned
Variables in PL/SQL are placeholders used to store temporary data that can be manipulated during the execution of a PL/SQL block.
Constants in PL/SQL are declared in the declaration section of a PL/SQL block and are initialized to a value that does not change during the block's execution.
Constants are defined using the CONSTANT keyword, followed by a data type and an initial value.
Unlike variables, constants must be initialized when they are declared.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
-- Constants: must be initialized, cannot be changed
c_company CONSTANT VARCHAR2(50) := 'ACME Corp';
c_max_salary CONSTANT NUMBER := 100000;
c_tax_rate CONSTANT NUMBER := 0.20;
c_pi CONSTANT NUMBER := 3.14159265;
v_salary NUMBER := 80000;
v_tax NUMBER;
BEGIN
v_tax := v_salary * c_tax_rate;
DBMS_OUTPUT.PUT_LINE('Company : ' || c_company);
DBMS_OUTPUT.PUT_LINE('Salary : $' || v_salary);
DBMS_OUTPUT.PUT_LINE('Tax (20%): $' || v_tax);
IF v_salary > c_max_salary THEN
DBMS_OUTPUT.PUT_LINE('WARNING: exceeds maximum allowed salary');
END IF;
-- c_max_salary := 200000; -- ERROR: constants cannot be reassigned
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Constants in PL/SQL are declared in the declaration section of a PL/SQL block and are initialized to a value that does not change during the block's execution.
PL/SQL supports various data types, including scalar types such as NUMBER, VARCHAR2, DATE, and BOOLEAN.
Composite data types include RECORDS and COLLECTIONS such as VARRAYS, NESTED TABLES, and ASSOCIATIVE ARRAYS.
PL/SQL also supports LOB data types like BLOB, CLOB, and BFILE for managing large objects.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
-- Scalar data types
v_num NUMBER(10, 2) := 9999.99;
v_int PLS_INTEGER := 42; -- faster integer arithmetic
v_str VARCHAR2(100) := 'Hello';
v_fixed CHAR(5) := 'Y'; -- padded to 5 chars
v_dt DATE := SYSDATE;
v_ts TIMESTAMP := SYSTIMESTAMP;
v_bool BOOLEAN := TRUE;
-- Composite: anchored to table row
v_emp employees%ROWTYPE;
-- Large Object
v_clob CLOB;
BEGIN
SELECT * INTO v_emp FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('NUMBER : ' || v_num);
DBMS_OUTPUT.PUT_LINE('VARCHAR2 : ' || v_str);
DBMS_OUTPUT.PUT_LINE('DATE : ' || TO_CHAR(v_dt, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Employee : ' || v_emp.employee_name);
DBMS_OUTPUT.PUT_LINE('Boolean : ' || CASE WHEN v_bool THEN 'TRUE' ELSE 'FALSE' END);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
PL/SQL supports various data types, including scalar types such as NUMBER, VARCHAR2, DATE, and BOOLEAN.
The scope of variables in PL/SQL determines where the variables can be accessed within a program.
Variables declared in a procedure or function are local to that block and cannot be accessed outside of it.
Variables declared in a package specification are global and can be accessed by any block within the package.
The scope controls the visibility and lifetime of variables.
-- Variable scope demonstration
DECLARE
v_outer VARCHAR2(20) := 'OUTER';
v_num NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer block: ' || v_outer); -- accessible here
-- Nested (inner) block
DECLARE
v_inner VARCHAR2(20) := 'INNER';
v_num NUMBER := 99; -- shadows outer v_num
BEGIN
DBMS_OUTPUT.PUT_LINE('Inner block v_outer: ' || v_outer); -- can see outer
DBMS_OUTPUT.PUT_LINE('Inner block v_inner: ' || v_inner); -- local
DBMS_OUTPUT.PUT_LINE('Inner block v_num : ' || v_num); -- 99 (shadowed)
END;
-- v_inner is NOT accessible here (out of scope)
DBMS_OUTPUT.PUT_LINE('Back in outer v_num: ' || v_num); -- 10 (original)
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
The scope of variables in PL/SQL determines where the variables can be accessed within a program.
The VARCHAR2 and CHAR data types in PL/SQL both store character data.
The key difference lies in their storage behavior.
CHAR is a fixed-length data type, which pads the stored value with spaces to meet the specified length.
The VARCHAR2 is variable-length, storing data as entered up to the maximum defined length.
VARCHAR2 is more space-efficient for storing variable-length strings compared to CHAR.
DECLARE
-- CHAR: fixed-length, pads with spaces to max length
v_char CHAR(10) := 'Hello'; -- stored as 'Hello ' (10 chars)
-- VARCHAR2: variable-length, stores exactly what you put in
v_varchar VARCHAR2(10) := 'Hello'; -- stored as 'Hello' (5 chars)
BEGIN
DBMS_OUTPUT.PUT_LINE('CHAR length: ' || LENGTH(v_char)); -- 10
DBMS_OUTPUT.PUT_LINE('VARCHAR2 length: ' || LENGTH(v_varchar)); -- 5
-- Comparison difference: CHAR pads for comparison
IF v_char = 'Hello' THEN
DBMS_OUTPUT.PUT_LINE('CHAR matches (blank-padded comparison)');
END IF;
IF v_varchar = 'Hello' THEN
DBMS_OUTPUT.PUT_LINE('VARCHAR2 matches exactly');
END IF;
-- Use VARCHAR2 for most data (more space-efficient)
-- Use CHAR only for fixed-length codes: country code, gender flag etc.
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
The VARCHAR2 and CHAR data types in PL/SQL both store character data.
%TYPE is used for declaring a variable of the same data type as a table column, for example, studentId students.student_id%TYPE;. %ROWTYPE, on the other hand, declares a RECORD type variable mirroring a table row's structure, such as Stud_rec students.%ROWTYPE;.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
DECLARE
v_name employees.employee_name%TYPE; -- same type as column
v_emp employees%ROWTYPE; -- same as entire row
BEGIN
SELECT employee_name INTO v_name FROM employees WHERE employee_id = 101;
SELECT * INTO v_emp FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Name : ' || v_name);
DBMS_OUTPUT.PUT_LINE('Dept : ' || v_emp.department_id);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
%TYPE is used for declaring a variable of the same data type as a table column, for example, studentId students.
In PL/SQL, a literal refers to a fixed value that appears directly in a statement.
Examples include numeric literals, string literals, date and time literals, Boolean literals, and character literals.
Literals are used to represent constant values in code.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
In PL/SQL, a literal refers to a fixed value that appears directly in a statement.
In PL/SQL, date functions operate on values of date data types and return a date.
Character functions accept character values and return character or numeric values, encompassing functions like UPPER, LOWER, CONCAT, LENGTH, etc.
Number functions take numeric inputs and return numeric values, and include functions like ROUND, TRUNC, MOD, etc.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
In PL/SQL, date functions operate on values of date data types and return a date.
PL/SQL supports various data types including Scalar (like integers, characters), Reference, Composite (such as records, arrays), and Large Object (LOB) types.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PL/SQL supports various data types including Scalar (like integers, characters), Reference, Composite (such as records, arrays), and Large Object (LOB) types.
The DUAL table is a special one-row, one-column table present by default in all Oracle databases, primarily used for selecting a pseudo column like SYSDATE or USER.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The DUAL table is a special one-row, one-column table present by default in all Oracle databases, primarily used for selecting a pseudo column like SYSDATE or USER.
SYSDATE is a function that returns the current date and time from the system.
USER returns the name of the current user accessing the database.
BEGIN
DBMS_OUTPUT.PUT_LINE('Date : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Time : ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('User : ' || USER);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
SYSDATE is a function that returns the current date and time from the system.
Global variables in PL/SQL are declared in the declarative section of a package and can be accessed by any procedure or function within the package.
They retain their values throughout the session.
-- Global: declared in package spec, visible everywhere
CREATE OR REPLACE PACKAGE pkg_globals AS
g_app_name VARCHAR2(50) := 'APEX Mastery';
g_max_rows NUMBER := 1000;
END;
/
-- Access globally
BEGIN
DBMS_OUTPUT.PUT_LINE(pkg_globals.g_app_name);
END;
/
-- Local: declared inside a block, only visible there
DECLARE v_local NUMBER := 42;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_local); -- OK
END;
/ -- v_local gone here| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Global variables in PL/SQL are declared in the declarative section of a package and can be accessed by any procedure or function within the package.
Local variables are confined to the function they are declared in, offering temporary data storage within that scope.
Global variables, conversely, are accessible throughout the program, facilitating data sharing across different functions and modules, albeit with careful consideration due to their broader scope and impact.
-- Global: declared in package spec, visible everywhere
CREATE OR REPLACE PACKAGE pkg_globals AS
g_app_name VARCHAR2(50) := 'APEX Mastery';
g_max_rows NUMBER := 1000;
END;
/
-- Access globally
BEGIN
DBMS_OUTPUT.PUT_LINE(pkg_globals.g_app_name);
END;
/
-- Local: declared inside a block, only visible there
DECLARE v_local NUMBER := 42;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_local); -- OK
END;
/ -- v_local gone here| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Local variables are confined to the function they are declared in, offering temporary data storage within that scope.
String length can be restricted in PL/SQL using two approaches.
First, declare the variable with a maximum length using VARCHAR2(n) which raises VALUE_ERROR if the assigned string exceeds n characters.
Second, use the SUBSTR() function to explicitly truncate a string to a maximum length before assignment or output.
For database columns, the column definition itself enforces length.
The RTRIM function can also trim trailing spaces before length validation.
DECLARE
-- Method 1: VARCHAR2(n) — raises VALUE_ERROR if exceeded
v_name VARCHAR2(20); -- max 20 chars
v_code CHAR(5); -- fixed 5 chars, padded with spaces
-- Method 2: SUBSTR to safely truncate
v_desc VARCHAR2(200);
v_long VARCHAR2(4000) := 'This is a very long description that exceeds limit';
BEGIN
v_name := 'Alice Johnson'; -- 13 chars — OK
-- v_name := 'A very long name over 20 chars'; -- raises VALUE_ERROR
-- SUBSTR: safely take first 20 chars
v_name := SUBSTR(v_long, 1, 20);
DBMS_OUTPUT.PUT_LINE('Truncated: ' || v_name);
-- LENGTH check before assign
IF LENGTH(v_long) > 200 THEN
v_desc := SUBSTR(v_long, 1, 200);
ELSE
v_desc := v_long;
END IF;
DBMS_OUTPUT.PUT_LINE('Length: ' || LENGTH(v_desc));
END;| OUTPUT / RESULT |
|---|
| Truncated: This is a very lon |
| Length: 50 |
| VARCHAR2(20): VALUE_ERROR if string > 20 chars |
| SUBSTR(str, 1, n): always returns at most n characters |
String length can be restricted in PL/SQL using two approaches.
Conditional statements in PL/SQL control the flow of execution based on conditions.
The IF statement checks for a condition and executes a block of code if the condition is true.
PL/SQL supports simple IF, IF-ELSE, and IF-ELSIF-ELSE statements.
For example, an IF-ELSE statement might check if a number is positive, negative, or zero and print an appropriate message for each case.
DECLARE
v_score NUMBER := 75;
v_grade CHAR(1);
BEGIN
-- IF-ELSIF-ELSE
IF v_score >= 90 THEN v_grade := 'A';
ELSIF v_score >= 80 THEN v_grade := 'B';
ELSIF v_score >= 70 THEN v_grade := 'C';
ELSIF v_score >= 60 THEN v_grade := 'D';
ELSE v_grade := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
-- CASE expression
DBMS_OUTPUT.PUT_LINE(
CASE v_grade
WHEN 'A' THEN 'Distinction'
WHEN 'B' THEN 'Merit'
WHEN 'C' THEN 'Pass'
ELSE 'Fail'
END
);
-- Searched CASE
DBMS_OUTPUT.PUT_LINE(
CASE
WHEN v_score BETWEEN 90 AND 100 THEN 'Excellent'
WHEN v_score BETWEEN 70 AND 89 THEN 'Good'
ELSE 'Needs improvement'
END
);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Conditional statements in PL/SQL control the flow of execution based on conditions.
Loops in PL/SQL are used to execute a block of code repeatedly.
PL/SQL supports three main types of loops: the SIMPLE LOOP, which executes until an EXIT condition is met; the WHILE LOOP, which executes as long as a specified condition remains true; and the FOR LOOP, which iterates over a range of values.
For example, a FOR LOOP can iterate from 1 to 10, printing each number.
DECLARE
v_n NUMBER;
BEGIN
-- 1. Basic LOOP with EXIT WHEN
v_n := 1;
LOOP
DBMS_OUTPUT.PUT_LINE('Basic loop: ' || v_n);
v_n := v_n + 1;
EXIT WHEN v_n > 3;
END LOOP;
-- 2. WHILE loop
v_n := 1;
WHILE v_n <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('While loop: ' || v_n);
v_n := v_n + 1;
END LOOP;
-- 3. FOR loop (implicit iterator, no declaration needed)
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('For loop: ' || i);
END LOOP;
-- 4. Reverse FOR loop
FOR i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Reverse: ' || i);
END LOOP;
END;
/| ITERATION | EMP_ID | EMP_NAME | ACTION |
|---|---|---|---|
| 1 | 101 | Alice Johnson | Updated salary to 55,000 |
| 2 | 103 | Carol White | Updated salary to 60,500 |
| End | — | — | COMMIT executed — 2 rows saved |
Loop processed 2 employees — each updated individually
Loops in PL/SQL are used to execute a block of code repeatedly.
The GOTO statement in PL/SQL provides a way to alter the flow of execution to a labeled statement within a PL/SQL block.
Developers use GOTO to skip over sections of code or to jump out of nested loops.
While GOTO can improve readability in complex control structures by avoiding deeply nested conditions, its use is generally discouraged in favor of structured programming techniques.
DECLARE
v_count NUMBER := 0;
v_total NUMBER := 0;
BEGIN
<<outer_loop>>
LOOP
v_count := v_count + 1;
v_total := v_total + v_count;
-- GOTO jumps to a label
IF v_count = 3 THEN
GOTO skip_print; -- skip the print for count = 3
END IF;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
<<skip_print>> -- label
NULL; -- label must be followed by a statement
EXIT WHEN v_count >= 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
-- NOTE: GOTO is generally discouraged; use IF/LOOP/EXIT instead
END;
/| ITERATION | EMP_ID | EMP_NAME | ACTION |
|---|---|---|---|
| 1 | 101 | Alice Johnson | Updated salary to 55,000 |
| 2 | 103 | Carol White | Updated salary to 60,500 |
| End | — | — | COMMIT executed — 2 rows saved |
Loop processed 2 employees — each updated individually
The GOTO statement in PL/SQL provides a way to alter the flow of execution to a labeled statement within a PL/SQL block.
A PL/SQL script for this would use a loop to decrement from 99, displaying each number divisible by 3.
The loop continues until it reaches 3.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A PL/SQL script for this would use a loop to decrement from 99, displaying each number divisible by 3.
EXIT unconditionally terminates the current loop immediately wherever it appears, typically placed inside an IF statement to make it conditional manually.
EXIT WHEN is a built-in conditional form that exits the loop only when the specified Boolean condition evaluates to TRUE, evaluated at the point where EXIT WHEN appears in the loop body.
EXIT WHEN is generally preferred over a bare IF...THEN EXIT because it states the loop's termination condition more directly and concisely in one line.
Both forms only exit the innermost loop by default; exiting an outer loop from inside a nested loop requires labelling the outer loop and using EXIT label_name.
DECLARE
v_counter NUMBER := 1;
BEGIN
<>
LOOP
FOR i IN 1..10 LOOP
IF i = 5 THEN
EXIT outer_loop; -- exits the OUTER loop, not just the FOR loop
END IF;
DBMS_OUTPUT.PUT_LINE('Inner: ' || i);
END LOOP;
v_counter := v_counter + 1;
EXIT outer_loop WHEN v_counter > 3;
END LOOP outer_loop;
END;
/ | OUTPUT |
|---|
| Inner: 1 |
| Inner: 2 |
| Inner: 3 |
| Inner: 4 |
| (loop exits — i = 5 reached) |
EXIT outer_loop inside the FOR loop terminates the labelled outer LOOP entirely, not just the inner FOR loop, as soon as i reaches 5
EXIT always terminates a loop immediately, while EXIT WHEN terminates it only when its condition is TRUE, and labelled loops let you EXIT an outer loop from inside a nested one.
The CASE statement compares a single expression against several possible values and executes the matching branch, while IF-ELSIF evaluates a separate Boolean condition at each branch.
PL/SQL supports a simple CASE, which compares one expression for equality against literal values, and a searched CASE, which evaluates an independent Boolean condition per WHEN clause similar to IF-ELSIF.
Unlike IF-ELSIF, a CASE statement without an ELSE clause raises the predefined exception CASE_NOT_FOUND if no WHEN clause matches, whereas an IF-ELSIF without ELSE simply does nothing.
CASE can also be used as an expression (returning a value directly in an assignment or SELECT), not just as a control statement, which IF cannot do.
| emp_id | emp_name | salary |
|---|---|---|
| 101 | Alice Johnson | 50000 |
| 102 | Bob Smith | 62000 |
DECLARE
v_salary NUMBER := 50000;
v_grade VARCHAR2(20);
BEGIN
-- Searched CASE statement
CASE
WHEN v_salary >= 60000 THEN v_grade := 'Senior';
WHEN v_salary >= 40000 THEN v_grade := 'Mid-level';
ELSE v_grade := 'Junior';
END CASE;
DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
-- CASE as an expression
v_grade := CASE WHEN v_salary >= 60000 THEN 'Senior' ELSE 'Junior' END;
DBMS_OUTPUT.PUT_LINE('Grade (expr): ' || v_grade);
END;
/| OUTPUT |
|---|
| Grade: Mid-level |
| Grade (expr): Junior |
The searched CASE statement matches the 40000+ branch; the CASE expression form only has two branches, so 50000 falls to the ELSE 'Junior'
CASE compares one expression against multiple values (or conditions, in its searched form) and raises CASE_NOT_FOUND if nothing matches and there's no ELSE, while IF-ELSIF silently does nothing without an ELSE.
Cursors in PL/SQL are pointers to the context area where SQL statements are processed.
Cursors allow row-by-row processing of the results of a query.
PL/SQL supports implicit cursors for single-row queries and explicit cursors for multi-row queries.
Developers declare, open, fetch data from, and close explicit cursors to manage data retrieval in a controlled manner.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Explicit cursor: DECLARE OPEN FETCH CLOSE
DECLARE
CURSOR c_emp IS
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;
v_id employees.employee_id%TYPE;
v_name employees.employee_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_name, v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' | $' || v_salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total rows: ' || c_emp%ROWCOUNT);
CLOSE c_emp;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 103 | Carol White | 55,000 |
| 101 | Alice Johnson | 50,000 |
2 rows — Bob Smith, Dave Brown excluded by WHERE clause — ordered by salary DESC
Cursors in PL/SQL are pointers to the context area where SQL statements are processed.
Implicit cursors are automatically created by PL/SQL for DML statements (SELECT, INSERT, UPDATE, DELETE) that process only one row.
Explicit cursors are defined by developers for queries that return multiple rows.
Explicit cursors give developers more control over the query execution and data fetching process, including the ability to fetch rows one at a time.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- IMPLICIT cursor (PL/SQL manages automatically)
DECLARE
v_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_name -- implicit cursor
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Implicit: ' || v_name);
UPDATE employees SET salary = salary * 1.05
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT); -- implicit cursor attr
END;
/
-- EXPLICIT cursor (developer controls every step)
DECLARE
CURSOR c_dept IS
SELECT department_id, COUNT(*) AS cnt
FROM employees GROUP BY department_id;
r c_dept%ROWTYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Dept ' || r.department_id || ': ' || r.cnt || ' employees');
END LOOP;
CLOSE c_dept;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| DEPT_ID | EMP_COUNT | AVG_SALARY | MAX_SALARY |
|---|---|---|---|
| 10 | 2 | 52,500 | 55,000 |
| 20 | 2 | 66,000 | 70,000 |
2 groups — Alice+Carol in dept 10, Bob+Dave in dept 20
Implicit cursors are automatically created by PL/SQL for DML statements (SELECT, INSERT, UPDATE, DELETE) that process only one row.
PL/SQL features two primary cursor types: Implicit cursors, automatically used for DML commands, and explicit cursors, manually defined by programmers for multi-row queries.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- 1. Implicit cursor (auto, single-row)
SELECT employee_name INTO v_name FROM employees WHERE employee_id=101;
-- 2. Explicit cursor
DECLARE
CURSOR c_emp IS SELECT employee_name FROM employees;
BEGIN
FOR r IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_name);
END LOOP;
END;
/
-- 3. REF CURSOR (dynamic)
DECLARE v_cur SYS_REFCURSOR;
BEGIN OPEN v_cur FOR SELECT * FROM employees; CLOSE v_cur; END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
PL/SQL features two primary cursor types: Implicit cursors, automatically used for DML commands, and explicit cursors, manually defined by programmers for multi-row queries.
The outcomes of DML statements are stored in cursor attributes like SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN, which provide various status and count information post-execution.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The outcomes of DML statements are stored in cursor attributes like SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN, which provide various status and count information post-execution.
A cursor program in PL/SQL can be written to fetch and display each employee's name and salary.
The %NOTFOUND attribute is used to terminate the loop when no more records are found.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A cursor program in PL/SQL can be written to fetch and display each employee's name and salary.
A Ref Cursor is a cursor variable that allows a cursor to be opened on the fly with a dynamic query.
There are two types: Strong Ref Cursors, which have a predefined return type, and Weak Ref Cursors, which do not have a predefined return structure.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Weak REF CURSOR (SYS_REFCURSOR)
DECLARE
v_cur SYS_REFCURSOR;
v_name VARCHAR2(100);
BEGIN
OPEN v_cur FOR SELECT employee_name FROM employees WHERE department_id = 10;
LOOP
FETCH v_cur INTO v_name;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE v_cur;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 103 | Carol White | 10 | 55,000 |
2 rows — Bob Smith, Dave Brown excluded by WHERE clause
A Ref Cursor is a cursor variable that allows a cursor to be opened on the fly with a dynamic query.
PL/SQL has three cursor types.
Implicit cursors are automatically created by Oracle for every DML (INSERT/UPDATE/DELETE) and SELECT INTO statement — attributes like SQL%ROWCOUNT and SQL%FOUND give information about the last operation.
Explicit cursors are declared by the developer using CURSOR keyword for multi-row SELECT queries — the developer controls OPEN, FETCH, and CLOSE.
REF CURSORs (cursor variables) are pointers to a query result set that can be opened for different queries at runtime and passed between subprograms.
-- Implicit cursor (auto-created)
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;
-- Explicit cursor
DECLARE
CURSOR c_emp IS
SELECT employee_id, employee_name, salary
FROM employees WHERE department_id = 10;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.employee_name || ': ' || v_emp.salary);
END LOOP;
CLOSE c_emp;
END;
-- REF CURSOR (dynamic)
DECLARE
TYPE t_ref IS REF CURSOR;
v_cur t_ref;
v_name VARCHAR2(100);
BEGIN
OPEN v_cur FOR SELECT employee_name FROM employees;
FETCH v_cur INTO v_name;
CLOSE v_cur;
END;| OUTPUT / RESULT |
|---|
| Implicit: 2 rows updated (SQL%ROWCOUNT = 2) |
| Explicit cursor fetches: Alice Johnson: 50000 |
| Carol White: 55000 |
| REF CURSOR: opened for dynamic query, can be reused |
PL/SQL has three cursor types.
Use the cursor attribute %ISOPEN to check if a cursor is currently open.
This returns TRUE if the cursor is open, FALSE otherwise.
It is good practice to check %ISOPEN before OPEN (to avoid CURSOR_ALREADY_OPEN error) and before CLOSE (to avoid invalid cursor error).
For implicit cursors, SQL%ISOPEN always returns FALSE because Oracle automatically opens and closes them.
DECLARE
CURSOR c_emp IS
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
BEGIN
-- Check before opening
IF NOT c_emp%ISOPEN THEN
OPEN c_emp;
DBMS_OUTPUT.PUT_LINE('Cursor opened: ' || c_emp%ISOPEN);
END IF;
-- Fetch all rows
FOR r IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_name);
END LOOP;
-- Check before closing
IF c_emp%ISOPEN THEN
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Cursor closed');
END IF;
-- SQL% — implicit cursor (always FALSE)
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN: ' || CASE WHEN SQL%ISOPEN THEN 'TRUE' ELSE 'FALSE' END);
END;| OUTPUT / RESULT |
|---|
| Cursor opened: TRUE |
| Alice Johnson |
| Carol White |
| Cursor closed |
| SQL%ISOPEN: FALSE (implicit always closed after statement) |
Use the cursor attribute %ISOPEN to check if a cursor is currently open.
There are three main ways to return multiple rows from PL/SQL.
BULK COLLECT fetches all matching rows into a PL/SQL collection at once — very efficient.
A REF CURSOR (SYS_REFCURSOR or user-defined) returns a cursor pointer that the caller can fetch from — commonly used with Java, .NET, and APEX applications.
A Pipelined table function uses PIPE ROW to return rows one at a time as they are computed — best for large data sets and streaming results in SQL queries.
-- Method 1: BULK COLLECT into collection
DECLARE
TYPE t_emps IS TABLE OF employees%ROWTYPE;
v_emps t_emps;
BEGIN
SELECT * BULK COLLECT INTO v_emps
FROM employees WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_emps.COUNT || ' rows');
FOR i IN 1..v_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_emps(i).employee_name);
END LOOP;
END;
-- Method 2: SYS_REFCURSOR (OUT parameter)
CREATE OR REPLACE PROCEDURE prc_get_dept_emps(
p_dept_id IN NUMBER,
p_cursor OUT SYS_REFCURSOR
) IS
BEGIN
OPEN p_cursor FOR
SELECT employee_id, employee_name, salary
FROM employees WHERE department_id = p_dept_id;
END;
-- APEX: use as report source, Java: ResultSet = p_cursor| OUTPUT / RESULT |
|---|
| BULK COLLECT: Fetched 2 rows — Alice Johnson, Carol White |
| SYS_REFCURSOR: caller iterates cursor (Java ResultSet / APEX) |
| BULK COLLECT: all rows in memory — use LIMIT for large sets |
| PIPE ROW: streams rows — memory efficient for millions of rows |
There are three main ways to return multiple rows from PL/SQL.
The active set is the set of rows that satisfy the WHERE condition of a cursor's SELECT statement at the time the cursor is opened.
When you OPEN a cursor, Oracle executes the query and identifies all the qualifying rows — this complete set of rows is called the active set.
The cursor then maintains a pointer that moves through the active set as you FETCH rows one by one.
The active set is determined at OPEN time, so changes made to the underlying table after OPEN do not affect what the cursor returns (due to Oracle's read consistency via MVCC).
DECLARE
-- This query defines the potential active set
CURSOR c_high_earners IS
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > 55000 -- only qualifying rows form active set
ORDER BY salary DESC;
v_emp c_high_earners%ROWTYPE;
BEGIN
OPEN c_high_earners; -- Active set LOCKED here
-- Active set: Bob Smith(62,000), Dave Brown(70,000)
-- Even if another session inserts a new high earner NOW,
-- this cursor will NOT see it (read consistency)
LOOP
FETCH c_high_earners INTO v_emp; -- moves pointer through active set
EXIT WHEN c_high_earners%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
'Rank ' || c_high_earners%ROWCOUNT || ': ' ||
v_emp.employee_name || ' - ' || v_emp.salary
);
END LOOP;
CLOSE c_high_earners;
END;| OUTPUT / RESULT |
|---|
| Rank 1: Dave Brown - 70,000 |
| Rank 2: Bob Smith - 62,000 |
| Active set: 2 rows (salary > 55,000 when cursor opened) |
| Alice Johnson(50,000) and Carol White(55,000) excluded by WHERE |
The active set is the set of rows that satisfy the WHERE condition of a cursor's SELECT statement at the time the cursor is opened.
Exceptions in PL/SQL are runtime errors or warnings that disrupt the normal execution of a PL/SQL block.
Developers handle exceptions in the EXCEPTION section of a PL/SQL block.
PL/SQL provides predefined exceptions and allows for user-defined exceptions.
Handlers specify actions to be taken when exceptions occur, using the EXCEPTION WHEN syntax followed by the specific action.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_salary NUMBER;
e_neg_salary EXCEPTION; -- user-defined exception
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 999; -- does not exist
IF v_salary < 0 THEN
RAISE e_neg_salary;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found (NO_DATA_FOUND)');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Query returned multiple rows');
WHEN e_neg_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary cannot be negative!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Exceptions in PL/SQL are runtime errors or warnings that disrupt the normal execution of a PL/SQL block.
Error handling in PL/SQL is implemented using the EXCEPTION block within a PL/SQL block.
Developers define exception handlers for specific exceptions using the EXCEPTION WHEN syntax.
Predefined exceptions cover common error conditions, and developers can also define and raise custom exceptions.
Error handling ensures that PL/SQL programs behave predictably and maintain integrity in the face of errors.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_salary NUMBER;
e_low_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(e_low_salary, -20001);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
IF v_salary < 30000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary below minimum threshold');
END IF;
DBMS_OUTPUT.PUT_LINE('Salary OK: $' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
INSERT INTO error_log(msg, logged_at) VALUES('EMP NOT FOUND', SYSDATE);
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Low salary warning: ' || SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Msg : ' || SQLERRM);
ROLLBACK;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Error handling in PL/SQL is implemented using the EXCEPTION block within a PL/SQL block.
Exception handling in user-defined functions in PL/SQL involves the use of the EXCEPTION block.
Developers define exceptions using the WHEN keyword followed by the specific exception or a predefined exception name, then implement corrective actions or error logging within this block.
Exception handling ensures robust functions that can gracefully manage errors and anomalies during execution.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
CREATE OR REPLACE FUNCTION fnc_get_dept_budget(p_dept_id NUMBER)
RETURN NUMBER IS
v_budget NUMBER;
v_dept VARCHAR2(50);
BEGIN
SELECT SUM(salary), department_name
INTO v_budget, v_dept
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = p_dept_id
GROUP BY d.department_name;
IF v_budget IS NULL THEN
RAISE_APPLICATION_ERROR(-20002, 'No employees in department ' || p_dept_id);
END IF;
RETURN v_budget;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department ' || p_dept_id || ' not found');
RETURN 0;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in fnc_get_dept_budget: ' || SQLERRM);
RETURN -1; -- indicate error to caller
END fnc_get_dept_budget;
/
-- Call the function
BEGIN
DBMS_OUTPUT.PUT_LINE('Budget: $' || fnc_get_dept_budget(10));
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Exception handling in user-defined functions in PL/SQL involves the use of the EXCEPTION block.
To implement error logging in PL/SQL procedures, developers use the DBMS_UTILITY package or a custom-built error logging utility.
This utility captures exceptions and logs detailed error information to a persistent store.
Key information such as error code, message, and stack trace are stored for analysis.
Error logging facilities are essential for identifying and troubleshooting runtime issues.
This mechanism ensures a robust debugging process in PL/SQL development.
-- Error log table
CREATE TABLE error_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
module_name VARCHAR2(100),
error_code NUMBER,
error_msg VARCHAR2(4000),
call_stack VARCHAR2(4000),
logged_at DATE DEFAULT SYSDATE,
session_user VARCHAR2(50) DEFAULT USER
);
-- Centralised logging procedure (autonomous so it always commits)
CREATE OR REPLACE PROCEDURE prc_log_error(
p_module VARCHAR2,
p_code NUMBER DEFAULT SQLCODE,
p_msg VARCHAR2 DEFAULT SQLERRM
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log(module_name, error_code, error_msg, call_stack)
VALUES (p_module, p_code, p_msg, DBMS_UTILITY.FORMAT_CALL_STACK);
COMMIT;
END;
/
-- Usage inside any procedure
CREATE OR REPLACE PROCEDURE prc_process_payment(p_order_id NUMBER) IS
v_amount NUMBER;
BEGIN
SELECT total INTO v_amount FROM orders WHERE order_id = p_order_id;
-- ... payment logic ...
EXCEPTION
WHEN OTHERS THEN
prc_log_error('prc_process_payment');
RAISE; -- re-raise to caller
END;
/| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
To implement error logging in PL/SQL procedures, developers use the DBMS_UTILITY package or a custom-built error logging utility.
Advanced exception handling in PL/SQL involves defining custom exception handlers and leveraging the EXCEPTION_INIT pragma to associate exceptions with specific Oracle error codes.
This approach enables granular control over error handling and response.
Developers use nested and labeled blocks to isolate and manage errors effectively.
Advanced techniques ensure robust application behavior under error conditions.
These methods enhance the reliability and maintainability of PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
CREATE OR REPLACE PROCEDURE prc_advanced_error_demo IS
e_fk_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_fk_violation, -2291);
e_dup_key EXCEPTION;
PRAGMA EXCEPTION_INIT(e_dup_key, -1);
BEGIN
-- Nested blocks with independent exception handling
BEGIN
INSERT INTO employees(employee_id, employee_name, department_id)
VALUES (999, 'Test', 9999); -- invalid dept -> FK violation
EXCEPTION
WHEN e_fk_violation THEN
DBMS_OUTPUT.PUT_LINE('FK error caught in inner block - using default dept');
INSERT INTO employees(employee_id, employee_name, department_id)
VALUES (999, 'Test', 10); -- retry with valid dept
END;
-- RAISE_APPLICATION_ERROR for custom messages
DECLARE
v_salary NUMBER := -500;
BEGIN
IF v_salary < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Salary cannot be negative: ' || v_salary);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Custom error: ' || SQLERRM);
prc_log_error('prc_advanced_error_demo'); -- centralised logging
RAISE; -- re-raise to propagate up
END;
EXCEPTION
WHEN e_dup_key THEN
DBMS_OUTPUT.PUT_LINE('Duplicate key at outer level');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unhandled: ' || SQLCODE || ' ' || SQLERRM);
ROLLBACK;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
Advanced exception handling in PL/SQL involves defining custom exception handlers and leveraging the EXCEPTION_INIT pragma to associate exceptions with specific Oracle error codes.
Exception handling in PL/SQL involves managing unexpected events or errors that interrupt normal flow of the program.
PL/SQL allows the creation of custom error-handling routines to handle these exceptions.
Exceptions can be predefined, user-defined, or undefined, each addressing different types of error scenarios.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Exception handling in PL/SQL involves managing unexpected events or errors that interrupt normal flow of the program.
An error in PL/SQL is an issue in the code that causes it to execute incorrectly, while an exception is a runtime event that disrupts the normal flow of the program.
Exceptions are conditions that can be handled within the PL/SQL code through exception handling mechanisms.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Exception: expected error handled by PL/SQL
BEGIN
SELECT salary INTO v_sal FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- handled gracefully
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/
-- Error: unexpected, unhandled problem
-- e.g. ORA-00942: table or view does not exist
-- These bubble up and terminate execution| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
An error in PL/SQL is an issue in the code that causes it to execute incorrectly, while an exception is a runtime event that disrupts the normal flow of the program.
A syntax error occurs when there is a mistake in the code structure, making it uninterpretable by the compiler.
A runtime error occurs during the execution of a program, often due to exceptional conditions that the program doesn't handle.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A syntax error occurs when there is a mistake in the code structure, making it uninterpretable by the compiler.
RAISE_APPLICATION_ERROR is a built-in procedure in PL/SQL that allows a programmer to issue a user-defined error message from a stored procedure or trigger, enabling better control over the error messaging system of applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
CREATE OR REPLACE PROCEDURE prc_check_salary(p_sal NUMBER) IS
BEGIN
IF p_sal < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
DBMS_OUTPUT.PUT_LINE('Salary OK: ' || p_sal);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
RAISE_APPLICATION_ERROR is a built-in procedure in PL/SQL that allows a programmer to issue a user-defined error message from a stored procedure or trigger, enabling better control over the error messaging system of applications.
SQLCODE and SQLERRM are built-in functions available inside PL/SQL exception handlers.
SQLCODE returns the error number (negative for Oracle errors, e.g., -1403 for NO_DATA_FOUND).
SQLERRM returns the error message text associated with the current SQLCODE.
They are used together to log or display meaningful error information without hardcoding error numbers.
DECLARE
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 9999; -- does not exist
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Code : ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
-- Log to audit table
INSERT INTO error_log(error_code, error_msg, log_date)
VALUES(SQLCODE, SQLERRM, SYSDATE);
COMMIT;
END;
/| OUTPUT / RESULT |
|---|
| Error Code : -1403 |
| Error Message: ORA-01403: no data found |
| 1 row inserted into error_log |
| WHEN OTHERS catches any unhandled exception |
SQLCODE and SQLERRM are built-in functions available inside PL/SQL exception handlers.
RAISE_APPLICATION_ERROR is a built-in procedure used to raise user-defined error messages from PL/SQL code.
It allows developers to create meaningful custom error messages that propagate back to the calling application just like Oracle built-in errors.
Error codes must be in the range -20000 to -20999 (reserved for user-defined errors).
The second parameter is the error message string (max 512 chars).
An optional third parameter (TRUE) appends the message to the existing error stack.
CREATE OR REPLACE PROCEDURE prc_update_salary(
p_emp_id IN NUMBER,
p_salary IN NUMBER
) IS
v_dept_id employees.department_id%TYPE;
BEGIN
-- Validate salary
IF p_salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative: ' || p_salary);
END IF;
IF p_salary > 500000 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum allowed: 500,000');
END IF;
UPDATE employees
SET salary = p_salary
WHERE employee_id = p_emp_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20003, 'Employee ID not found: ' || p_emp_id);
END IF;
COMMIT;
END;
/
-- Call:
BEGIN prc_update_salary(101, -5000); END;| OUTPUT / RESULT |
|---|
| ORA-20001: Salary cannot be negative: -5000 |
| ORA-06512: at "HR.PRC_UPDATE_SALARY", line 7 |
| Error range: -20000 to -20999 (user-defined only) |
| Propagates to calling application like built-in Oracle errors |
RAISE_APPLICATION_ERROR is a built-in procedure used to raise user-defined error messages from PL/SQL code.
Oracle PL/SQL provides predefined exceptions for common error conditions that are automatically raised by the runtime engine.
Key ones include: NO_DATA_FOUND (SELECT INTO returns 0 rows), TOO_MANY_ROWS (SELECT INTO returns more than 1 row), DUP_VAL_ON_INDEX (unique constraint violated on INSERT/UPDATE), ZERO_DIVIDE (division by zero), INVALID_NUMBER (string cannot be converted to number), VALUE_ERROR (arithmetic or conversion error), LOGIN_DENIED (invalid username/password), NOT_LOGGED_ON (not connected), CURSOR_ALREADY_OPEN (reopening an open cursor).
DECLARE
v_salary NUMBER;
v_text VARCHAR2(10);
BEGIN
-- NO_DATA_FOUND
SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee with ID 9999');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows returned for SELECT INTO');
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Duplicate value violates unique constraint');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero error');
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Invalid number conversion');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Arithmetic or size error');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unhandled: ' || SQLCODE || ' - ' || SQLERRM);
END;| OUTPUT / RESULT |
|---|
| No employee with ID 9999 |
| Exception handled — execution continues after block |
| WHEN OTHERS: always put last — catches anything unhandled |
| Use RAISE to re-raise after logging |
Oracle PL/SQL provides predefined exceptions for common error conditions that are automatically raised by the runtime engine.
Syntax errors (compile-time errors) occur when the PL/SQL code has incorrect grammar — wrong keywords, missing semicolons, undeclared variables.
These are detected at compile time before any code runs and are shown in DBA_ERRORS.
Runtime errors (exceptions) occur during execution when the code logic encounters a problem — dividing by zero, selecting no rows, null pointer errors.
Runtime errors can be handled using EXCEPTION blocks.
Syntax errors must be fixed in the source code before the block can compile.
-- SYNTAX ERROR (compile-time) — won't even compile:
-- BEGIN
-- SELCT * FROM employees; typo in SELECT
-- x := 10 missing semicolon
-- END;
-- Error: PLS-00103: Encountered symbol "*"
-- RUNTIME ERROR (exception) — compiles but fails at runtime:
DECLARE
v_result NUMBER;
BEGIN
v_result := 100 / 0; -- compiles OK, fails at runtime
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Runtime error: division by zero');
DBMS_OUTPUT.PUT_LINE('Handled gracefully — execution continues');
END;
-- Check compile errors:
SELECT line, position, text
FROM user_errors
WHERE name = 'MY_PROCEDURE'
ORDER BY sequence;| OUTPUT / RESULT |
|---|
| Syntax error: PLS-00103 at compile time — block cannot run |
| Runtime error: ORA-01476 ZERO_DIVIDE — caught by EXCEPTION |
| v_result: not assigned (exception fired before assignment) |
| Handled gracefully — execution continues after EXCEPTION block |
Syntax errors (compile-time errors) occur when the PL/SQL code has incorrect grammar — wrong keywords, missing semicolons, undeclared variables.
A procedure in PL/SQL is created using the CREATE PROCEDURE statement, followed by the procedure name, parameters (if any), and the IS or AS keyword.
The procedure body starts with the BEGIN keyword and ends with the END keyword.
Procedures can perform tasks but do not return a value directly to the caller.
Parameters can be IN, OUT, or IN OUT type.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Create a stored procedure
CREATE OR REPLACE PROCEDURE prc_update_salary(
p_emp_id IN NUMBER,
p_new_sal IN NUMBER,
p_status OUT VARCHAR2
) IS
v_old_sal NUMBER;
BEGIN
SELECT salary INTO v_old_sal
FROM employees WHERE employee_id = p_emp_id;
UPDATE employees SET salary = p_new_sal
WHERE employee_id = p_emp_id;
p_status := 'Updated from $' || v_old_sal || ' to $' || p_new_sal;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_status := 'ERROR: Employee ' || p_emp_id || ' not found';
WHEN OTHERS THEN
ROLLBACK;
p_status := 'ERROR: ' || SQLERRM;
END prc_update_salary;
/
-- Call the procedure
DECLARE
v_result VARCHAR2(200);
BEGIN
prc_update_salary(101, 75000, v_result);
DBMS_OUTPUT.PUT_LINE(v_result);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A procedure in PL/SQL is created using the CREATE PROCEDURE statement, followed by the procedure name, parameters (if any), and the IS or AS keyword.
A function in PL/SQL is similar to a procedure but is designed to return a single value to the caller.
Functions are created using the CREATE FUNCTION statement and must contain a RETURN statement specifying the data type of the returned value.
Unlike procedures, functions can be used in SQL expressions wherever an expression of their return type is allowed.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- FUNCTION: must return a value, usable in SQL
CREATE OR REPLACE FUNCTION fnc_annual_salary(p_emp_id NUMBER)
RETURN NUMBER IS
v_monthly NUMBER;
BEGIN
SELECT salary INTO v_monthly
FROM employees WHERE employee_id = p_emp_id;
RETURN v_monthly * 12;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
END fnc_annual_salary;
/
-- Function can be used directly in SQL
SELECT employee_name,
salary,
fnc_annual_salary(employee_id) AS annual_salary
FROM employees WHERE department_id = 10;
-- PROCEDURE: cannot be used in SQL expressions
CREATE OR REPLACE PROCEDURE prc_print_salary(p_emp_id NUMBER) IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary: $' || v_salary);
-- No RETURN value — uses OUT params instead
END prc_print_salary;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A function in PL/SQL is similar to a procedure but is designed to return a single value to the caller.
An example of a simple procedure could be: CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10) AS BEGIN SELECT * FROM customers WHERE age = @age AND city = @city; END;.
This procedure selects records from the 'customers' table based on age and city parameters.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
An example of a simple procedure could be: CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10) AS BEGIN SELECT * FROM customers WHERE age = @age AND city = @city; END;.
Functions in PL/SQL are designed to return a single value and must specify a return type.
Procedures, however, do not return a value and are used for performing actions.
Packages are collections of functions, procedures, and other elements, providing modular structure to the PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Functions in PL/SQL are designed to return a single value and must specify a return type.
A stored procedure is a pre-written SQL code that can be saved and reused.
It can perform complex operations and can be called by triggers, other procedures, or applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A stored procedure is a pre-written SQL code that can be saved and reused.
Overloading in PL/SQL refers to creating multiple procedures or functions with the same name but different parameter lists.
It allows the same operation to be performed in different ways, depending on the types or number of arguments.
CREATE OR REPLACE PACKAGE pkg_calc AS
FUNCTION get_tax(p_sal NUMBER) RETURN NUMBER;
FUNCTION get_tax(p_sal NUMBER, p_rate NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_calc AS
FUNCTION get_tax(p_sal NUMBER) RETURN NUMBER IS
BEGIN RETURN p_sal * 0.20; END;
FUNCTION get_tax(p_sal NUMBER, p_rate NUMBER) RETURN NUMBER IS
BEGIN RETURN p_sal * p_rate; END;
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Overloading in PL/SQL refers to creating multiple procedures or functions with the same name but different parameter lists.
Stored Procedures offer advantages like modular programming, faster execution, reduced network traffic, and enhanced security.
However, they also present challenges such as limited execution environments, increased memory utilization, maintenance complexity, and potential vendor lock-in.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Stored Procedures offer advantages like modular programming, faster execution, reduced network traffic, and enhanced security.
A Stored Procedure is a pre-compiled collection of SQL statements in a database.
It offers efficiency, performance optimization, security, code reusability, and maintainability.
Stored Procedures streamline database operations, ensuring effective data management and access control.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A Stored Procedure is a pre-compiled collection of SQL statements in a database.
Yes — unlike a procedure, a function can be called directly inside a SQL SELECT, WHERE, or ORDER BY clause, as long as it returns a single value of a SQL-compatible data type.
To be callable from SQL, the function must not contain any DML statements that would violate the purity rules Oracle enforces for SQL context, such as reading or writing package state in ways that could cause inconsistent results within the same SQL statement.
Functions called from a SQL statement also cannot include transaction control statements like COMMIT or ROLLBACK, since this would interfere with the SQL statement's own transaction.
Oracle can sometimes call a function called from SQL multiple times for the same row depending on the execution plan, so the function should be deterministic for predictable results; marking it DETERMINISTIC where appropriate can also enable performance optimizations like function result caching.
| emp_id | emp_name | salary |
|---|---|---|
| 101 | Alice Johnson | 50000 |
| 102 | Bob Smith | 62000 |
CREATE OR REPLACE FUNCTION fnc_bonus(p_salary NUMBER)
RETURN NUMBER
DETERMINISTIC -- same input always gives same output, enables caching
IS
BEGIN
RETURN p_salary * 0.10;
END;
/
-- Function used directly inside a SQL SELECT
SELECT emp_name, salary, fnc_bonus(salary) AS bonus
FROM employees
ORDER BY fnc_bonus(salary) DESC;| EMP_NAME | SALARY | BONUS |
|---|---|---|
| Bob Smith | 62,000 | 6,200 |
| Alice Johnson | 50,000 | 5,000 |
fnc_bonus is called for every row directly inside SELECT and ORDER BY, something a PROCEDURE could never be used for
A function (not a procedure) can be called directly inside SQL as long as it avoids DML and transaction control, since SQL purity rules require it to behave predictably within the surrounding query's execution.
Parameters are passed to procedures and functions in the declaration section of the procedure or function.
Parameters can be of IN, OUT, or IN OUT type.
IN parameters are read-only and used to pass values into the procedure or function.
OUT parameters are used to return values from the procedure or function to the caller.
IN-OUT parameters allow for both input and output functionality.
-- IN / OUT / IN OUT parameter demonstration
CREATE OR REPLACE PROCEDURE prc_param_demo(
p_in IN NUMBER, -- read-only
p_out OUT VARCHAR2, -- write-only (returns value)
p_inout IN OUT NUMBER -- read AND write
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('IN param : ' || p_in);
p_out := 'Received: ' || p_in;
p_inout := p_inout * 2;
END prc_param_demo;
/
-- Call using named notation
DECLARE
v_out VARCHAR2(100);
v_inout NUMBER := 10;
BEGIN
prc_param_demo(
p_in => 42,
p_out => v_out,
p_inout => v_inout
);
DBMS_OUTPUT.PUT_LINE('OUT : ' || v_out); -- Received: 42
DBMS_OUTPUT.PUT_LINE('INOUT : ' || v_inout); -- 20
END;
/| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
Parameters are passed to procedures and functions in the declaration section of the procedure or function.
In PL/SQL, parameters can be of three types: IN parameters, which are read-only and used to pass values to the procedure; OUT parameters, which return values to the calling program; and IN OUT parameters, which can both receive values and return updated values to the caller.
CREATE OR REPLACE PROCEDURE prc_modes(
p_in IN NUMBER, -- read only
p_out OUT VARCHAR2, -- write only
p_inout IN OUT NUMBER -- read + write
) IS
BEGIN
p_out := 'Received: ' || p_in;
p_inout := p_inout * 2;
END;
/| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
In PL/SQL, parameters can be of three types: IN parameters, which are read-only and used to pass values to the procedure; OUT parameters, which return values to the calling program; and IN OUT parameters, which can both receive values and return updated values to the caller.
Actual parameters are the values or expressions passed to a procedure at the time of call, such as emp_num and amount in raise_sal(emp_num, merit+amount);.
Formal parameters, on the other hand, are the variables declared in the procedure definition, acting as placeholders for actual parameter values during procedure execution.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Actual parameters are the values or expressions passed to a procedure at the time of call, such as emp_num and amount in raise_sal(emp_num, merit+amount);.
PL/SQL subprogram parameters have three modes.
IN (default) passes a read-only value into the subprogram — the parameter acts like a constant.
OUT passes a value back to the caller — the parameter starts as NULL inside and the subprogram writes to it.
IN OUT does both — it passes a value in and the subprogram can read and modify it, sending the modified value back to the caller.
IN parameters are passed by reference; OUT and IN OUT by value (unless NOCOPY hint is used).
CREATE OR REPLACE PROCEDURE prc_salary_info(
p_emp_id IN NUMBER, -- read-only input
p_emp_name OUT VARCHAR2, -- output only
p_salary IN OUT NUMBER -- read + write
) IS
BEGIN
SELECT employee_name, salary
INTO p_emp_name, p_salary
FROM employees
WHERE employee_id = p_emp_id;
-- Modify IN OUT parameter (apply 10% bonus)
p_salary := p_salary * 1.10;
END;
/
-- Call:
DECLARE
v_name VARCHAR2(100);
v_salary NUMBER := 0; -- IN OUT must be initialized
BEGIN
prc_salary_info(101, v_name, v_salary);
DBMS_OUTPUT.PUT_LINE(v_name || ' | New salary: ' || v_salary);
END;| OUTPUT / RESULT |
|---|
| Alice Johnson | New salary: 55000 |
| IN : read-only, cannot be assigned inside procedure |
| OUT : write-only, starts as NULL |
| IN OUT: read and write, value passed both ways |
PL/SQL subprogram parameters have three modes.
Formal parameters are the parameters declared in the procedure or function definition — they define the name, mode (IN/OUT/IN OUT), and datatype.
Actual parameters are the real values or variables passed by the caller when invoking the subprogram.
Actual parameters must be compatible with the corresponding formal parameters in type and mode.
Parameters can be passed positionally (by order) or by name using the => notation, allowing any order and skipping defaulted parameters.
-- Formal parameters (in definition)
CREATE OR REPLACE PROCEDURE prc_raise_salary(
p_emp_id IN NUMBER, -- formal param 1
p_pct IN NUMBER := 10, -- formal param 2 (default 10%)
p_msg OUT VARCHAR2 -- formal param 3
) IS
BEGIN
UPDATE employees SET salary = salary * (1 + p_pct/100)
WHERE employee_id = p_emp_id;
p_msg := 'Salary raised by ' || p_pct || '%';
END;
/
-- Actual parameters (at call site)
DECLARE
v_result VARCHAR2(200);
BEGIN
-- Positional: actual = 101, 15, v_result
prc_raise_salary(101, 15, v_result);
-- Named notation (any order, skip defaults):
prc_raise_salary(p_emp_id => 102, p_msg => v_result);
DBMS_OUTPUT.PUT_LINE(v_result);
END;| OUTPUT / RESULT |
|---|
| Salary raised by 15% |
| Salary raised by 10% (default used) |
| Named notation: p_emp_id => 102 — any order allowed |
| Default parameters can be omitted by caller |
Formal parameters are the parameters declared in the procedure or function definition — they define the name, mode (IN/OUT/IN OUT), and datatype.
Packages in PL/SQL are schema objects that logically group related PL/SQL types, variables, procedures, functions, and cursors.
A package consists of two parts: the specification and the body.
The specification declares the package's public components, while the body defines the implementation details.
Packages enhance modularity, reusability, and performance of applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Package Specification (public interface)
CREATE OR REPLACE PACKAGE pkg_emp AS
c_bonus_rate CONSTANT NUMBER := 0.10; -- public constant
PROCEDURE prc_give_bonus(p_emp_id NUMBER); -- public procedure
FUNCTION fnc_get_salary(p_emp_id NUMBER) RETURN NUMBER; -- public function
END pkg_emp;
/
-- Package Body (implementation)
CREATE OR REPLACE PACKAGE BODY pkg_emp AS
-- Private helper (not visible outside package)
FUNCTION is_eligible(p_emp_id NUMBER) RETURN BOOLEAN IS
v_yrs NUMBER;
BEGIN
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) / 12
INTO v_yrs FROM employees WHERE employee_id = p_emp_id;
RETURN v_yrs >= 1;
END;
PROCEDURE prc_give_bonus(p_emp_id NUMBER) IS
v_sal NUMBER;
BEGIN
IF is_eligible(p_emp_id) THEN
v_sal := fnc_get_salary(p_emp_id);
UPDATE employees SET salary = v_sal + (v_sal * c_bonus_rate)
WHERE employee_id = p_emp_id;
COMMIT;
END IF;
END prc_give_bonus;
FUNCTION fnc_get_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
RETURN v_sal;
END fnc_get_salary;
END pkg_emp;
/
-- Usage
BEGIN
pkg_emp.prc_give_bonus(101);
DBMS_OUTPUT.PUT_LINE('Salary: ' || pkg_emp.fnc_get_salary(101));
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Packages in PL/SQL are schema objects that logically group related PL/SQL types, variables, procedures, functions, and cursors.
Overloading in PL/SQL allows multiple subprograms in a package to share the same name but differ in the number or type of parameters.
Developers implement overloading by defining two or more procedures or functions with the same name within the same package, ensuring that each version has a unique signature.
Overloading enhances the versatility of packages by allowing them to perform varied operations under the same subprogram name.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Overloaded procedures in a package (same name, different signatures)
CREATE OR REPLACE PACKAGE pkg_salary_util AS
-- Version 1: update by employee ID
PROCEDURE prc_update(p_emp_id NUMBER, p_new_sal NUMBER);
-- Version 2: update by employee name
PROCEDURE prc_update(p_emp_name VARCHAR2, p_new_sal NUMBER);
-- Version 3: update by department with percentage
PROCEDURE prc_update(p_dept_id NUMBER, p_pct NUMBER, p_mode CHAR);
END pkg_salary_util;
/
CREATE OR REPLACE PACKAGE BODY pkg_salary_util AS
PROCEDURE prc_update(p_emp_id NUMBER, p_new_sal NUMBER) IS
BEGIN
UPDATE employees SET salary = p_new_sal WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Updated by ID: ' || p_emp_id);
END;
PROCEDURE prc_update(p_emp_name VARCHAR2, p_new_sal NUMBER) IS
BEGIN
UPDATE employees SET salary = p_new_sal
WHERE UPPER(employee_name) = UPPER(p_emp_name);
DBMS_OUTPUT.PUT_LINE('Updated by name: ' || p_emp_name);
END;
PROCEDURE prc_update(p_dept_id NUMBER, p_pct NUMBER, p_mode CHAR) IS
BEGIN
UPDATE employees SET salary = salary * (1 + p_pct/100)
WHERE department_id = p_dept_id;
DBMS_OUTPUT.PUT_LINE('Updated dept ' || p_dept_id || ' by ' || p_pct || '%');
END;
END pkg_salary_util;
/
-- PL/SQL resolves the correct version at compile time
BEGIN
pkg_salary_util.prc_update(101, 70000); -- version 1
pkg_salary_util.prc_update('Alice', 72000); -- version 2
pkg_salary_util.prc_update(10, 5, 'P'); -- version 3
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated — COMMIT to make permanent
Overloading in PL/SQL allows multiple subprograms in a package to share the same name but differ in the number or type of parameters.
PL/SQL packages serve as repositories for storing related functions and procedures.
They enhance data security, allow shared use of common variables among packaged functions and procedures, support overloaded functions, and optimize performance by loading multiple objects into memory simultaneously.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PL/SQL packages serve as repositories for storing related functions and procedures.
To delete a package in PL/SQL, use the DROP PACKAGE [package name] command.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Remove a trigger
DROP TRIGGER trg_validate_sal;
-- Remove a package (spec + body)
DROP PACKAGE pkg_emp;
DROP PACKAGE BODY pkg_emp;
-- Verify
SELECT object_name, object_type FROM user_objects
WHERE object_type IN ('TRIGGER','PACKAGE','PACKAGE BODY');| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
To delete a package in PL/SQL, use the DROP PACKAGE [package name] command.
PL/SQL packages can include a combination of procedures, functions, variables, cursors, and record type statements.
They also contain exception names and Pragmas for error handling.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PL/SQL packages can include a combination of procedures, functions, variables, cursors, and record type statements.
A package specification (spec) is the public interface of a PL/SQL package — it declares what is visible to the outside world.
It contains declarations of public procedures, functions, types, variables, constants, cursors, and exceptions.
The spec does not contain any implementation code — that lives in the package body.
Users and applications can call only what is declared in the spec.
Items declared only in the body are private.
The spec compiles independently and can be compiled without the body.
-- Package SPEC: public interface
CREATE OR REPLACE PACKAGE pkg_employee AS
-- Public constant
c_max_salary CONSTANT NUMBER := 500000;
-- Public type
TYPE t_emp_rec IS RECORD(
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);
-- Public cursor
CURSOR c_all_emps RETURN employees%ROWTYPE;
-- Public procedures/functions (signatures only)
PROCEDURE prc_raise_salary(p_emp_id IN NUMBER, p_pct IN NUMBER DEFAULT 10);
FUNCTION fn_get_salary(p_emp_id IN NUMBER) RETURN NUMBER;
PROCEDURE prc_log_audit(p_action IN VARCHAR2);
END pkg_employee;
/
-- Usage:
-- pkg_employee.prc_raise_salary(101, 15);
-- v_sal := pkg_employee.fn_get_salary(101);
-- DBMS_OUTPUT.PUT_LINE(pkg_employee.c_max_salary);| OUTPUT / RESULT |
|---|
| Package spec compiled successfully |
| Public interface exposed: 2 procedures, 1 function, 1 constant |
| Body can be replaced without recompiling dependents on spec |
| pkg_employee.fn_get_salary(101) 50000 |
A package specification (spec) is the public interface of a PL/SQL package — it declares what is visible to the outside world.
Yes — a package-level variable, declared in the package specification or body but outside any procedure or function, retains its value for the entire database session once the package is first referenced.
This behavior is called package state, and it means each session gets its own independent copy of the package's global variables, persisting until the session ends, the package is recompiled, or it is explicitly reset.
This is useful for caching values, counters, or flags across multiple calls without needing a database table, but it must be used carefully since stale state can cause unexpected results if the session is reused across unrelated logical operations.
The PRAGMA SERIALLY_REUSABLE directive can be added to a package to disable this persistence, resetting its state after every call instead of keeping it for the whole session.
CREATE OR REPLACE PACKAGE pkg_counter AS
PROCEDURE prc_increment;
FUNCTION fnc_get_count RETURN NUMBER;
END pkg_counter;
/
CREATE OR REPLACE PACKAGE BODY pkg_counter AS
g_count NUMBER := 0; -- package-level variable: persists for the session
PROCEDURE prc_increment IS
BEGIN
g_count := g_count + 1;
END;
FUNCTION fnc_get_count RETURN NUMBER IS
BEGIN
RETURN g_count;
END;
END pkg_counter;
/
BEGIN
pkg_counter.prc_increment;
pkg_counter.prc_increment;
DBMS_OUTPUT.PUT_LINE('Count: ' || pkg_counter.fnc_get_count);
END;
/| OUTPUT |
|---|
| Count: 2 |
g_count keeps incrementing across calls within the same session because it is package state, not a local variable reset on each call
Package-level variables persist for the life of the database session that first touches the package — a behavior called package state — unless PRAGMA SERIALLY_REUSABLE is used to reset them after each call.
Triggers in PL/SQL are stored procedures that automatically execute in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE operations.
Triggers are used for maintaining data integrity, enforcing business rules, and auditing changes in the database.
Triggers can be defined to execute before or after the triggering event.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- BEFORE INSERT trigger: validate + auto-populate
CREATE OR REPLACE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
/
-- AFTER UPDATE trigger: audit trail
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit_log(employee_id, old_salary, new_salary, changed_on)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
-- INSTEAD OF trigger on a view
CREATE OR REPLACE TRIGGER trg_emp_view_insert
INSTEAD OF INSERT ON v_employee_summary
FOR EACH ROW
BEGIN
INSERT INTO employees(employee_id, employee_name, salary)
VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.sal);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY | HIRE_DATE |
|---|---|---|---|
| 205 | New Employee | 50,000 | 20-APR-2026 |
Trigger auto-fired on INSERT — hire_date set to SYSDATE by BEFORE INSERT trigger
Triggers in PL/SQL are stored procedures that automatically execute in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE operations.
PL/SQL supports several types of triggers, including BEFORE and AFTER triggers, which execute before or after a DML event on a table, INSTEAD OF triggers, which are defined on views and execute in place of the triggering event and system triggers, which fire in response to system-level events such as logon or DDL statements.
Each type serves different purposes in database management and application development.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- BEFORE trigger: fires before the DML
CREATE OR REPLACE TRIGGER trg_before_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.updated_at := SYSDATE; -- auto-stamp
END;
/
-- AFTER trigger: fires after the DML
CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO deleted_employees
VALUES (:OLD.employee_id, :OLD.employee_name, SYSDATE);
END;
/
-- INSTEAD OF trigger: replaces DML on a view
CREATE OR REPLACE TRIGGER trg_instead_of_insert
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
INSERT INTO employees(employee_id, employee_name)
VALUES (:NEW.emp_id, :NEW.emp_name);
END;
/
-- Statement-level trigger (no FOR EACH ROW)
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO logon_log(username, logon_time)
VALUES (USER, SYSDATE);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY | HIRE_DATE |
|---|---|---|---|
| 205 | New Employee | 50,000 | 20-APR-2026 |
Trigger auto-fired on INSERT — hire_date set to SYSDATE by BEFORE INSERT trigger
PL/SQL supports several types of triggers, including BEFORE and AFTER triggers, which execute before or after a DML event on a table, INSTEAD OF triggers, which are defined on views and execute in place of the triggering event and system triggers, which fire in response to system-level events such as logon or DDL statements.
Database triggers, which are automated procedures triggered by DML events, are used for a variety of tasks including data validation, enforcing business rules, and maintaining audit trails.
The syntax for a trigger typically includes its creation, triggering event specification, and the body of the trigger.
For example: create trigger [trigger_name] [before | after] on [table_name] [for each row] [trigger_body].
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Database triggers, which are automated procedures triggered by DML events, are used for a variety of tasks including data validation, enforcing business rules, and maintaining audit trails.
Triggers are procedures that automatically execute in response to certain events on a table or view, typically used for maintaining consistency, logging, or auditing.
Constraints are rules enforced on data columns to ensure data integrity, such as NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Triggers are procedures that automatically execute in response to certain events on a table or view, typically used for maintaining consistency, logging, or auditing.
A mutating table in PL/SQL is a table that is being modified by an insert, update, or delete operation and cannot be referenced within a trigger.
A constraining table, on the other hand, is involved in enforcing referential integrity constraints.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Mutating error: trigger queries same table being modified
-- FIX: use BEFORE trigger, not AFTER
CREATE OR REPLACE TRIGGER trg_validate_sal
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- Safe: use :NEW directly, never SELECT from employees here
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid salary');
END IF;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY | HIRE_DATE |
|---|---|---|---|
| 205 | New Employee | 50,000 | 20-APR-2026 |
Trigger auto-fired on INSERT — hire_date set to SYSDATE by BEFORE INSERT trigger
A mutating table in PL/SQL is a table that is being modified by an insert, update, or delete operation and cannot be referenced within a trigger.
A mutating table error occurs in PL/SQL when a trigger tries to reference the table that caused the trigger to fire.
This is typically seen in row-level triggers that attempt to access or modify the same table on which the trigger is defined.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Mutating error: trigger queries same table being modified
-- FIX: use BEFORE trigger, not AFTER
CREATE OR REPLACE TRIGGER trg_validate_sal
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- Safe: use :NEW directly, never SELECT from employees here
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid salary');
END IF;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY | HIRE_DATE |
|---|---|---|---|
| 205 | New Employee | 50,000 | 20-APR-2026 |
Trigger auto-fired on INSERT — hire_date set to SYSDATE by BEFORE INSERT trigger
A mutating table error occurs in PL/SQL when a trigger tries to reference the table that caused the trigger to fire.
To remove a trigger in SQL, the command DROP TRIGGER [trigger name] is used.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Remove a trigger
DROP TRIGGER trg_validate_sal;
-- Remove a package (spec + body)
DROP PACKAGE pkg_emp;
DROP PACKAGE BODY pkg_emp;
-- Verify
SELECT object_name, object_type FROM user_objects
WHERE object_type IN ('TRIGGER','PACKAGE','PACKAGE BODY');| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
To remove a trigger in SQL, the command DROP TRIGGER [trigger name] is used.
The WHEN clause in PL/SQL triggers specifies a condition that must be met for the trigger to fire.
It adds a conditional control to trigger execution.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The WHEN clause in PL/SQL triggers specifies a condition that must be met for the trigger to fire.
A trigger in PL/SQL includes components like the triggering event (INSERT, UPDATE, DELETE), the trigger body (the set of statements that are executed when the trigger fires), and optionally, a trigger restriction using the WHEN clause.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
A trigger in PL/SQL includes components like the triggering event (INSERT, UPDATE, DELETE), the trigger body (the set of statements that are executed when the trigger fires), and optionally, a trigger restriction using the WHEN clause.
Basic components of a trigger include the trigger event, the trigger timing (BEFORE, AFTER, INSTEAD OF), and the trigger body containing the logic to be executed.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Basic components of a trigger include the trigger event, the trigger timing (BEFORE, AFTER, INSTEAD OF), and the trigger body containing the logic to be executed.
Stored procedures are named PL/SQL blocks explicitly executed by a user, application, or another subprogram using EXECUTE or CALL.
They accept parameters and perform actions on demand.
Triggers are PL/SQL blocks that fire automatically in response to specific database events (INSERT, UPDATE, DELETE on a table, or DDL/system events).
Triggers cannot accept parameters, cannot be called directly, and are invisible to the end user.
Stored procedures are best for reusable business logic; triggers for enforcing rules automatically.
-- Stored Procedure: explicitly called
CREATE OR REPLACE PROCEDURE prc_audit_salary(
p_emp_id IN NUMBER, p_new_sal IN NUMBER
) IS
BEGIN
INSERT INTO salary_audit(emp_id, new_salary, changed_by, changed_date)
VALUES(p_emp_id, p_new_sal, USER, SYSDATE);
COMMIT;
END;
-- Call explicitly:
-- EXEC prc_audit_salary(101, 60000);
-- Trigger: fires automatically
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
-- Fires automatically on UPDATE — no explicit call needed
INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_by, changed_date)
VALUES(:OLD.employee_id, :OLD.salary, :NEW.salary, USER, SYSDATE);
END;
-- Fires automatically when:
-- UPDATE employees SET salary = 60000 WHERE employee_id = 101;| OUTPUT / RESULT |
|---|
| Stored Proc: called explicitly — EXEC prc_audit_salary(101, 60000) |
| Trigger: fires automatically on UPDATE employees |
| Trigger: no parameters, uses :OLD and :NEW pseudorecords |
| Trigger cannot be called directly |
Stored procedures are named PL/SQL blocks explicitly executed by a user, application, or another subprogram using EXECUTE or CALL.
A database trigger is a named PL/SQL block stored in the database that executes automatically in response to a specified event without being explicitly called.
Trigger types: DML triggers fire on INSERT, UPDATE, or DELETE on a table (BEFORE or AFTER, at row level or statement level).
INSTEAD OF triggers fire on views to intercept DML.
DDL triggers fire on CREATE, ALTER, or DROP statements.
System/database triggers fire on database events like LOGON, LOGOFF, STARTUP, SHUTDOWN, and SERVERERROR.
-- BEFORE INSERT trigger: auto-set audit fields
CREATE OR REPLACE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW -- row-level
BEGIN
:NEW.created_by := USER;
:NEW.created_date := SYSDATE;
IF :NEW.employee_id IS NULL THEN
:NEW.employee_id := emp_seq.NEXTVAL;
END IF;
END;
-- AFTER DELETE trigger: archive deleted rows
CREATE OR REPLACE TRIGGER trg_emp_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_archive
VALUES(:OLD.employee_id, :OLD.employee_name, :OLD.salary, SYSDATE);
END;
-- INSTEAD OF trigger: make view updatable
CREATE OR REPLACE TRIGGER trg_v_emp_upd
INSTEAD OF UPDATE ON v_dept_employees
FOR EACH ROW
BEGIN
UPDATE employees SET salary = :NEW.salary
WHERE employee_id = :OLD.employee_id;
END;| OUTPUT / RESULT |
|---|
| BEFORE INSERT: employee_id auto-assigned from sequence |
| AFTER DELETE: old row archived to employees_archive |
| INSTEAD OF: UPDATE on view redirected to base table |
| Types: BEFORE/AFTER, Row/Statement, INSTEAD OF, DDL, System |
A database trigger is a named PL/SQL block stored in the database that executes automatically in response to a specified event without being explicitly called.
A mutating table error (ORA-04091) occurs when a row-level trigger tries to query or modify the same table that caused the trigger to fire.
For example, an AFTER INSERT FOR EACH ROW trigger on EMPLOYEES that tries to SELECT COUNT(*) FROM EMPLOYEES will fail because the table is in a state of change (mutating).
Solutions include: using a BEFORE statement-level trigger to capture data, using a compound trigger (available from Oracle 11g) to separate row and statement level logic, or using an autonomous transaction pragma.
-- CAUSES ORA-04091 (mutating table)
CREATE OR REPLACE TRIGGER trg_check_dept_count
AFTER INSERT ON employees FOR EACH ROW
BEGIN
DECLARE v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count -- queries mutating table
FROM employees WHERE department_id = :NEW.department_id;
IF v_count > 10 THEN
RAISE_APPLICATION_ERROR(-20010, 'Dept has more than 10 employees');
END IF;
END;
END;
-- FIX: Use COMPOUND TRIGGER
CREATE OR REPLACE TRIGGER trg_check_dept_count_fixed
FOR INSERT ON employees COMPOUND TRIGGER
v_dept_id NUMBER;
BEFORE EACH ROW IS BEGIN
v_dept_id := :NEW.department_id;
END BEFORE EACH ROW;
AFTER STATEMENT IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count -- safe after all rows done
FROM employees WHERE department_id = v_dept_id;
IF v_count > 10 THEN
RAISE_APPLICATION_ERROR(-20010, 'Dept > 10 employees');
END IF;
END AFTER STATEMENT;
END;| OUTPUT / RESULT |
|---|
| ORA-04091: table EMPLOYEES is mutating |
| Compound trigger separates row and statement processing |
| BEFORE EACH ROW: capture :NEW values safely |
| AFTER STATEMENT: query table after all row changes are done |
A mutating table error (ORA-04091) occurs when a row-level trigger tries to query or modify the same table that caused the trigger to fire.
Arrays in PL/SQL, also known as collections, are used to store sets of elements of the same type.
PL/SQL supports three types of collections: VARRAYS, which have a fixed size; NESTED TABLES, which are unbounded; and ASSOCIATIVE ARRAYS, which are key-value pairs.
Arrays are declared in the declaration section of a PL/SQL block or package and are manipulated using collection methods.
-- 1. VARRAY (fixed max size, ordered)
DECLARE
TYPE t_days IS VARRAY(7) OF VARCHAR2(10);
v_days t_days := t_days('Mon','Tue','Wed','Thu','Fri');
BEGIN
FOR i IN 1..v_days.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Day: ' || v_days(i));
END LOOP;
END;
/
-- 2. Nested Table (unbounded, sparse allowed)
DECLARE
TYPE t_names IS TABLE OF VARCHAR2(50);
v_names t_names := t_names('Alice','Bob','Carol');
BEGIN
v_names.EXTEND;
v_names(4) := 'Dave';
v_names.DELETE(2); -- delete Bob
DBMS_OUTPUT.PUT_LINE('Count: ' || v_names.COUNT);
END;
/
-- 3. Associative Array (key-value, most flexible)
DECLARE
TYPE t_sal_map IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
v_sals t_sal_map;
v_key VARCHAR2(50);
BEGIN
v_sals('Alice') := 75000;
v_sals('Bob') := 82000;
v_key := v_sals.FIRST;
WHILE v_key IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(v_key || ': $' || v_sals(v_key));
v_key := v_sals.NEXT(v_key);
END LOOP;
END;
/| ITERATION | EMP_ID | EMP_NAME | ACTION |
|---|---|---|---|
| 1 | 101 | Alice Johnson | Updated salary to 55,000 |
| 2 | 103 | Carol White | Updated salary to 60,500 |
| End | — | — | COMMIT executed — 2 rows saved |
Loop processed 2 employees — each updated individually
Arrays in PL/SQL, also known as collections, are used to store sets of elements of the same type.
A composite data type in PL/SQL allows developers to combine multiple scalar data types into a single structure.
The most common composite data types are RECORDS, which can contain fields of different data types, and COLLECTIONS, such as VARRAYS, NESTED TABLES, and ASSOCIATIVE ARRAYS.
Composite data types are useful for handling related data as a single unit.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- User-defined RECORD type
DECLARE
TYPE t_emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER(10,2),
hire_date DATE
);
v_emp t_emp_rec;
-- %ROWTYPE anchors to a full table row
v_emp2 employees%ROWTYPE;
BEGIN
v_emp.emp_id := 999;
v_emp.emp_name := 'Test User';
v_emp.salary := 55000;
SELECT * INTO v_emp2
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('RECORD : ' || v_emp.emp_name || ' | $' || v_emp.salary);
DBMS_OUTPUT.PUT_LINE('ROWTYPE : ' || v_emp2.employee_name || ' | $' || v_emp2.salary);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
A composite data type in PL/SQL allows developers to combine multiple scalar data types into a single structure.
Index-by tables, also known as associative arrays, are key-value pairs where each key is unique and indexed.
Developers create index-by tables in PL/SQL by declaring a variable of type INDEX BY BINARY_INTEGER or INDEX BY PLS_INTEGER.
These tables are used to store temporary data accessible by an index, which can be of the BINARY_INTEGER or VARCHAR2 data type.
Index-by tables are ideal for lookups and temporary data storage that do not persist beyond the session.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Associative arrays (index-by tables): most flexible collection
DECLARE
-- Integer-indexed
TYPE t_salary_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_sals t_salary_list;
-- String-indexed
TYPE t_dept_map IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(10);
v_depts t_dept_map;
BEGIN
-- Populate integer-indexed
v_sals(1) := 55000;
v_sals(2) := 72000;
v_sals(3) := 88000;
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Salary ' || i || ': $' || v_sals(i));
END LOOP;
-- Populate string-indexed
v_depts('IT') := 'Information Technology';
v_depts('HR') := 'Human Resources';
v_depts('FIN') := 'Finance';
DBMS_OUTPUT.PUT_LINE('IT dept: ' || v_depts('IT'));
DBMS_OUTPUT.PUT_LINE('Count : ' || v_depts.COUNT);
DBMS_OUTPUT.PUT_LINE('Exists : ' || CASE WHEN v_depts.EXISTS('HR') THEN 'YES' ELSE 'NO' END);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| ITERATION | EMP_ID | EMP_NAME | ACTION |
|---|---|---|---|
| 1 | 101 | Alice Johnson | Updated salary to 55,000 |
| 2 | 103 | Carol White | Updated salary to 60,500 |
| End | — | — | COMMIT executed — 2 rows saved |
Loop processed 2 employees — each updated individually
Index-by tables, also known as associative arrays, are key-value pairs where each key is unique and indexed.
A record in PL/SQL is a composite data type that allows the storage of different data types in a single variable.
Developers define records using the %ROWTYPE attribute for rows in a database table or by explicitly specifying the structure of the record.
Records are useful for fetching rows from a table into a single variable and for passing groups of related data as parameters to subprograms.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
-- Explicit record type
TYPE t_emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER(10,2),
hire_date DATE
);
v_emp t_emp_rec;
-- %ROWTYPE: mirrors entire table row
v_emp2 employees%ROWTYPE;
-- Cursor %ROWTYPE
CURSOR c IS SELECT employee_id, employee_name FROM employees WHERE ROWNUM <= 3;
v_row c%ROWTYPE;
BEGIN
-- Populate custom record
v_emp.emp_id := 999;
v_emp.emp_name := 'Test User';
v_emp.salary := 60000;
DBMS_OUTPUT.PUT_LINE('Record: ' || v_emp.emp_name || ' $' || v_emp.salary);
-- Populate %ROWTYPE from query
SELECT * INTO v_emp2 FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('RowType: ' || v_emp2.employee_name);
-- Cursor rowtype
OPEN c;
LOOP
FETCH c INTO v_row;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Cursor row: ' || v_row.employee_name);
END LOOP;
CLOSE c;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY |
|---|---|---|
| 101 | Alice Johnson | 50,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
Top 3 rows — ROWNUM/FETCH FIRST limits result set
A record in PL/SQL is a composite data type that allows the storage of different data types in a single variable.
Collections in PL/SQL are composite data types that allow the storage of multiple elements of the same type.
The three types of collections are associative arrays (index-by tables), nested tables, and varrays (variable-size arrays).
Collections are used to represent sets of data as a single entity, allowing for efficient storage and manipulation of data sets within PL/SQL blocks.
-- 1. Associative Array (index-by table) - session only, not storable in DB
DECLARE
TYPE t_dept_map IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(10);
v_depts t_dept_map;
BEGIN
v_depts('IT') := 'Information Technology';
v_depts('HR') := 'Human Resources';
DBMS_OUTPUT.PUT_LINE('IT: ' || v_depts('IT'));
DBMS_OUTPUT.PUT_LINE('Count: ' || v_depts.COUNT);
END;
/
-- 2. Nested Table - unbounded, storable in DB column
CREATE OR REPLACE TYPE t_phone_list AS TABLE OF VARCHAR2(20);
/
DECLARE
v_phones t_phone_list := t_phone_list('555-0101','555-0102');
BEGIN
v_phones.EXTEND;
v_phones(3) := '555-0103';
v_phones.DELETE(1);
DBMS_OUTPUT.PUT_LINE('Phones: ' || v_phones.COUNT);
END;
/
-- 3. VARRAY - fixed max size, ordered, storable in DB column
CREATE OR REPLACE TYPE t_weekdays AS VARRAY(7) OF VARCHAR2(10);
/
DECLARE
v_days t_weekdays := t_weekdays('Mon','Tue','Wed','Thu','Fri');
BEGIN
DBMS_OUTPUT.PUT_LINE('Days: ' || v_days.COUNT || ' / limit: ' || v_days.LIMIT);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Collections in PL/SQL are composite data types that allow the storage of multiple elements of the same type.
VARRAYS and nested tables serve different purposes in PL/SQL.
VARRAYS are fixed-size arrays, suitable for storing a known quantity of elements with efficient access by index.
Nested tables allow for a variable number of elements and are optimal for situations where the number of elements is unknown or varies.
Nested tables support dynamic SQL operations and can be used in table joins.
Choosing between VARRAYS and nested tables depends on the specific requirements of the application.
-- VARRAY: fixed max size, ordered, always dense
CREATE OR REPLACE TYPE t_weekdays AS VARRAY(7) OF VARCHAR2(10);
/
DECLARE
v_days t_weekdays := t_weekdays('Mon','Tue','Wed','Thu','Fri');
BEGIN
DBMS_OUTPUT.PUT_LINE('Count: ' || v_days.COUNT); -- 5
DBMS_OUTPUT.PUT_LINE('Limit: ' || v_days.LIMIT); -- 7 (max)
v_days.EXTEND;
v_days(6) := 'Sat';
-- v_days.DELETE(3); -- ERROR: cannot delete from VARRAY
FOR i IN 1..v_days.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(i || ': ' || v_days(i));
END LOOP;
END;
/
-- NESTED TABLE: unbounded, can be sparse, storable in DB column
CREATE OR REPLACE TYPE t_phone_list AS TABLE OF VARCHAR2(20);
/
DECLARE
v_phones t_phone_list := t_phone_list('555-0101','555-0102','555-0103');
BEGIN
v_phones.EXTEND(2);
v_phones(4) := '555-0104';
v_phones(5) := '555-0105';
v_phones.DELETE(2); -- sparse: element 2 deleted, count=4
DBMS_OUTPUT.PUT_LINE('Count : ' || v_phones.COUNT); -- 4
DBMS_OUTPUT.PUT_LINE('Exists(2): ' || CASE WHEN v_phones.EXISTS(2) THEN 'YES' ELSE 'NO' END);
-- SET operations on nested tables
-- v_phones MULTISET UNION / INTERSECT / EXCEPT other_table
END;
/
/*
Comparison:
VARRAY Nested Table
Fixed size? YES NO
Sparse? NO YES (after DELETE)
Store in DB? YES YES
Order prsrvd YES YES (initially)
MULTISET ops NO YES
Use when: Known count Dynamic/unknown count
*/| ITERATION | EMP_ID | EMP_NAME | ACTION |
|---|---|---|---|
| 1 | 101 | Alice Johnson | Updated salary to 55,000 |
| 2 | 103 | Carol White | Updated salary to 60,500 |
| End | — | — | COMMIT executed — 2 rows saved |
Loop processed 2 employees — each updated individually
VARRAYS and nested tables serve different purposes in PL/SQL.
User-defined exceptions are custom error conditions declared in the DECLARE section and raised with the RAISE statement.
They allow meaningful business rule violations to be signalled without using cryptic Oracle error codes.
User-defined types in PL/SQL include RECORD types (grouping fields like a struct), TABLE types (collections of records or scalars), VARRAY types (fixed-size arrays), and object types (OOP-style with attributes and methods).
These are defined with TYPE...IS syntax and can be stored in the database schema for reuse.
DECLARE
-- User-defined exception
ex_low_salary EXCEPTION;
ex_bad_dept EXCEPTION;
-- User-defined RECORD type
TYPE t_emp_rec IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.employee_name%TYPE,
salary employees.salary%TYPE
);
v_emp t_emp_rec;
-- User-defined TABLE type (collection)
TYPE t_id_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_ids t_id_list;
BEGIN
SELECT employee_id, employee_name, salary
INTO v_emp.emp_id, v_emp.emp_name, v_emp.salary
FROM employees WHERE employee_id = 101;
IF v_emp.salary < 30000 THEN
RAISE ex_low_salary;
END IF;
DBMS_OUTPUT.PUT_LINE(v_emp.emp_name || ': ' || v_emp.salary);
EXCEPTION
WHEN ex_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Error: salary below minimum threshold');
END;| OUTPUT / RESULT |
|---|
| Alice Johnson: 50000 (salary >= 30000, no exception) |
| ex_low_salary would fire if salary < 30000 |
| t_emp_rec: groups emp_id, emp_name, salary in one variable |
| t_id_list: collection of NUMBER values indexed by integer |
User-defined exceptions are custom error conditions declared in the DECLARE section and raised with the RAISE statement.
BULK COLLECT in PL/SQL allows for fetching multiple rows into a collection with a single context switch between SQL and PL/SQL, significantly improving performance over row-by-row fetching.
It is used with SELECT statements and in conjunction with the FORALL statement for bulk DML operations.
BULK COLLECT reduces CPU time and increases efficiency when processing large data sets.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
TYPE t_salaries IS TABLE OF employees.salary%TYPE;
v_ids t_emp_ids;
v_sals t_salaries;
BEGIN
-- BULK COLLECT: single SQL fetch into collections
SELECT employee_id, salary
BULK COLLECT INTO v_ids, v_sals
FROM employees
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Fetched ' || v_ids.COUNT || ' employees');
FOR i IN 1..v_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_ids(i) || ' Salary: $' || v_sals(i));
END LOOP;
END;
/
-- With LIMIT clause for large datasets (avoid memory issues)
DECLARE
TYPE t_emp IS TABLE OF employees%ROWTYPE;
v_emps t_emp;
CURSOR c IS SELECT * FROM employees;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_emps LIMIT 100; -- process 100 at a time
EXIT WHEN v_emps.COUNT = 0;
-- process batch here
DBMS_OUTPUT.PUT_LINE('Batch of: ' || v_emps.COUNT);
END LOOP;
CLOSE c;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
BULK COLLECT in PL/SQL allows for fetching multiple rows into a collection with a single context switch between SQL and PL/SQL, significantly improving performance over row-by-row fetching.
The FORALL statement in PL/SQL is used for performing bulk DML operations (INSERT, UPDATE, DELETE) on multiple rows in a single operation.
FORALL works with collections to process all elements in the collection with a single context switch.
This significantly improves performance when updating or inserting large volumes of data by minimizing the overhead associated with individual row operations.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
TYPE t_sals IS TABLE OF NUMBER;
v_ids t_ids;
v_sals t_sals;
BEGIN
SELECT employee_id, salary
BULK COLLECT INTO v_ids, v_sals
FROM employees WHERE department_id = 10;
-- FORALL: single round-trip for all updates
FORALL i IN 1..v_ids.COUNT
UPDATE employees
SET salary = v_sals(i) * 1.10
WHERE employee_id = v_ids(i);
DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
COMMIT;
-- FORALL with SAVE EXCEPTIONS: continue even if some rows fail
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
INSERT INTO salary_history(emp_id, salary, snapshot_date)
VALUES (v_ids(i), v_sals(i), SYSDATE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Bulk errors: ' || SQL%BULK_EXCEPTIONS.COUNT);
ROLLBACK;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
The FORALL statement in PL/SQL is used for performing bulk DML operations (INSERT, UPDATE, DELETE) on multiple rows in a single operation.
The BULK COLLECT INTO clause in PL/SQL enables the fetching of multiple rows into a PL/SQL collection with a single operation.
This feature significantly improves performance by reducing context switches between the SQL and PL/SQL engines.
Developers use BULK COLLECT INTO within a SELECT statement or in combination with FETCH operations in cursors for efficient data retrieval operations.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
TYPE t_names IS TABLE OF employees.employee_name%TYPE;
TYPE t_sals IS TABLE OF employees.salary%TYPE;
v_ids t_ids;
v_names t_names;
v_sals t_sals;
BEGIN
-- BULK COLLECT INTO: fetch all rows in one operation
SELECT employee_id, employee_name, salary
BULK COLLECT INTO v_ids, v_names, v_sals
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;
DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_ids.COUNT || ' rows');
FOR i IN 1..v_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i) || ' | $' || v_sals(i));
END LOOP;
-- With cursor and LIMIT for large tables
DECLARE
CURSOR c IS SELECT * FROM employees;
TYPE t_emp IS TABLE OF employees%ROWTYPE;
v_emps t_emp;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_emps LIMIT 50;
EXIT WHEN v_emps.COUNT = 0;
DBMS_OUTPUT.PUT_LINE('Batch: ' || v_emps.COUNT);
END LOOP;
CLOSE c;
END;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
The BULK COLLECT INTO clause in PL/SQL enables the fetching of multiple rows into a PL/SQL collection with a single operation.
PL/SQL collections, such as associative arrays, nested tables, and varrays, are used to handle sets of data as single entities.
These collections allow for bulk data operations, significantly improving performance over row-by-row processing.
Developers use collections to fetch, manipulate, and return multiple rows in a single PL/SQL block.
This approach reduces context switches between SQL and PL/SQL, enhancing efficiency.
PL/SQL collections are fundamental in processing large data sets effectively.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
TYPE t_sals IS TABLE OF NUMBER;
v_ids t_ids;
v_sals t_sals;
CURSOR c IS
SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
-- BULK COLLECT: fetch all rows at once
OPEN c;
FETCH c BULK COLLECT INTO v_ids, v_sals;
CLOSE c;
DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_ids.COUNT || ' employees');
-- Apply 10% raise to all
FOR i IN 1..v_sals.COUNT LOOP
v_sals(i) := v_sals(i) * 1.10;
END LOOP;
-- FORALL: single SQL call for all updates
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET salary = v_sals(i)
WHERE employee_id = v_ids(i);
DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
COMMIT;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
PL/SQL collections, such as associative arrays, nested tables, and varrays, are used to handle sets of data as single entities.
PL/SQL collections such as associative arrays, nested tables, and VARRAYs are used to handle bulk data operations efficiently by allowing operations on sets of data as a single unit.
BULK COLLECT fetches multiple rows into a collection in a single context switch.
FORALL performs bulk DML operations on all collection elements with one SQL call.
Together they significantly reduce round trips between PL/SQL and SQL engines, improving performance dramatically for large data sets.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
TYPE t_emp_id_tab IS TABLE OF employees.employee_id%TYPE;
TYPE t_salary_tab IS TABLE OF employees.salary%TYPE;
v_emp_ids t_emp_id_tab;
v_salaries t_salary_tab;
v_new_sals t_salary_tab;
BEGIN
-- BULK COLLECT: single fetch of all relevant rows
SELECT employee_id, salary
BULK COLLECT INTO v_emp_ids, v_salaries
FROM employees
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE('Employees fetched: ' || v_emp_ids.COUNT);
-- Calculate new salaries in PL/SQL (complex logic possible here)
v_new_sals := t_salary_tab();
v_new_sals.EXTEND(v_salaries.COUNT);
FOR i IN 1..v_salaries.COUNT LOOP
v_new_sals(i) := ROUND(v_salaries(i) * 1.10, 2); -- 10% raise
END LOOP;
-- FORALL: single SQL call for all updates (no context switch per row)
FORALL i IN 1..v_emp_ids.COUNT
UPDATE employees
SET salary = v_new_sals(i)
WHERE employee_id = v_emp_ids(i);
DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
COMMIT;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
PL/SQL collections such as associative arrays, nested tables, and VARRAYs are used to handle bulk data operations efficiently by allowing operations on sets of data as a single unit.
By default, if any single row in a FORALL bulk DML operation raises an exception, the entire FORALL statement stops immediately and no further rows are processed.
Adding SAVE EXCEPTIONS to the FORALL statement tells Oracle to continue processing the remaining rows even after some rows fail, instead of stopping at the first error.
All the errors that occurred are collected in the implicit collection SQL%BULK_EXCEPTIONS, where each entry records the index of the failed row and the corresponding error code.
After the FORALL completes, the code typically loops through SQL%BULK_EXCEPTIONS to log or report which rows failed and why, since the statement itself only raises a single generic exception, ORA-24381, after all rows have been attempted.
| emp_id | salary |
|---|---|
| 101 | 50000 |
| 102 | -1000 |
| 103 | 55000 |
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
TYPE t_sals IS TABLE OF NUMBER;
v_ids t_ids := t_ids(101, 102, 103);
v_sals t_sals := t_sals(55000, -1000, 60000); -- -1000 violates a CHECK constraint
BEGIN
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
UPDATE employees SET salary = v_sals(i) WHERE employee_id = v_ids(i);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Row ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' failed: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
COMMIT; -- keep the rows that succeeded
END;
/| ROW_INDEX | RESULT |
|---|---|
| 1 | Updated successfully |
| 2 | Failed — logged in SQL%BULK_EXCEPTIONS |
| 3 | Updated successfully |
Rows 1 and 3 succeed and are committed; row 2's failure is recorded but doesn't stop the other two rows from being processed
SAVE EXCEPTIONS lets a FORALL statement continue past row-level failures instead of stopping at the first one, recording all failures in SQL%BULK_EXCEPTIONS for later inspection.
Yes — PL/SQL fully supports recursive procedures and functions, where a subprogram calls itself either directly or indirectly through another subprogram, as long as a base case eventually stops the recursion.
Each recursive call creates a new instance of the subprogram's local variables on the PL/SQL call stack, separate from the previous call's variables, which is what allows recursion to work correctly.
Oracle does impose a maximum recursion depth, controlled by the MAX_ENABLED_ROLES-independent limit tied to PGA memory and the database parameter OPEN_CURSORS in some cases, but in practice the relevant constraint is available PGA memory — deep recursion can raise ORA-06512 with a stack space error if it goes too far.
For processing large hierarchies or datasets, an iterative approach using a cursor loop or a recursive CTE in SQL is often preferred over deep PL/SQL recursion, since it avoids consuming a large, growing stack of stored procedure calls.
CREATE OR REPLACE FUNCTION fnc_factorial(p_n NUMBER)
RETURN NUMBER IS
BEGIN
IF p_n <= 1 THEN
RETURN 1; -- base case stops the recursion
ELSE
RETURN p_n * fnc_factorial(p_n - 1); -- recursive call
END IF;
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('5! = ' || fnc_factorial(5));
END;
/| OUTPUT |
|---|
| 5! = 120 |
fnc_factorial(5) calls fnc_factorial(4), which calls fnc_factorial(3), and so on, until the base case p_n <= 1 returns 1 and the calls resolve back up the stack
PL/SQL supports recursive procedures and functions with a proper base case, but very deep recursion can exhaust PGA stack space and raise an error, so iterative or SQL-based recursive approaches are often safer for large datasets.
Transactions in PL/SQL are managed using the COMMIT, ROLLBACK, and SAVEPOINT statements.
A COMMIT statement makes all changes made in the current transaction permanent.
A ROLLBACK statement undoes changes made in the current transaction.
SAVEPOINT allows for partial rollbacks to a named point within the transaction.
Transactions ensure data integrity and consistency.
| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
DECLARE
e_insufficient_funds EXCEPTION;
v_balance NUMBER;
BEGIN
SELECT balance INTO v_balance FROM accounts WHERE account_id = 101;
IF v_balance < 1000 THEN
RAISE e_insufficient_funds;
END IF;
SAVEPOINT before_transfer;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
-- Verify
SELECT balance INTO v_balance FROM accounts WHERE account_id = 101;
IF v_balance < 0 THEN
ROLLBACK TO before_transfer; -- partial rollback
DBMS_OUTPUT.PUT_LINE('Transfer reversed - insufficient funds');
ELSE
COMMIT;
DBMS_OUTPUT.PUT_LINE('Transfer committed successfully');
END IF;
EXCEPTION
WHEN e_insufficient_funds THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back: insufficient funds');
END;
/| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Transactions in PL/SQL are managed using the COMMIT, ROLLBACK, and SAVEPOINT statements.
The SAVEPOINT, ROLLBACK, and COMMIT commands manage transactions in PL/SQL.
Developers use SAVEPOINT to mark a transactional savepoint within a transaction.
This action allows partial rollbacks using the ROLLBACK command to the specified savepoint without affecting the entire transaction.
COMMIT command finalizes the changes made during the transaction, making them permanent in the database.
ROLLBACK, without specifying a savepoint, reverts all changes made in the transaction.
| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
BEGIN
-- Mark a savepoint before making changes
SAVEPOINT before_updates;
INSERT INTO employees(employee_id, employee_name, salary)
VALUES (201, 'Alice', 55000);
UPDATE employees SET salary = 75000 WHERE employee_id = 101;
-- Something goes wrong — rollback to savepoint (partial rollback)
IF (1 = 1) THEN -- simulating a business rule failure
ROLLBACK TO before_updates;
DBMS_OUTPUT.PUT_LINE('Changes reversed to savepoint');
ELSE
COMMIT;
DBMS_OUTPUT.PUT_LINE('All changes committed');
END IF;
END;
/
-- Full rollback example
BEGIN
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT; -- make permanent
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- undo everything
DBMS_OUTPUT.PUT_LINE('Transaction rolled back: ' || SQLERRM);
END;
/| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY | STATUS |
|---|---|---|---|---|
| 205 | New Employee | 10 | 50,000 | 1 row inserted |
INSERT successful — use COMMIT to make permanent
The SAVEPOINT, ROLLBACK, and COMMIT commands manage transactions in PL/SQL.
Autonomous transactions in PL/SQL are independent transactions initiated within another transaction.
Developers implement autonomous transactions by declaring a routine (procedure or function) with the PRAGMA AUTONOMOUS_TRANSACTION; directive.
This directive ensures that the autonomous transaction can commit or rollback independently of the parent transaction.
Autonomous transactions are useful for logging and error-handling mechanisms in a PL/SQL block.
-- Autonomous transaction: commits independently of parent
CREATE OR REPLACE PROCEDURE prc_log_error(
p_module VARCHAR2,
p_message VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION; -- key directive
BEGIN
INSERT INTO error_log(module, message, logged_at)
VALUES (p_module, p_message, SYSDATE);
COMMIT; -- commits ONLY this autonomous transaction
END prc_log_error;
/
-- Main procedure: uses autonomous logging
CREATE OR REPLACE PROCEDURE prc_process_order(p_order_id NUMBER) IS
BEGIN
UPDATE orders SET status = 'PROCESSING' WHERE order_id = p_order_id;
-- This commit is independent of the parent transaction
prc_log_error('prc_process_order', 'Processing order ' || p_order_id);
-- If we ROLLBACK here, the log entry is still saved
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Order rolled back but log entry saved');
END;
/| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
Autonomous transactions in PL/SQL are independent transactions initiated within another transaction.
Ensuring the integrity of data in concurrent PL/SQL transactions requires leveraging Oracle's locking mechanisms and transaction isolation levels.
Developers use optimistic and pessimistic locking to manage concurrent access to data.
Setting appropriate isolation levels prevents phenomena like dirty reads, non-repeatable reads, and phantom reads.
These methods maintain data consistency and prevent anomalies in concurrent environments.
Data integrity in PL/SQL transactions is fundamental for reliable and accurate database operations.
-- Pessimistic locking: lock rows before modifying
CREATE OR REPLACE PROCEDURE prc_reserve_seat(
p_flight_id NUMBER,
p_seat VARCHAR2,
p_passenger NUMBER
) IS
v_status VARCHAR2(20);
BEGIN
-- SELECT FOR UPDATE locks the row until COMMIT/ROLLBACK
SELECT seat_status INTO v_status
FROM flight_seats
WHERE flight_id = p_flight_id AND seat_no = p_seat
FOR UPDATE NOWAIT; -- NOWAIT: fail immediately if locked
IF v_status = 'AVAILABLE' THEN
UPDATE flight_seats
SET seat_status = 'RESERVED', passenger_id = p_passenger
WHERE flight_id = p_flight_id AND seat_no = p_seat;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Seat reserved: ' || p_seat);
ELSE
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Seat already taken: ' || v_status);
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- Isolation level: SERIALIZABLE prevents dirty/phantom reads
BEGIN
EXECUTE IMMEDIATE 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE';
-- ... critical operations ...
COMMIT;
END;
/| DYNAMIC_SQL | EXECUTED | ROWS_AFFECTED |
|---|---|---|
| UPDATE employees SET... | 2 rows | |
| DROP TABLE temp_tbl | Table removed |
EXECUTE IMMEDIATE runs SQL assembled as a string at runtime
Ensuring the integrity of data in concurrent PL/SQL transactions requires leveraging Oracle's locking mechanisms and transaction isolation levels.
COMMIT refers to the process of making all database changes permanent and clearing all savepoints, whereas SAVEPOINT allows for the creation of intermediate points within a transaction, enabling partial rollbacks if necessary.
| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
BEGIN
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;
SAVEPOINT sp_debit;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1002;
SAVEPOINT sp_credit;
COMMIT; -- saves both
-- OR: ROLLBACK TO sp_debit; undo only credit part
END;
/| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated — COMMIT to make permanent
COMMIT refers to the process of making all database changes permanent and clearing all savepoints, whereas SAVEPOINT allows for the creation of intermediate points within a transaction, enabling partial rollbacks if necessary.
The ROLLBACK statement in SQL is used to undo transactions that have not yet been saved to the database.
It restores the database to its last committed state.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The ROLLBACK statement in SQL is used to undo transactions that have not yet been saved to the database.
SELECT ... FOR UPDATE locks the selected rows for the duration of the current transaction, preventing other sessions from updating or deleting those same rows until the transaction commits or rolls back.
This is a form of pessimistic locking, used when a session needs to read a row and be confident it can safely modify it afterward without another session changing it in between.
By default, if the rows are already locked by another session, SELECT FOR UPDATE waits until that lock is released, which can cause the session to hang if the other transaction is long-running.
Adding the NOWAIT clause makes the statement fail immediately with ORA-00054 instead of waiting, while the WAIT n clause waits only for a specified number of seconds before failing — both let an application avoid being blocked indefinitely.
| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5000 |
| 1002 | Bob | 3200 |
DECLARE
v_balance accounts.balance%TYPE;
BEGIN
-- Lock the row; fail immediately if another session already holds the lock
SELECT balance INTO v_balance
FROM accounts
WHERE account_id = 1001
FOR UPDATE NOWAIT;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -54 THEN
DBMS_OUTPUT.PUT_LINE('Row is locked by another session — try again later');
ELSE
RAISE;
END IF;
END;
/| SCENARIO | RESULT |
|---|---|
| Row not locked elsewhere | Row locked, update proceeds, then committed |
| Row already locked by another session | ORA-00054 raised immediately due to NOWAIT |
Without NOWAIT, the second scenario would instead make this session wait indefinitely until the other session's lock is released
SELECT FOR UPDATE locks rows so only the current session can modify them until commit or rollback, and NOWAIT (or WAIT n) controls whether the session fails immediately or after a timeout instead of waiting indefinitely for an already-locked row.
No — a standard DML trigger cannot directly execute COMMIT, ROLLBACK, or SAVEPOINT, because the trigger fires as part of the same transaction as the statement that triggered it, and Oracle does not allow ending or partially undoing a transaction in the middle of itself.
Attempting to do so raises ORA-04092, 'cannot COMMIT in a trigger'.
The workaround is to mark the trigger's logic as an autonomous transaction using PRAGMA AUTONOMOUS_TRANSACTION, which lets that block run as an independent transaction with its own commit and rollback, separate from the triggering statement's transaction.
This is commonly used for audit logging inside triggers, where the log entry should be saved permanently even if the main transaction later rolls back.
-- This trigger would raise ORA-04092 if it tried to COMMIT directly:
-- CREATE TRIGGER trg_bad AFTER INSERT ON employees
-- FOR EACH ROW BEGIN
-- INSERT INTO audit_log VALUES(...);
-- COMMIT; -- ORA-04092
-- END;
-- Correct approach: autonomous transaction
CREATE OR REPLACE PROCEDURE prc_write_audit(p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log(message, logged_at) VALUES(p_msg, SYSDATE);
COMMIT; -- commits only this autonomous transaction, not the trigger's caller
END;
/
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
prc_write_audit('New employee inserted: ' || :NEW.employee_id);
END;
/| SCENARIO | RESULT |
|---|---|
| Trigger COMMITs directly | ORA-04092: cannot COMMIT in a trigger |
| Trigger calls autonomous transaction procedure | Audit row committed independently; main transaction continues normally |
Even if the INSERT into employees is later rolled back, the audit_log row written via the autonomous transaction stays committed
A regular trigger cannot COMMIT or ROLLBACK because it shares the triggering statement's transaction, raising ORA-04092 if attempted — the workaround is PRAGMA AUTONOMOUS_TRANSACTION, which gives the trigger logic its own independent transaction.
Oracle supports two transaction isolation levels via the SQL standard SET TRANSACTION ISOLATION LEVEL syntax: READ COMMITTED and SERIALIZABLE.
READ COMMITTED, the default in Oracle, guarantees that a query only sees data that was committed before that query began, preventing dirty reads, but it does not prevent non-repeatable reads if another transaction commits a change between two queries in the same transaction.
SERIALIZABLE makes the entire transaction see a consistent snapshot of the database as it existed at the start of the transaction, preventing both dirty reads and non-repeatable reads, at the cost of a higher chance of ORA-08177 'cannot serialize access' errors under heavy concurrent write contention.
Oracle does not support the standard SQL isolation levels READ UNCOMMITTED or REPEATABLE READ by name, because its multiversion concurrency control architecture already prevents dirty reads by default, making READ UNCOMMITTED unnecessary and REPEATABLE READ effectively achieved differently through SERIALIZABLE.
-- Default isolation level (no need to set explicitly)
-- Each query sees only data committed before that query started
SELECT balance FROM accounts WHERE account_id = 1001;
-- SERIALIZABLE: the whole transaction sees one consistent snapshot
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 1001;
-- ... later in the same transaction ...
SELECT balance FROM accounts WHERE account_id = 1001; -- same snapshot, even if another session committed a change
COMMIT;| ISOLATION_LEVEL | PREVENTS_DIRTY_READS | PREVENTS_NON_REPEATABLE_READS |
|---|---|---|
| READ COMMITTED (default) | Yes | No |
| SERIALIZABLE | Yes | Yes |
Under SERIALIZABLE, both SELECT statements in the transaction return the same balance even if another session commits a change to that row in between
Oracle offers READ COMMITTED (the default, preventing dirty reads only) and SERIALIZABLE (a consistent snapshot for the whole transaction), and doesn't need READ UNCOMMITTED since its architecture already avoids dirty reads.
Dynamic SQL in PL/SQL allows the execution of SQL statements that are constructed and possibly modified at runtime.
It is used for operations where the exact SQL operation cannot be predetermined.
Dynamic SQL is executed using the EXECUTE IMMEDIATE statement for single SQL statements or the DBMS_SQL package for more complex operations involving multiple steps or returning multiple rows.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_table VARCHAR2(30) := 'employees';
v_col VARCHAR2(30) := 'salary';
v_val NUMBER;
v_sql VARCHAR2(500);
v_count NUMBER;
BEGIN
-- Dynamic SELECT (table/column name not known until runtime)
v_sql := 'SELECT MAX(' || v_col || ') FROM ' || v_table;
EXECUTE IMMEDIATE v_sql INTO v_val;
DBMS_OUTPUT.PUT_LINE('Max ' || v_col || ': ' || v_val);
-- Dynamic DML with bind variable (prevents SQL injection)
v_sql := 'UPDATE employees SET salary = salary * 1.05 WHERE department_id = :1';
EXECUTE IMMEDIATE v_sql USING 10;
DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
-- Dynamic DDL
EXECUTE IMMEDIATE 'CREATE TABLE tmp_log (msg VARCHAR2(200), ts DATE)';
DBMS_OUTPUT.PUT_LINE('Temp table created');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| DYNAMIC_SQL | EXECUTED | ROWS_AFFECTED |
|---|---|---|
| UPDATE employees SET... | 2 rows | |
| DROP TABLE temp_tbl | Table removed |
EXECUTE IMMEDIATE runs SQL assembled as a string at runtime
Dynamic SQL in PL/SQL allows the execution of SQL statements that are constructed and possibly modified at runtime.
Dynamic SQL in PL/SQL allows the execution of SQL statements that are constructed dynamically at runtime.
Developers use the EXECUTE IMMEDIATE statement for single DML operations or the OPEN FOR, FETCH, and CLOSE statements for queries that return multiple rows.
Dynamic SQL is powerful for writing flexible code that can operate on database objects whose names are not known until runtime.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
DECLARE
v_sql VARCHAR2(4000);
v_count NUMBER;
v_emp_name VARCHAR2(100);
v_dept_id NUMBER := 10;
BEGIN
-- Dynamic query with bind variable (safe - prevents SQL injection)
v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :1';
EXECUTE IMMEDIATE v_sql INTO v_count USING v_dept_id;
DBMS_OUTPUT.PUT_LINE('Dept 10 headcount: ' || v_count);
-- Dynamic query returning a row
v_sql := 'SELECT employee_name FROM employees WHERE employee_id = :emp_id';
EXECUTE IMMEDIATE v_sql INTO v_emp_name USING 101;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
-- Dynamic DML
v_sql := 'UPDATE employees SET salary = salary * :pct WHERE department_id = :dept';
EXECUTE IMMEDIATE v_sql USING 1.05, v_dept_id;
DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
COMMIT;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| DYNAMIC_SQL | EXECUTED | ROWS_AFFECTED |
|---|---|---|
| UPDATE employees SET... | 2 rows | |
| DROP TABLE temp_tbl | Table removed |
EXECUTE IMMEDIATE runs SQL assembled as a string at runtime
Dynamic SQL in PL/SQL allows the execution of SQL statements that are constructed dynamically at runtime.
EXECUTE IMMEDIATE is the simpler, native PL/SQL way to run dynamic SQL — it parses and executes a SQL string in a single statement, and works well when the number and types of bind variables are known at compile time.
The DBMS_SQL package is a more granular, API-based approach that separates parsing, binding, and execution into distinct steps, giving finer control over the process.
DBMS_SQL is needed in situations EXECUTE IMMEDIATE can't easily handle, such as when the number of bind variables or columns in the result set is not known until runtime, since EXECUTE IMMEDIATE requires a fixed, known set of bind variables in its USING clause.
EXECUTE IMMEDIATE is generally preferred for its simplicity and slightly better performance whenever the SQL structure is known in advance; DBMS_SQL is reserved for genuinely dynamic cases like generic table-processing utilities.
-- EXECUTE IMMEDIATE: simple, fixed number of binds
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM employees WHERE department_id = :1'
INTO v_count USING 10;
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/
-- DBMS_SQL: needed when the number of binds is unknown until runtime
DECLARE
v_cursor INTEGER;
v_rows INTEGER;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor,
'UPDATE employees SET salary = salary * 1.05 WHERE department_id = :dept',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept', 10);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
DBMS_SQL.CLOSE_CURSOR(v_cursor);
DBMS_OUTPUT.PUT_LINE('Rows updated: ' || v_rows);
END;
/| APPROACH | BEST_USED_WHEN |
|---|---|
| EXECUTE IMMEDIATE | Bind count and types known at compile time |
| DBMS_SQL | Bind count or result columns only known at runtime |
Both examples achieve a similar result, but DBMS_SQL requires explicit OPEN_CURSOR, PARSE, BIND_VARIABLE, EXECUTE, and CLOSE_CURSOR steps instead of one statement
EXECUTE IMMEDIATE is the simpler, faster choice when the bind variables and result columns are known in advance, while DBMS_SQL is needed when the number of binds or columns is only known at runtime.
Object-oriented concepts in PL/SQL are implemented through object types and methods.
Developers define object types with attributes and methods, resembling classes in other programming languages.
Instances of object types can be stored in database tables, and methods can be invoked on these instances.
This approach enables encapsulation, inheritance, and polymorphism, aligning with object-oriented programming principles.
-- Object type (class equivalent)
CREATE OR REPLACE TYPE t_animal AS OBJECT (
name VARCHAR2(50),
species VARCHAR2(50),
MEMBER PROCEDURE make_sound,
MEMBER FUNCTION describe RETURN VARCHAR2
) NOT FINAL; -- allows inheritance
/
CREATE OR REPLACE TYPE BODY t_animal AS
MEMBER PROCEDURE make_sound IS
BEGIN
DBMS_OUTPUT.PUT_LINE(name || ' makes a sound');
END;
MEMBER FUNCTION describe RETURN VARCHAR2 IS
BEGIN
RETURN name || ' is a ' || species;
END;
END;
/
-- Subtype (inheritance)
CREATE OR REPLACE TYPE t_dog UNDER t_animal (
breed VARCHAR2(50),
OVERRIDING MEMBER PROCEDURE make_sound
);
/
CREATE OR REPLACE TYPE BODY t_dog AS
OVERRIDING MEMBER PROCEDURE make_sound IS
BEGIN
DBMS_OUTPUT.PUT_LINE(name || ' barks! Breed: ' || breed);
END;
END;
/
-- Usage
DECLARE
v_dog t_dog := t_dog('Rex', 'Canis lupus', 'Labrador');
BEGIN
v_dog.make_sound;
DBMS_OUTPUT.PUT_LINE(v_dog.describe);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Object-oriented concepts in PL/SQL are implemented through object types and methods.
Polymorphism in PL/SQL is implemented through subprogram overloading — defining multiple procedures or functions with the same name but different parameter signatures (different number, types, or order of parameters).
Oracle resolves which version to call based on the actual parameters passed.
Overloading is only allowed within a package (not standalone procedures).
It improves API usability by allowing one logical operation to work with different data types without requiring the caller to use different names.
CREATE OR REPLACE PACKAGE pkg_format AS
-- Overloaded functions — same name, different signatures
-- Format a NUMBER
FUNCTION fmt(p_val IN NUMBER) RETURN VARCHAR2;
-- Format a DATE
FUNCTION fmt(p_val IN DATE) RETURN VARCHAR2;
-- Format a VARCHAR2
FUNCTION fmt(p_val IN VARCHAR2, p_upper IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;
END pkg_format;
/
CREATE OR REPLACE PACKAGE BODY pkg_format AS
FUNCTION fmt(p_val IN NUMBER) RETURN VARCHAR2 IS
BEGIN RETURN TO_CHAR(p_val, '999,999.99'); END;
FUNCTION fmt(p_val IN DATE) RETURN VARCHAR2 IS
BEGIN RETURN TO_CHAR(p_val, 'DD-MON-YYYY HH24:MI'); END;
FUNCTION fmt(p_val IN VARCHAR2, p_upper IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2 IS
BEGIN
RETURN CASE WHEN p_upper THEN UPPER(p_val) ELSE INITCAP(p_val) END;
END;
END pkg_format;
/
-- Usage:
-- pkg_format.fmt(50000) '50,000.00'
-- pkg_format.fmt(SYSDATE) '27-APR-2026 14:30'
-- pkg_format.fmt('alice',TRUE) 'ALICE'| OUTPUT / RESULT |
|---|
| pkg_format.fmt(50000) ' 50,000.00' |
| pkg_format.fmt(SYSDATE) '27-APR-2026 14:30' |
| pkg_format.fmt('hello', TRUE) 'HELLO' |
| Oracle resolves correct version by parameter types at compile time |
Polymorphism in PL/SQL is implemented through subprogram overloading — defining multiple procedures or functions with the same name but different parameter signatures (different number, types, or order of parameters).
Every Oracle object type automatically has a default constructor — a function with the same name as the type that takes one argument per attribute and returns a new instance with those attributes set.
A member method is a function or procedure defined inside the object type's body that operates on a specific instance, implicitly receiving that instance as a hidden SELF parameter, similar to 'this' in other object-oriented languages.
Member methods are declared in the object type specification using the MEMBER keyword and implemented in the type body, allowing behavior to be bundled together with the data it operates on.
A custom constructor can also be defined explicitly using the CONSTRUCTOR FUNCTION syntax, which is useful when the default attribute-by-attribute constructor isn't sufficient, such as when some attributes should be computed rather than supplied directly.
CREATE OR REPLACE TYPE t_employee AS OBJECT (
emp_name VARCHAR2(100),
salary NUMBER,
MEMBER FUNCTION get_annual_salary RETURN NUMBER,
MEMBER PROCEDURE give_raise(p_pct NUMBER)
);
/
CREATE OR REPLACE TYPE BODY t_employee AS
MEMBER FUNCTION get_annual_salary RETURN NUMBER IS
BEGIN
RETURN SELF.salary * 12; -- SELF refers to the current instance
END;
MEMBER PROCEDURE give_raise(p_pct NUMBER) IS
BEGIN
SELF.salary := SELF.salary * (1 + p_pct/100);
END;
END;
/
DECLARE
v_emp t_employee := t_employee('Alice Johnson', 50000); -- default constructor
BEGIN
v_emp.give_raise(10);
DBMS_OUTPUT.PUT_LINE('New annual salary: ' || v_emp.get_annual_salary);
END;
/| OUTPUT |
|---|
| New annual salary: 660000 |
give_raise updates SELF.salary to 55000, and get_annual_salary then returns 55000 * 12 = 660000
Every object type gets an automatic default constructor for creating instances, and MEMBER methods let you attach behavior to that type's data, implicitly operating on the current instance via a hidden SELF parameter.
The UTL_FILE package in PL/SQL enables reading from and writing to files on the server's file system.
Developers use it to open files (UTL_FILE.FOPEN), write data (UTL_FILE.PUT or UTL_FILE.PUT_LINE), read data (UTL_FILE.GET_LINE), and close files (UTL_FILE.FCLOSE or UTL_FILE.FCLOSE_ALL).
UTL_FILE operations are essential for generating reports, logging, and interfacing with external data files in a secure and controlled manner.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- DBA must create a directory object first:
-- CREATE OR REPLACE DIRECTORY out_dir AS '/oracle/data/output';
-- GRANT READ, WRITE ON DIRECTORY out_dir TO your_user;
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_line VARCHAR2(4000);
BEGIN
-- WRITE a file
v_file := UTL_FILE.FOPEN('OUT_DIR', 'employees.csv', 'W', 32767);
UTL_FILE.PUT_LINE(v_file, 'ID,Name,Salary');
FOR emp IN (SELECT employee_id, employee_name, salary FROM employees) LOOP
UTL_FILE.PUT_LINE(v_file, emp.employee_id || ',' || emp.employee_name || ',' || emp.salary);
END LOOP;
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('File written successfully');
-- READ the file back
v_file := UTL_FILE.FOPEN('OUT_DIR', 'employees.csv', 'R', 32767);
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
DBMS_OUTPUT.PUT_LINE('Line: ' || v_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(v_file);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The UTL_FILE package in PL/SQL enables reading from and writing to files on the server's file system.
Handling large objects (LOBs) in PL/SQL involves using BLOB, CLOB, NCLOB, and BFILE datatypes to store and manipulate large data sets such as text, images, and multimedia.
Oracle provides built-in functions and procedures in the DBMS_LOB package to work with LOBs efficiently.
Developers can read, write, and manage LOB data in chunks, optimizing performance and overcoming size limitations of traditional datatypes.
Efficient LOB handling is crucial for applications dealing with large volumes of multimedia data or documents.
DECLARE
v_clob CLOB;
v_chunk VARCHAR2(32767);
v_offset INTEGER := 1;
v_amount INTEGER := 32767;
v_blob BLOB;
BEGIN
-- Read CLOB in chunks
SELECT document_text INTO v_clob
FROM documents WHERE doc_id = 1;
DBMS_OUTPUT.PUT_LINE('CLOB length: ' || DBMS_LOB.GETLENGTH(v_clob));
LOOP
EXIT WHEN v_offset > DBMS_LOB.GETLENGTH(v_clob);
DBMS_LOB.READ(v_clob, v_amount, v_offset, v_chunk);
DBMS_OUTPUT.PUT_LINE('Chunk: ' || SUBSTR(v_chunk, 1, 80) || '...');
v_offset := v_offset + v_amount;
END LOOP;
-- Write/append to a CLOB
SELECT document_text INTO v_clob FROM documents WHERE doc_id = 1 FOR UPDATE;
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(' [REVIEWED]'), ' [REVIEWED]');
COMMIT;
-- Convert CLOB to BLOB
v_blob := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(v_clob, 4000, 1));
DBMS_OUTPUT.PUT_LINE('BLOB size: ' || DBMS_LOB.GETLENGTH(v_blob));
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Handling large objects (LOBs) in PL/SQL involves using BLOB, CLOB, NCLOB, and BFILE datatypes to store and manipulate large data sets such as text, images, and multimedia.
Handling large objects (LOBs) in PL/SQL efficiently involves using the DBMS_LOB package for operations such as reading, writing, and manipulating LOB data.
Streaming techniques are employed to process LOB data in chunks, minimizing memory consumption.
For performance optimization, LOBs are stored in separate tablespaces.
Proper use of LOB locators reduces the overhead of LOB manipulations.
These practices ensure efficient management of large objects in PL/SQL applications.
-- Efficient CLOB processing with DBMS_LOB
CREATE OR REPLACE PROCEDURE prc_process_document(p_doc_id NUMBER) IS
v_clob CLOB;
v_chunk VARCHAR2(32767);
v_offset INTEGER := 1;
v_amount INTEGER := 32767;
v_length INTEGER;
BEGIN
-- Lock and read LOB
SELECT document_text INTO v_clob
FROM documents WHERE doc_id = p_doc_id FOR UPDATE;
v_length := DBMS_LOB.GETLENGTH(v_clob);
DBMS_OUTPUT.PUT_LINE('Document size: ' || v_length || ' chars');
-- Process in chunks (avoids memory issues with huge LOBs)
WHILE v_offset <= v_length LOOP
DBMS_LOB.READ(v_clob, v_amount, v_offset, v_chunk);
-- process v_chunk here (e.g., search/replace, word count)
v_offset := v_offset + v_amount;
END LOOP;
-- Append to CLOB
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(' [PROCESSED]'), ' [PROCESSED]');
COMMIT;
-- Create a new temp CLOB
DECLARE
v_tmp CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_tmp, TRUE, DBMS_LOB.CALL);
DBMS_LOB.WRITE(v_tmp, 5, 1, 'Hello');
DBMS_OUTPUT.PUT_LINE('Temp CLOB: ' || DBMS_LOB.SUBSTR(v_tmp, 10, 1));
DBMS_LOB.FREETEMPORARY(v_tmp);
END;
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Handling large objects (LOBs) in PL/SQL efficiently involves using the DBMS_LOB package for operations such as reading, writing, and manipulating LOB data.
Implementing a PL/SQL solution for handling external files and directories involves using the UTL_FILE package.
This package provides capabilities to read from and write to files on the server's filesystem within PL/SQL programs.
Directories are securely managed through database directory objects.
Access permissions are carefully controlled to ensure security.
This functionality supports the integration of PL/SQL applications with external data and batch processes.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- DBA creates directory objects first:
-- CREATE OR REPLACE DIRECTORY in_dir AS '/oracle/data/input';
-- CREATE OR REPLACE DIRECTORY out_dir AS '/oracle/data/output';
-- GRANT READ ON DIRECTORY in_dir TO hr;
-- GRANT WRITE ON DIRECTORY out_dir TO hr;
CREATE OR REPLACE PROCEDURE prc_import_csv(p_filename VARCHAR2) IS
v_file UTL_FILE.FILE_TYPE;
v_line VARCHAR2(4000);
v_count NUMBER := 0;
v_emp_id NUMBER;
v_name VARCHAR2(100);
v_salary NUMBER;
BEGIN
v_file := UTL_FILE.FOPEN('IN_DIR', p_filename, 'R', 32767);
-- Skip header line
UTL_FILE.GET_LINE(v_file, v_line);
-- Process data rows
LOOP
BEGIN
UTL_FILE.GET_LINE(v_file, v_line);
v_emp_id := TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 1));
v_name := REGEXP_SUBSTR(v_line, '[^,]+', 1, 2);
v_salary := TO_NUMBER(REGEXP_SUBSTR(v_line, '[^,]+', 1, 3));
INSERT INTO employees(employee_id, employee_name, salary)
VALUES (v_emp_id, v_name, v_salary);
v_count := v_count + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT; -- end of file
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Skipping bad line: ' || v_line);
END;
END LOOP;
UTL_FILE.FCLOSE(v_file);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Imported: ' || v_count || ' records');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid directory - check DIRECTORY object');
WHEN UTL_FILE.NO_ACCESS THEN
DBMS_OUTPUT.PUT_LINE('Permission denied on directory');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated by stored procedure — 10% raise applied to dept 10
Implementing a PL/SQL solution for handling external files and directories involves using the UTL_FILE package.
UTL_FILE is an Oracle built-in package that allows PL/SQL code to read from and write to operating system files on the Oracle database server.
It is useful for generating reports, export files, log files, and data feeds from within PL/SQL.
The directory must be pre-created as an Oracle DIRECTORY object (not a plain path string) by a DBA, and the database user must have READ/WRITE privilege on that directory.
Key procedures include FOPEN, PUT_LINE, GET_LINE, FCLOSE, and FFLUSH.
-- Step 1: DBA creates directory (one-time)
-- CREATE OR REPLACE DIRECTORY EXPORT_DIR AS '/oracle/exports';
-- GRANT READ, WRITE ON DIRECTORY EXPORT_DIR TO HR;
-- Step 2: Write file from PL/SQL
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
-- Open file for writing
v_file := UTL_FILE.FOPEN('EXPORT_DIR', 'employees.csv', 'W');
-- Write header
UTL_FILE.PUT_LINE(v_file, 'EMP_ID,EMP_NAME,SALARY,DEPT');
-- Write data rows
FOR r IN (SELECT employee_id, employee_name, salary, department_id
FROM employees ORDER BY employee_id) LOOP
UTL_FILE.PUT_LINE(v_file,
r.employee_id || ',' || r.employee_name || ','
|| r.salary || ',' || r.department_id);
END LOOP;
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('File written: employees.csv');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid directory — check DIRECTORY object');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file); RAISE;
END;| OUTPUT / RESULT |
|---|
| File written: employees.csv |
| File location: /oracle/exports/employees.csv |
| Contents: EMP_ID,EMP_NAME,SALARY,DEPT |
| 101,Alice Johnson,50000,10 |
| 102,Bob Smith,62000,20 |
UTL_FILE is an Oracle built-in package that allows PL/SQL code to read from and write to operating system files on the Oracle database server.
Securing a PL/SQL application involves using roles and privileges to control access to data and functionalities.
Developers grant and revoke privileges on database objects to roles and then assign these roles to users.
This model ensures that users have only the necessary permissions to execute PL/SQL procedures, access tables, and perform specific operations, enhancing security and minimizing the risk of unauthorized data access.
Invoker rights run with the caller's privileges, not the owner's.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Create roles
CREATE ROLE plsql_reader;
CREATE ROLE plsql_writer;
-- Grant object privileges to roles
GRANT SELECT ON employees TO plsql_reader;
GRANT INSERT, UPDATE, DELETE ON employees TO plsql_writer;
GRANT EXECUTE ON pkg_emp TO plsql_writer;
-- Assign roles to users
GRANT plsql_reader TO app_readonly_user;
GRANT plsql_writer TO app_user;
-- Invoker rights: procedure runs with CALLER's privileges
CREATE OR REPLACE PROCEDURE prc_list_employees
AUTHID CURRENT_USER IS
BEGIN
FOR emp IN (SELECT employee_name FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(emp.employee_name);
END LOOP;
END;
/
-- Definer rights (default): procedure runs with OWNER's privileges
CREATE OR REPLACE PROCEDURE prc_secure_report
AUTHID DEFINER IS
BEGIN
-- Runs with the procedure owner's privileges regardless of caller
SELECT COUNT(*) FROM sensitive_table INTO NULL;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
Securing a PL/SQL application involves using roles and privileges to control access to data and functionalities.
Data encryption in PL/SQL is achieved using the DBMS_CRYPTO package, which provides interfaces to encrypt and decrypt data.
Developers use algorithms like AES and DES to secure sensitive data before storage or transmission.
Keys and encryption modes are specified to meet security requirements.
This encryption is vital for protecting data confidentiality in Oracle Database applications.
Proper key management and encryption practices ensure data is accessible only to authorized users.
DECLARE
v_key RAW(32); -- 256-bit AES key
v_plain RAW(4000);
v_encrypted RAW(4000);
v_decrypted RAW(4000);
c_algo PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
-- Generate a random encryption key
v_key := DBMS_CRYPTO.RANDOMBYTES(32);
-- Encrypt
v_plain := UTL_RAW.CAST_TO_RAW('Sensitive Data: SSN 123-45-6789');
v_encrypted := DBMS_CRYPTO.ENCRYPT(
src => v_plain,
typ => c_algo,
key => v_key
);
DBMS_OUTPUT.PUT_LINE('Encrypted (hex): ' || RAWTOHEX(v_encrypted));
-- Decrypt
v_decrypted := DBMS_CRYPTO.DECRYPT(
src => v_encrypted,
typ => c_algo,
key => v_key
);
DBMS_OUTPUT.PUT_LINE('Decrypted: ' || UTL_RAW.CAST_TO_VARCHAR2(v_decrypted));
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Data encryption in PL/SQL is achieved using the DBMS_CRYPTO package, which provides interfaces to encrypt and decrypt data.
Fine-grained access control in PL/SQL, implemented through the DBMS_RLS package, allows for dynamic, row-level security policies.
These policies enforce security, determining access rights based on user attributes or session context.
Developers use this feature to create security policies that apply to table or view rows.
This mechanism enhances data security by providing flexible, scalable control over who can access data.
Fine-grained access control is essential in multi-user environments where data access needs vary.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Step 1: Create the security policy function
CREATE OR REPLACE FUNCTION fnc_emp_security_policy(
schema_name IN VARCHAR2,
table_name IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
-- Users can only see their own department's employees
IF SYS_CONTEXT('USERENV','SESSION_USER') = 'HR_ADMIN' THEN
RETURN NULL; -- no restriction for HR admin
ELSE
RETURN 'department_id = SYS_CONTEXT(''CTX_APP'',''DEPT_ID'')';
END IF;
END;
/
-- Step 2: Register the policy (VPD - Virtual Private Database)
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_DEPT_POLICY',
function_schema=> 'HR',
policy_function=> 'FNC_EMP_SECURITY_POLICY',
statement_types=> 'SELECT, INSERT, UPDATE, DELETE'
);
END;
/
-- Now every SELECT on EMPLOYEES automatically adds the WHERE clause
SELECT * FROM employees;
-- Becomes: SELECT * FROM employees WHERE department_id = SYS_CONTEXT('CTX_APP','DEPT_ID')| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Fine-grained access control in PL/SQL, implemented through the DBMS_RLS package, allows for dynamic, row-level security policies.
Securing PL/SQL code against SQL injection attacks involves using bind variables to separate SQL code from data, thereby preventing attackers from injecting malicious SQL.
Developers also use the DBMS_ASSERT package to sanitize user inputs and validate dynamic SQL constructs.
Stored procedures and functions encapsulate SQL logic, minimizing direct SQL execution from user inputs.
These practices protect PL/SQL applications from SQL injection, ensuring data integrity and application security.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- VULNERABLE: string concatenation allows SQL injection
-- NEVER do this:
CREATE OR REPLACE PROCEDURE prc_bad_search(p_name VARCHAR2) IS
v_count NUMBER;
BEGIN
-- Attacker can pass: ' OR '1'='1 to return all rows
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM employees WHERE name = ''' || p_name || ''''
INTO v_count;
END;
/
-- SECURE: always use bind variables
CREATE OR REPLACE PROCEDURE prc_safe_search(p_name VARCHAR2) IS
v_count NUMBER;
BEGIN
-- Bind variable: user input treated as DATA, never as SQL
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM employees WHERE UPPER(employee_name) = UPPER(:1)'
INTO v_count USING p_name;
DBMS_OUTPUT.PUT_LINE('Found: ' || v_count);
END;
/
-- Static SQL is safest (no dynamic SQL at all)
CREATE OR REPLACE PROCEDURE prc_static_search(p_dept_id NUMBER) IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_dept_id;
DBMS_OUTPUT.PUT_LINE('Dept ' || p_dept_id || ' has ' || v_count || ' employees');
END;
/
-- Also validate and sanitize all inputs
CREATE OR REPLACE FUNCTION fnc_sanitize(p_input VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_REPLACE(p_input, '[^A-Za-z0-9 _-]', '');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| APPROACH | INPUT | SAFE | RESULT |
|---|---|---|---|
| String concat | ' OR '1'='1 | No | Returns ALL 4 rows — security breach |
| Bind variable :p1 | ' OR '1'='1 | Yes | 0 rows — treated as literal data |
Bind variables prevent SQL injection — the #1 Oracle security best practice
Securing PL/SQL code against SQL injection attacks involves using bind variables to separate SQL code from data, thereby preventing attackers from injecting malicious SQL.
Optimizing PL/SQL code for performance involves several techniques, such as using bulk SQL operations instead of row-by-row processing, minimizing context switches between SQL and PL/SQL, using collections and bulk binding, optimizing loop constructs, and avoiding unnecessary computations within loops.
Proper indexing and understanding the cost of different PL/SQL operations also contribute to better performance.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Bad: row-by-row (slow, many context switches)
BEGIN
FOR emp IN (SELECT employee_id, salary FROM employees WHERE department_id = 10) LOOP
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp.employee_id;
END LOOP;
COMMIT;
END;
/
-- Good: single SQL (no context switches)
UPDATE employees SET salary = salary * 1.05
WHERE department_id = 10;
COMMIT;
-- Better: BULK COLLECT + FORALL (best for complex logic)
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
TYPE t_sals IS TABLE OF NUMBER;
v_ids t_ids;
v_sals t_sals;
BEGIN
SELECT employee_id, salary * 1.05
BULK COLLECT INTO v_ids, v_sals
FROM employees WHERE department_id = 10;
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET salary = v_sals(i)
WHERE employee_id = v_ids(i);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
Optimizing PL/SQL code for performance involves several techniques, such as using bulk SQL operations instead of row-by-row processing, minimizing context switches between SQL and PL/SQL, using collections and bulk binding, optimizing loop constructs, and avoiding unnecessary computations within loops.
The profiler in PL/SQL, typically accessed through the DBMS_PROFILER package, analyzes code performance by collecting execution statistics.
Developers use these statistics to identify bottlenecks and optimize PL/SQL code.
The profiler provides detailed information on execution counts, elapsed times, and CPU times for each line of code.
This tool is crucial for tuning PL/SQL applications, ensuring optimal performance.
Regular profiling helps maintain efficient code as applications evolve.
-- Step 1: Install profiler tables (run once as target user)
-- @$ORACLE_HOME/rdbms/admin/proftab.sql
-- Step 2: Profile a specific code block
DECLARE
v_run_id BINARY_INTEGER;
BEGIN
DBMS_PROFILER.START_PROFILER(
run_comment => 'Order processing test run',
run_number => v_run_id
);
-- Code to profile
FOR i IN 1..1000 LOOP
prc_process_order(i);
END LOOP;
DBMS_PROFILER.STOP_PROFILER;
DBMS_OUTPUT.PUT_LINE('Profile run ID: ' || v_run_id);
END;
/
-- Step 3: Analyse results
SELECT u.unit_name,
d.line#,
d.total_time / 1000000 AS ms,
d.total_occur AS calls,
s.text
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON u.runid = d.runid AND u.unit_number = d.unit_number
JOIN all_source s ON s.name = u.unit_name AND s.line = d.line#
WHERE d.runid = &run_id
AND d.total_time > 0
ORDER BY d.total_time DESC
FETCH FIRST 20 ROWS ONLY;| EMP_NAME | DEPT_NAME |
|---|---|
| Alice Johnson | Engineering |
| Bob Smith | Marketing |
| Carol White | Engineering |
3 rows — only rows with matching dept_id in both tables (Dave Brown excluded — no dept)
The profiler in PL/SQL, typically accessed through the DBMS_PROFILER package, analyzes code performance by collecting execution statistics.
Profiling PL/SQL code to identify performance bottlenecks involves the use of the DBMS_PROFILER package.
This tool collects detailed runtime execution statistics for PL/SQL code blocks.
By analyzing the profiler output, developers identify slow-running sections of code and optimize them.
Profiling is a critical step in the performance-tuning process.
It ensures PL/SQL applications run with optimal efficiency.
-- DBMS_PROFILER: line-by-line timing
DECLARE
v_run_id BINARY_INTEGER;
BEGIN
DBMS_PROFILER.START_PROFILER('Performance test', run_number => v_run_id);
-- Code under test
FOR i IN 1..1000 LOOP
prc_process_order(i);
END LOOP;
DBMS_PROFILER.FLUSH_DATA;
DBMS_PROFILER.STOP_PROFILER;
DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_run_id);
END;
/
-- Analyse top 10 slowest lines
SELECT u.unit_name,
d.line#,
d.total_time / 1e6 AS total_ms,
d.total_occur AS calls,
ROUND(d.total_time / d.total_occur / 1000) AS avg_us_per_call,
s.text
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON u.runid = d.runid AND u.unit_number = d.unit_number
JOIN all_source s ON s.name = u.unit_name
AND s.line = d.line#
AND s.type = u.unit_type
WHERE d.runid = &run_id AND d.total_time > 0
ORDER BY d.total_time DESC
FETCH FIRST 10 ROWS ONLY;
-- DBMS_HPROF: hierarchical profiler (Oracle 11g+, more detail)
-- DBMS_HPROF.START_PROFILING('PROF_DIR', 'hprof_out.trc');| EMP_NAME | DEPT_NAME |
|---|---|
| Alice Johnson | Engineering |
| Bob Smith | Marketing |
| Carol White | Engineering |
3 rows — only rows with matching dept_id in both tables (Dave Brown excluded — no dept)
Profiling PL/SQL code to identify performance bottlenecks involves the use of the DBMS_PROFILER package.
Managing and optimizing session-level settings for PL/SQL procedures involves the strategic use of the ALTER SESSION command.
Developers adjust session parameters to fine-tune the execution environment for their procedures.
Parameters such as optimizer modes, NLS settings, and memory allocation are optimized.
These optimizations are crucial for enhancing the performance and behavior of PL/SQL procedures.
Session-level settings customization ensures PL/SQL procedures execute efficiently in various environments.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
CREATE OR REPLACE PROCEDURE prc_optimised_batch(p_dept_id NUMBER) IS
v_orig_optimizer VARCHAR2(100);
v_orig_hash_area NUMBER;
BEGIN
-- Save original session settings
v_orig_optimizer := SYS_CONTEXT('USERENV','OPTIMIZER_MODE');
-- Tune session for batch processing
EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS';
EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE = 104857600'; -- 100MB
EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE = 52428800'; -- 50MB
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD''';
-- Bulk operation benefits from tuned session
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
v_ids t_ids;
BEGIN
SELECT employee_id BULK COLLECT INTO v_ids
FROM employees WHERE department_id = p_dept_id;
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET last_reviewed = SYSDATE WHERE employee_id = v_ids(i);
COMMIT;
END;
-- Restore original settings
EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_MODE = ' || v_orig_optimizer;
DBMS_OUTPUT.PUT_LINE('Batch complete for dept ' || p_dept_id);
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
Managing and optimizing session-level settings for PL/SQL procedures involves the strategic use of the ALTER SESSION command.
Fine-tuning the performance of PL/SQL blocks involves code profiling, using bulk operations, optimizing loop processing, and proper exception handling.
The DBMS_PROFILER and DBMS_HPROF tools identify performance bottlenecks.
Bulk operations with FORALL and BULK COLLECT reduce context switching between SQL and PL/SQL.
Efficient use of PL/SQL collections minimizes memory usage and execution time.
These techniques ensure optimal performance of PL/SQL blocks.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Tip 1: PLS_INTEGER (faster than NUMBER for integer arithmetic)
DECLARE
v_total PLS_INTEGER := 0;
BEGIN
FOR i IN 1..1000000 LOOP
v_total := v_total + i; -- PLS_INTEGER is much faster than NUMBER here
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END;
/
-- Tip 2: Cache lookup data (avoid repeated SQL calls)
DECLARE
TYPE t_dept_cache IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_cache t_dept_cache;
v_dept VARCHAR2(50);
BEGIN
-- Load once
FOR rec IN (SELECT department_id, department_name FROM departments) LOOP
v_cache(rec.department_id) := rec.department_name;
END LOOP;
-- Use cache (no SQL needed in loop)
FOR emp IN (SELECT department_id FROM employees WHERE ROWNUM <= 100) LOOP
v_dept := v_cache(emp.department_id);
END LOOP;
END;
/
-- Tip 3: Avoid implicit conversions (always convert explicitly)
DECLARE
v_str VARCHAR2(20) := '12345';
v_num NUMBER;
BEGIN
v_num := TO_NUMBER(v_str); -- explicit: fast
-- v_num := v_str; -- implicit: slower, risky
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | SALARY |
|---|---|---|
| 101 | Alice Johnson | 50,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
Top 3 rows — ROWNUM/FETCH FIRST limits result set
Fine-tuning the performance of PL/SQL blocks involves code profiling, using bulk operations, optimizing loop processing, and proper exception handling.
Tracing PL/SQL code, a technique for runtime performance evaluation, can be accomplished using methods like DBMS_APPLICATION_INFO, DBMS_TRACE, DBMS_SESSION, DBMS_MONITOR, and utilities like trcsess and tkprof.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Tracing PL/SQL code, a technique for runtime performance evaluation, can be accomplished using methods like DBMS_APPLICATION_INFO, DBMS_TRACE, DBMS_SESSION, DBMS_MONITOR, and utilities like trcsess and tkprof.
The PL/SQL Hierarchical Profiler (DBMS_HPROF) analyses the execution time of PL/SQL programs at the subprogram (function/procedure) level, showing how long each call took, how many times it was called, and in what calling hierarchy.
Unlike DBMS_PROFILER (line-level), DBMS_HPROF gives a function-call-tree view making it easier to identify expensive bottlenecks.
The output is stored in database tables and can be analysed with the supplied PLSHPROF utility or Oracle SQL Developer.
-- Step 1: Grant and create tables (DBA does once)
-- GRANT EXECUTE ON DBMS_HPROF TO HR;
-- @$ORACLE_HOME/rdbms/admin/dbmshptab.sql
-- Step 2: Start profiling
BEGIN
DBMS_HPROF.START_PROFILING(
location => 'PROFILE_DIR', -- Oracle DIRECTORY object
filename => 'my_profile.trc'
);
END;
/
-- Step 3: Run the code to profile
BEGIN
prc_generate_monthly_report(p_month => '2026-04');
END;
/
-- Step 4: Stop profiling
BEGIN
DBMS_HPROF.STOP_PROFILING;
END;
/
-- Step 5: Analyse (load into tables)
SELECT DBMS_HPROF.ANALYZE(
location => 'PROFILE_DIR',
filename => 'my_profile.trc'
) AS run_id FROM DUAL;
-- Step 6: Query results
SELECT owner, module, function, calls, elapsed_time
FROM dbmshp_function_info WHERE runid = 1
ORDER BY elapsed_time DESC;| OUTPUT / RESULT |
|---|
| FUNCTION CALLS ELAPSED_TIME |
| prc_generate_monthly_report 1 8,432,100 µs |
| fn_calc_bonuses 1200 3,201,400 µs |
| pkg_report.prc_format_row 12000 2,100,000 µs |
| fn_calc_bonuses: bottleneck — 38% of total time |
The PL/SQL Hierarchical Profiler (DBMS_HPROF) analyses the execution time of PL/SQL programs at the subprogram (function/procedure) level, showing how long each call took, how many times it was called, and in what calling hierarchy.
Sequences in PL/SQL are database objects used to generate unique numbers.
They are commonly used for creating primary key values.
Sequences are created using the CREATE SEQUENCE statement, specifying options such as the starting value, minimum value, maximum value, increment, and whether the sequence should cycle.
Once created, the NEXTVAL and CURRVAL pseudo columns are used to access the sequence values.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Create a sequence
CREATE SEQUENCE seq_emp_id
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 9999999
NOCYCLE
CACHE 20;
-- Use NEXTVAL on INSERT
INSERT INTO employees(employee_id, employee_name, salary)
VALUES (seq_emp_id.NEXTVAL, 'Jane Doe', 60000);
-- Use CURRVAL to retrieve last-used value
DECLARE
v_new_id NUMBER;
BEGIN
v_new_id := seq_emp_id.NEXTVAL;
DBMS_OUTPUT.PUT_LINE('New employee ID: ' || v_new_id);
DBMS_OUTPUT.PUT_LINE('Current val : ' || seq_emp_id.CURRVAL);
END;
/
-- Query sequence metadata
SELECT sequence_name, last_number, increment_by, cache_size
FROM user_sequences
WHERE sequence_name = 'SEQ_EMP_ID';| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Sequences in PL/SQL are database objects used to generate unique numbers.
A materialized view is a database object that stores the result of a query.
Oracle Database updates materialized views on demand or at regular intervals, utilizing the refresh method specified.
Developers use the DBMS_MVIEW package to manually refresh materialized views if necessary.
This feature improves query performance by storing complex query results and accessing them quickly.
Materialized views are suitable for data warehousing applications where query performance is crucial.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Create materialized view log (needed for FAST refresh)
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (employee_id, salary, department_id)
INCLUDING NEW VALUES;
-- Create the materialized view
CREATE MATERIALIZED VIEW mv_dept_salary_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT -- auto-refresh on commit
ENABLE QUERY REWRITE
AS
SELECT department_id,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Manual refresh options
BEGIN
-- Refresh a single MV
DBMS_MVIEW.REFRESH('MV_DEPT_SALARY_SUMMARY', 'F'); -- F = FAST
-- Refresh completely
DBMS_MVIEW.REFRESH('MV_DEPT_SALARY_SUMMARY', 'C'); -- C = COMPLETE
-- Refresh all MVs in dependency order
DBMS_MVIEW.REFRESH_ALL_MVIEWS(0);
END;
/
-- Query the MV (much faster than querying base table)
SELECT * FROM mv_dept_salary_summary ORDER BY total_salary DESC;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| DEPT_ID | EMP_COUNT | AVG_SALARY | MAX_SALARY |
|---|---|---|---|
| 10 | 2 | 52,500 | 55,000 |
| 20 | 2 | 66,000 | 70,000 |
2 groups — Alice+Carol in dept 10, Bob+Dave in dept 20
A materialized view is a database object that stores the result of a query.
Global temporary tables are database tables that hold temporary data on a session or transaction basis.
Data inserted into a global temporary table is visible only to the session that inserted the data.
Oracle Database automatically deletes rows at the end of a transaction or session, based on the table's definition.
These tables are optimal for storing intermediate results of complex calculations.
Global temporary tables do not affect database recovery, and their content is not logged.
Each session sees only its own data.
-- GTT data visible only to the creating session
-- ON COMMIT DELETE ROWS: data gone after each COMMIT
CREATE GLOBAL TEMPORARY TABLE gtt_session_cart (
item_id NUMBER,
item_name VARCHAR2(100),
quantity NUMBER,
price NUMBER
) ON COMMIT DELETE ROWS;
-- ON COMMIT PRESERVE ROWS: data lasts for the whole session
CREATE GLOBAL TEMPORARY TABLE gtt_session_log (
log_id NUMBER,
log_msg VARCHAR2(500),
logged_at DATE
) ON COMMIT PRESERVE ROWS;
-- Usage
DECLARE
v_total NUMBER;
BEGIN
INSERT INTO gtt_session_cart VALUES (1, 'Laptop', 2, 1200);
INSERT INTO gtt_session_cart VALUES (2, 'Mouse', 1, 25);
SELECT SUM(quantity * price) INTO v_total FROM gtt_session_cart;
DBMS_OUTPUT.PUT_LINE('Cart total: $' || v_total);
COMMIT; -- rows deleted (ON COMMIT DELETE ROWS)
SELECT COUNT(*) INTO v_total FROM gtt_session_cart;
DBMS_OUTPUT.PUT_LINE('After commit: ' || v_total || ' rows'); -- 0
END;
/| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
Global temporary tables are database tables that hold temporary data on a session or transaction basis.
Table functions in PL/SQL return a set of rows that can be queried as a regular table.
Developers define table functions by returning a collection type, such as a nested table or a varray.
Users can then select from the table function as any other table or view in SQL.
Table functions are beneficial for pipelining data, transforming data sets, and performing complex processing within SQL queries.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Define return type
CREATE OR REPLACE TYPE t_emp_row AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);
/
CREATE OR REPLACE TYPE t_emp_tab AS TABLE OF t_emp_row;
/
-- Table function returns rows like a table
CREATE OR REPLACE FUNCTION fnc_high_earners(p_min_sal NUMBER)
RETURN t_emp_tab IS
v_result t_emp_tab := t_emp_tab();
BEGIN
FOR rec IN (SELECT employee_id, employee_name, salary
FROM employees WHERE salary >= p_min_sal) LOOP
v_result.EXTEND;
v_result(v_result.LAST) := t_emp_row(rec.employee_id, rec.employee_name, rec.salary);
END LOOP;
RETURN v_result;
END;
/
-- Query the table function like a regular table
SELECT * FROM TABLE(fnc_high_earners(70000));| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 101 | Alice Johnson | 50,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
| 104 | Dave Brown | 70,000 |
All 4 rows returned
Table functions in PL/SQL return a set of rows that can be queried as a regular table.
The WITH clause in PL/SQL simplifies complex SQL queries by allowing the definition of subqueries that can be reused within the main query.
This technique, known as subquery factoring or common table expressions, enhances readability and performance.
Developers define the subquery once using the WITH clause and reference it multiple times in the main query.
The WITH clause is particularly useful for breaking down complicated queries into simpler, modular components.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- WITH clause (CTE) inside PL/SQL
DECLARE
v_top_dept NUMBER;
v_avg_sal NUMBER;
BEGIN
SELECT dept_id, dept_avg
INTO v_top_dept, v_avg_sal
FROM (
WITH dept_stats AS (
SELECT department_id AS dept_id,
AVG(salary) AS dept_avg,
COUNT(*) AS headcount
FROM employees
GROUP BY department_id
),
ranked AS (
SELECT dept_id, dept_avg,
RANK() OVER (ORDER BY dept_avg DESC) AS rnk
FROM dept_stats
WHERE headcount >= 3
)
SELECT dept_id, dept_avg FROM ranked WHERE rnk = 1
);
DBMS_OUTPUT.PUT_LINE('Top dept: ' || v_top_dept || ', Avg salary: $' || ROUND(v_avg_sal));
END;
/
-- Standalone SQL with WITH
WITH high_earners AS (
SELECT employee_id, employee_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM high_earners WHERE rnk = 1;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_NAME | SALARY |
|---|---|
| Bob Smith | 62,000 |
| Carol White | 55,000 |
| Dave Brown | 70,000 |
3 rows — salary > average (59,250). Alice Johnson (50,000) excluded — equals avg, not greater
The WITH clause in PL/SQL simplifies complex SQL queries by allowing the definition of subqueries that can be reused within the main query.
The MERGE statement in PL/SQL combines insert, update, and delete operations into a single statement, based on the matching conditions specified.
This statement is used to synchronize two tables by inserting or updating rows in one table based on the rows from another.
The MERGE statement is ideal for data warehousing applications where large volumes of data require efficient synchronization.
Oracle Database optimizes MERGE operations for performance, reducing the need for multiple DML statements.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- MERGE: upsert (insert if not exists, update if exists)
MERGE INTO employees tgt
USING staging_employees src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET
tgt.salary = src.salary,
tgt.department_id = src.department_id,
tgt.updated_at = SYSDATE
WHERE tgt.salary <> src.salary -- only update if actually changed
WHEN NOT MATCHED THEN
INSERT (employee_id, employee_name, salary, department_id, hire_date)
VALUES (src.employee_id, src.employee_name, src.salary, src.department_id, SYSDATE)
-- DELETE clause (optional): remove target rows not in source
-- WHEN NOT MATCHED BY SOURCE THEN DELETE (Oracle 12c+)
;
DBMS_OUTPUT.PUT_LINE('Merge complete. Rows: ' || SQL%ROWCOUNT);
COMMIT;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | ACTION | RESULT |
|---|---|---|---|
| 101 | Alice Johnson | UPDATED (matched) | Salary 55,000 |
| 102 | Bob Smith | UPDATED (matched) | Salary 68,200 |
| 999 | New Hire | INSERTED (not matched) | New row added |
MERGE: 2 rows updated + 1 inserted in one atomic statement
The MERGE statement in PL/SQL combines insert, update, and delete operations into a single statement, based on the matching conditions specified.
Pipelined table functions allow PL/SQL functions to return rows iteratively, behaving like a table that can be queried.
These functions enhance data processing by allowing rows to be processed and returned as soon as they are available, rather than waiting for the entire result set.
Pipelined table functions are used in data transformation and loading processes, where they streamline complex data processing tasks.
Oracle Database executes these functions efficiently, enabling real-time data streaming in PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Step 1: Define return types
CREATE OR REPLACE TYPE t_emp_row AS OBJECT (
emp_id NUMBER, emp_name VARCHAR2(100), salary NUMBER
);
/
CREATE OR REPLACE TYPE t_emp_pipe_tab AS TABLE OF t_emp_row;
/
-- Step 2: Pipelined function (streams rows without buffering all in memory)
CREATE OR REPLACE FUNCTION fnc_dept_employees(p_dept_id NUMBER)
RETURN t_emp_pipe_tab PIPELINED IS
BEGIN
FOR emp IN (
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = p_dept_id
ORDER BY salary DESC
) LOOP
-- PIPE ROW returns one row immediately (no memory accumulation)
PIPE ROW(t_emp_row(emp.employee_id, emp.employee_name, emp.salary));
END LOOP;
RETURN; -- mandatory for pipelined functions
END;
/
-- Query: used just like a table
SELECT * FROM TABLE(fnc_dept_employees(10));
-- Can join with other tables
SELECT p.emp_name, p.salary, d.department_name
FROM TABLE(fnc_dept_employees(10)) p
JOIN departments d ON d.department_id = 10;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_NAME | DEPT_NAME |
|---|---|
| Alice Johnson | Engineering |
| Bob Smith | Marketing |
| Carol White | Engineering |
3 rows — only rows with matching dept_id in both tables (Dave Brown excluded — no dept)
Pipelined table functions allow PL/SQL functions to return rows iteratively, behaving like a table that can be queried.
Simple subqueries are single-level queries used within another SQL statement.
Nested subqueries are subqueries inside another subquery, allowing for multiple levels of querying.
Correlated subqueries reference column values from the outer query, executing once for each row selected by the outer query.
These differences in structure and execution impact the use case and performance of each subquery type.
Understanding these types aids in writing precise and efficient SQL queries.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- 1. Simple subquery (executes once)
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 2. Nested subquery (subquery inside subquery)
SELECT employee_name, salary, department_id
FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id IN (
SELECT location_id FROM locations WHERE country_id = 'US'
)
);
-- 3. Correlated subquery (references outer query, executes per row)
SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) -- uses e.department_id from outer query
FROM employees
WHERE department_id = e.department_id -- correlation
);
-- Same result with analytic function (often faster):
SELECT employee_name, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_NAME | SALARY | DEPT_AVG |
|---|---|---|
| Dave Brown | 70,000 | 66,000 |
| Bob Smith | 62,000 | 66,000 |
2 rows — employees earning above their own department average
Simple subqueries are single-level queries used within another SQL statement.
The MERGE command in SQL is used to combine insert, update, and delete operations into a single statement.
This command is particularly useful for synchronizing two tables by inserting, updating, or deleting records in one table based on differences found in another table.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
MERGE INTO employees tgt
USING staging_emp src ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET tgt.salary = src.salary, tgt.dept_id = src.dept_id
WHEN NOT MATCHED THEN
INSERT (employee_id, employee_name, salary)
VALUES (src.employee_id, src.employee_name, src.salary);
COMMIT;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | ACTION | RESULT |
|---|---|---|---|
| 101 | Alice Johnson | UPDATED (matched) | Salary 55,000 |
| 102 | Bob Smith | UPDATED (matched) | Salary 68,200 |
| 999 | New Hire | INSERTED (not matched) | New row added |
MERGE: 2 rows updated + 1 inserted in one atomic statement
The MERGE command in SQL is used to combine insert, update, and delete operations into a single statement.
MERGE (also called UPSERT) combines INSERT and UPDATE in a single atomic statement.
It compares a source data set against a target table using a ON condition.
When a match is found it can UPDATE the target row.
When no match is found it can INSERT a new row.
Optionally, a WHEN NOT MATCHED BY SOURCE clause can DELETE rows from the target that have no corresponding source row.
MERGE is ideal for ETL processes, data synchronisation, and maintaining summary/aggregate tables.
-- Merge new hire data into employees table
MERGE INTO employees tgt
USING (
SELECT 205 AS emp_id, 'Emma Wilson' AS emp_name,
10 AS dept_id, 55000 AS salary FROM DUAL
UNION ALL
SELECT 101, 'Alice Johnson Updated', 10, 60000 FROM DUAL
) src
ON (tgt.employee_id = src.emp_id)
WHEN MATCHED THEN
UPDATE SET tgt.employee_name = src.emp_name,
tgt.salary = src.salary,
tgt.updated_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT (employee_id, employee_name, department_id, salary, hire_date)
VALUES (src.emp_id, src.emp_name, src.dept_id, src.salary, SYSDATE);
COMMIT;
SELECT employee_id, employee_name, salary FROM employees
WHERE employee_id IN (101, 205);| OUTPUT / RESULT |
|---|
| EMPLOYEE_ID EMPLOYEE_NAME SALARY |
| 101 Alice Johnson Updated 60,000 UPDATED |
| 205 Emma Wilson 55,000 INSERTED |
| 2 rows merged — 1 updated, 1 inserted |
MERGE (also called UPSERT) combines INSERT and UPDATE in a single atomic statement.
SQL statements are categorised by what they do.
DML (Data Manipulation Language) manipulates existing data: SELECT, INSERT, UPDATE, DELETE, MERGE.
DML is transactional — changes can be committed or rolled back.
DDL (Data Definition Language) defines and modifies database structure: CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT.
DDL in Oracle issues an implicit COMMIT before and after execution — it cannot be rolled back.
TCL (Transaction Control Language) manages transactions: COMMIT (save), ROLLBACK (undo), SAVEPOINT (partial rollback point).
-- DML: transactional, can be rolled back
INSERT INTO employees(employee_id, employee_name, salary)
VALUES(206, 'Test User', 45000);
UPDATE employees SET salary = 50000 WHERE employee_id = 206;
DELETE FROM employees WHERE employee_id = 206;
-- TCL: control transactions
SAVEPOINT before_update;
UPDATE employees SET salary = 99999 WHERE department_id = 10;
ROLLBACK TO before_update; -- undo the update only
COMMIT; -- commit everything before savepoint
-- DDL: auto-commits, cannot be rolled back
CREATE TABLE temp_data(id NUMBER, val VARCHAR2(100));
ALTER TABLE temp_data ADD active_flag CHAR(1) DEFAULT 'Y';
DROP TABLE temp_data; -- implicit COMMIT, permanent| OUTPUT / RESULT |
|---|
| DML INSERT: pending — not yet committed |
| ROLLBACK TO before_update: UPDATE undone |
| COMMIT: all changes before savepoint permanently saved |
| DDL DROP TABLE: implicit COMMIT — cannot be rolled back |
SQL statements are categorised by what they do.
The TABLE() function converts a PL/SQL collection (nested table or VARRAY) into a relational result set that can be used in SQL queries — effectively treating the collection like a table.
This allows you to JOIN a PL/SQL collection with real tables, use it in WHERE IN clauses, and use it as a FROM clause source.
The collection type must be defined at the schema level (CREATE TYPE), not just inside a PL/SQL block, for TABLE() to work in SQL context.
-- Step 1: Create schema-level type
CREATE OR REPLACE TYPE t_number_list AS TABLE OF NUMBER;
/
CREATE OR REPLACE TYPE t_emp_row AS OBJECT(
emp_id NUMBER, emp_name VARCHAR2(100), salary NUMBER
);
/
CREATE OR REPLACE TYPE t_emp_list AS TABLE OF t_emp_row;
/
-- Step 2: Use TABLE() in SQL
DECLARE
v_ids t_number_list := t_number_list(101, 103);
BEGIN
-- Query employees whose IDs are in the collection
FOR r IN (
SELECT e.employee_name, e.salary
FROM employees e
JOIN TABLE(v_ids) t ON e.employee_id = t.COLUMN_VALUE
) LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_name || ': ' || r.salary);
END LOOP;
END;
/
-- TABLE() in SELECT (pipelined function result)
SELECT * FROM TABLE(pkg_report.fn_get_dept_data(10));| OUTPUT / RESULT |
|---|
| Alice Johnson: 50,000 |
| Carol White: 55,000 |
| TABLE(): converts collection to virtual table in SQL |
| JOIN TABLE(v_ids): filter employees by collection of IDs |
The TABLE() function converts a PL/SQL collection (nested table or VARRAY) into a relational result set that can be used in SQL queries — effectively treating the collection like a table.
PLV msg in PL/SQL enables assigning individual text messages to specific rows in a PL/SQL table, automatic message replacement for common Oracle errors, retrieval of text messages by integer, and batch loading of texts and message integers from a database table.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PLV msg in PL/SQL enables assigning individual text messages to specific rows in a PL/SQL table, automatic message replacement for common Oracle errors, retrieval of text messages by integer, and batch loading of texts and message integers from a database table.
In PL/SQL, PLVcmt and PLVrb are part of the PL/Vision suite.
PLVcmt provides a framework for managing commit operations, encapsulating logic related to commit processing.
PLVrb offers a programmatic interface for managing roll-back activities, enhancing control over transactional operations in PL/SQL.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
In PL/SQL, PLVcmt and PLVrb are part of the PL/Vision suite.
PLVprs and PLVprsps are part of the PL/Vision suite in PL/SQL.
PLVprs extends the string parsing functionality, providing low-level string parsing capabilities.
PLVprsps, on the other hand, is a high-level package used for parsing PL/SQL source code into separate components, relying on other parsing packages to facilitate this process.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
PLVprs and PLVprsps are part of the PL/Vision suite in PL/SQL.
In PL/SQL, a discard file is a specialized file that captures records that are not successfully loaded into database tables.
Identified by the '.dsc' extension, this file is particularly crucial for error handling and data validation processes, providing insights into data that could not be integrated into the database due to various constraints or format issues.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
-- Discard file: created by SQL*Loader
-- Records rejected by selection criteria go here
-- (different from bad file which has format errors)
-- SQL*Loader control file example:
-- LOAD DATA
-- INFILE 'employees.csv'
-- DISCARDFILE 'rejected.dsc'
-- APPEND INTO TABLE employees
-- WHEN dept_id != '30' -- records with dept 30 go to discard
-- FIELDS TERMINATED BY ','
-- (employee_id, employee_name, salary, dept_id)| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Marketing |
| 30 | Finance |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
In PL/SQL, a discard file is a specialized file that captures records that are not successfully loaded into database tables.
ORA-03113 is 'end-of-file on communication channel'.
It means the connection between the client (SQL*Plus, SQL Developer, application) and the Oracle database server was lost unexpectedly during a session.
Common causes include: database server crash, network failure, Oracle listener timeout, idle session timeout policy, or the DBA killing the session.
The client receives this error when it tries to send the next request after the connection dropped.
-- ORA-03113: end-of-file on communication channel
-- Causes:
-- 1. Database server crashed or restarted
-- 2. Network failure / firewall timeout
-- 3. Session killed by DBA: ALTER SYSTEM KILL SESSION '123,456';
-- 4. Idle session timeout in sqlnet.ora:
-- SQLNET.EXPIRE_TIME = 10 (minutes)
-- Check in alert log:
-- $ORACLE_BASE/diag/rdbms/<db>/<SID>/trace/alert_<SID>.log
-- Check active sessions
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE username IS NOT NULL
ORDER BY last_call_et DESC;| OUTPUT / RESULT |
|---|
| ORA-03113: end-of-file on communication channel |
| Check alert log for ORA-07445 or ORA-00600 (crash) |
| Check network: ping db_host |
| Check DBA_AUDIT_TRAIL for killed sessions |
ORA-03113 is 'end-of-file on communication channel'.
SGA (System Global Area) is shared memory allocated when the Oracle instance starts, shared by all sessions and processes.
It contains the Buffer Cache (data blocks), Shared Pool (library cache for parsed SQL/PL/SQL, data dictionary cache), Redo Log Buffer, and Large/Java/Streams Pools.
PGA (Program Global Area) is private memory allocated for each individual server process/session.
It contains the session's sort area, hash area, cursor state, and session variables.
PGA is not shared — each session has its own PGA.
When a session ends, its PGA is freed.
-- Check SGA size and components
SELECT name, ROUND(bytes/1024/1024, 1) AS size_mb
FROM v$sgainfo
ORDER BY bytes DESC;
-- Check PGA usage per session
SELECT s.sid, s.username, s.program,
ROUND(p.pga_alloc_mem/1024/1024, 2) AS pga_mb,
ROUND(p.pga_max_mem/1024/1024, 2) AS pga_max_mb
FROM v$session s JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
ORDER BY pga_alloc_mem DESC;
-- PGA target (auto-managed)
SELECT name, value FROM v$parameter
WHERE name IN ('pga_aggregate_target','sga_target','memory_target');| OUTPUT / RESULT |
|---|
| SGA: Buffer Cache 800MB, Shared Pool 400MB, Redo Buffer 64MB |
| PGA per session: THARUN.K 12.5 MB current, 18.0 MB peak |
| SGA: shared by all — survives session end |
| PGA: private per session — freed when session disconnects |
SGA (System Global Area) is shared memory allocated when the Oracle instance starts, shared by all sessions and processes.
The SPOOL command in SQL is used to direct the output of SQL queries to a file.
This is particularly useful for saving the results of a query for later review or for exporting data.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The SPOOL command in SQL is used to direct the output of SQL queries to a file.
The VERIFY command in SQL is used to control whether or not to display the substitution variables before and after SQL*Plus replaces them with values.
It can be turned on or off using the SET VERIFY command.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The VERIFY command in SQL is used to control whether or not to display the substitution variables before and after SQL*Plus replaces them with values.
The FEEDBACK command in SQL*Plus is used to display the number of records returned by a SQL query, enhancing user awareness about the query's impact.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
DECLARE
v_result VARCHAR2(200);
BEGIN
SELECT employee_name INTO v_result
FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not found');
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
The FEEDBACK command in SQL*Plus is used to display the number of records returned by a SQL query, enhancing user awareness about the query's impact.
No, SQL*Plus does not have its own PL/SQL engine.
When you submit a PL/SQL block in SQL*Plus, it is sent entirely to the Oracle Database Server where the server-side PL/SQL engine compiles and executes it.
SQL*Plus itself only handles the submission and display of results.
This is different from Oracle Forms or Oracle Reports which have their own client-side PL/SQL engines.
The implication is that all PL/SQL code in SQL*Plus requires a database connection and runs on the server.
-- SQL*Plus submits entire block to DB server
-- SQL*Plus itself only handles:
-- 1. Sending the block to Oracle server
-- 2. Displaying DBMS_OUTPUT (with SET SERVEROUTPUT ON)
-- 3. Substitution variables (&var)
-- Example: this block runs on the SERVER, not in SQL*Plus
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Running on: ' ||
SYS_CONTEXT('USERENV','SERVER_HOST'));
DBMS_OUTPUT.PUT_LINE('DB version: ' ||
SUBSTR(DBMS_DB_VERSION.VERSION,1,2));
END;
/
-- SQL*Plus features (client-side):
-- SPOOL output.txt client file write
-- @script.sql client file execute
-- &substitution client-side variable| OUTPUT / RESULT |
|---|
| Running on: db-server-01 |
| DB version: 19 |
| SQL*Plus: no PL/SQL engine — sends block to DB server |
| SET SERVEROUTPUT ON: required to see DBMS_OUTPUT results |
No, SQL*Plus does not have its own PL/SQL engine.
TTITLE and BTITLE are SQL*Plus formatting commands for report headers and footers.
TTITLE (Top Title) sets a title that appears at the top of every page in a spooled report.
BTITLE (Bottom Title) sets a footer that appears at the bottom of every page.
They support page numbers (SQL.PNO), date/time, and custom text.
They are typically used with SPOOL to produce formatted report files.
Modern Oracle applications use APEX or BI Publisher for reporting, but SQL*Plus reports are still used for DBA scripts and scheduled exports.
-- SQL*Plus report formatting
SET PAGESIZE 50
SET LINESIZE 120
SET SERVEROUTPUT ON
-- Top title (appears at top of every page)
TTITLE CENTER 'EMANO GmbH — Employee Salary Report' SKIP 1 -
RIGHT 'Page: ' FORMAT 999 SQL.PNO SKIP 2
-- Bottom title (appears at bottom of every page)
BTITLE LEFT 'Confidential — HR Department' -
RIGHT 'Generated: ' _DATE
-- Column formatting
COLUMN employee_name FORMAT A25 HEADING 'Employee Name'
COLUMN salary FORMAT 999,999 HEADING 'Salary'
COLUMN department_id FORMAT 99 HEADING 'Dept'
-- Spool to file and run query
SPOOL salary_report.txt
SELECT employee_name, salary, department_id
FROM employees ORDER BY department_id, salary DESC;
SPOOL OFF
TTITLE OFF
BTITLE OFF| OUTPUT / RESULT |
|---|
| EMANO GmbH — Employee Salary Report Page: 1 |
| Employee Name Salary Dept |
| Alice Johnson 50,000 10 |
| Carol White 55,000 10 |
| Confidential — HR Department Generated: 27-APR-26 |
TTITLE and BTITLE are SQL*Plus formatting commands for report headers and footers.
Managing PL/SQL code in large projects requires adhering to best practices such as modular programming, code reuse, and consistent naming conventions.
Developers should use version control systems to track changes and facilitate collaboration.
Documentation is crucial for maintainability and understanding complex logic.
Performance profiling and testing ensure code efficiency and reliability.
Adopting these best practices ensures scalable, maintainable, and high-quality PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Best practice 1: Consistent naming conventions
-- pkg_ = package, prc_ = procedure, fnc_ = function
-- trg_ = trigger, v_ = variable, c_ = constant
-- t_ = type, p_ = parameter, mv_ = materialized view
-- Best practice 2: Package-based modular design
CREATE OR REPLACE PACKAGE pkg_order_api AS
PROCEDURE prc_create_order(p_customer_id NUMBER, p_amount NUMBER);
PROCEDURE prc_cancel_order(p_order_id NUMBER);
FUNCTION fnc_get_total(p_customer_id NUMBER) RETURN NUMBER;
END pkg_order_api;
/
-- Best practice 3: Always use bind variables (never concatenate user input)
CREATE OR REPLACE PROCEDURE prc_safe_search(p_name VARCHAR2) IS
v_count NUMBER;
BEGIN
-- SAFE: bind variable
SELECT COUNT(*) INTO v_count FROM employees
WHERE UPPER(employee_name) = UPPER(p_name);
-- NEVER do this (SQL injection risk):
-- EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees WHERE name = ''' || p_name || '''';
DBMS_OUTPUT.PUT_LINE('Found: ' || v_count);
END;
/
-- Best practice 4: Centralise exception handling
CREATE OR REPLACE PROCEDURE prc_handle_error(
p_module VARCHAR2, p_msg VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log(module, message, logged_at)
VALUES (p_module, p_msg, SYSDATE);
COMMIT;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
Managing PL/SQL code in large projects requires adhering to best practices such as modular programming, code reuse, and consistent naming conventions.
To ensure the scalability of PL/SQL applications, developers employ strategies such as code optimization, efficient data access patterns, and the use of advanced PL/SQL features like bulk binding.
Scalability is further enhanced by optimizing database design and leveraging Oracle features like partitioning.
Scalability strategies ensure that PL/SQL applications can handle growing data volumes and user loads.
These approaches are fundamental in maintaining the high performance and responsiveness of PL/SQL applications as they scale.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Scalability strategy 1: BULK COLLECT + FORALL (avoid row-by-row)
DECLARE
TYPE t_ids IS TABLE OF NUMBER;
TYPE t_sals IS TABLE OF NUMBER;
v_ids t_ids;
v_sals t_sals;
BEGIN
SELECT employee_id, salary BULK COLLECT INTO v_ids, v_sals FROM employees;
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET salary = v_sals(i) * 1.03 WHERE employee_id = v_ids(i);
COMMIT;
END;
/
-- Scalability strategy 2: Partition large work into chunks
CREATE OR REPLACE PROCEDURE prc_process_in_chunks(p_chunk_size NUMBER DEFAULT 1000) IS
v_min_id NUMBER;
v_max_id NUMBER;
v_cursor SYS_REFCURSOR;
BEGIN
SELECT MIN(employee_id), MAX(employee_id) INTO v_min_id, v_max_id FROM employees;
WHILE v_min_id <= v_max_id LOOP
UPDATE employees SET processed_flag = 'Y'
WHERE employee_id BETWEEN v_min_id AND v_min_id + p_chunk_size - 1;
COMMIT; -- commit each chunk to avoid large rollback segments
v_min_id := v_min_id + p_chunk_size;
END LOOP;
END;
/| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| OPERATION | ROWS | CONTEXT_SWITCHES |
|---|---|---|
| Row-by-row | 4 | 4 (slow) |
| BULK COLLECT | 4 | 1 (fast) |
BULK COLLECT fetches all rows at once — reduces SQLPL/SQL round trips
To ensure the scalability of PL/SQL applications, developers employ strategies such as code optimization, efficient data access patterns, and the use of advanced PL/SQL features like bulk binding.
When designing PL/SQL packages for large-scale applications, considerations include modularity, performance optimization, and security.
Packages should be organized to encapsulate related functionality, facilitating code reuse and maintainability.
Performance considerations involve efficient error handling, use of bulk operations, and minimizing context switches.
Security measures include implementing proper access controls and validating inputs to prevent SQL injection.
These considerations are crucial for building scalable, efficient, and secure PL/SQL applications.
-- Well-designed package for large-scale application
CREATE OR REPLACE PACKAGE pkg_order_mgmt AS
-- Public constants (avoid magic numbers in code)
c_status_pending CONSTANT VARCHAR2(10) := 'PENDING';
c_status_approved CONSTANT VARCHAR2(10) := 'APPROVED';
c_status_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
c_max_order_lines CONSTANT NUMBER := 500;
-- Custom exception (public so callers can handle it)
e_order_limit_exceeded EXCEPTION;
PRAGMA EXCEPTION_INIT(e_order_limit_exceeded, -20200);
-- Public API
FUNCTION fnc_create_order(p_customer_id NUMBER, p_amount NUMBER) RETURN NUMBER;
PROCEDURE prc_approve_order(p_order_id NUMBER);
PROCEDURE prc_cancel_order(p_order_id NUMBER, p_reason VARCHAR2);
FUNCTION fnc_get_status(p_order_id NUMBER) RETURN VARCHAR2;
END pkg_order_mgmt;
/
CREATE OR REPLACE PACKAGE BODY pkg_order_mgmt AS
-- Private helper
PROCEDURE prc_validate_order(p_order_id NUMBER) IS
v_status VARCHAR2(10);
BEGIN
SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
IF v_status = c_status_cancelled THEN
RAISE_APPLICATION_ERROR(-20200, 'Cannot modify cancelled order: ' || p_order_id);
END IF;
END;
FUNCTION fnc_create_order(p_customer_id NUMBER, p_amount NUMBER) RETURN NUMBER IS
v_order_id NUMBER;
BEGIN
INSERT INTO orders(customer_id, amount, status, created_at)
VALUES (p_customer_id, p_amount, c_status_pending, SYSDATE)
RETURNING order_id INTO v_order_id;
COMMIT;
RETURN v_order_id;
END fnc_create_order;
PROCEDURE prc_approve_order(p_order_id NUMBER) IS
BEGIN
prc_validate_order(p_order_id);
UPDATE orders SET status = c_status_approved WHERE order_id = p_order_id;
COMMIT;
END prc_approve_order;
PROCEDURE prc_cancel_order(p_order_id NUMBER, p_reason VARCHAR2) IS
BEGIN
prc_validate_order(p_order_id);
UPDATE orders SET status = c_status_cancelled, cancel_reason = p_reason
WHERE order_id = p_order_id;
COMMIT;
END prc_cancel_order;
FUNCTION fnc_get_status(p_order_id NUMBER) RETURN VARCHAR2 IS
v_status VARCHAR2(10);
BEGIN
SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
RETURN v_status;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'NOT_FOUND';
END fnc_get_status;
END pkg_order_mgmt;
/| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
When designing PL/SQL packages for large-scale applications, considerations include modularity, performance optimization, and security.
Implementing a version control system for PL/SQL code involves using tools like Git or SVN to manage changes and revisions.
Developers store PL/SQL scripts in repositories, enabling collaborative development and change tracking.
Branching and merging strategies are applied to manage different development streams.
Continuous integration pipelines automate testing and deployment of PL/SQL changes.
This approach ensures code integrity and facilitates team collaboration.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Recommended Git folder structure:
-- /db
-- /packages
-- pkg_order_mgmt.pks (spec)
-- pkg_order_mgmt.pkb (body)
-- /procedures
-- prc_process_order.prc
-- /functions
-- fnc_get_salary.fnc
-- /triggers
-- trg_emp_audit.trg
-- /migrations
-- V001__initial_schema.sql
-- V002__add_audit_table.sql
-- deploy_all.sql
-- Header comment template (add to every object)
/*
* OBJECT : pkg_order_mgmt
* PURPOSE : Order management API
* AUTHOR : Tharun Kommaddi
* CREATED : 2025-01-01
* VERSION : 1.3
*
* CHANGE LOG:
* v1.0 - 2025-01-01 - Initial version
* v1.1 - 2025-02-15 - Added prc_cancel_order
* v1.2 - 2025-03-01 - Performance tuning
* v1.3 - 2025-04-01 - Added audit logging
*/
-- Tag a release in Git:
-- git tag -a v1.3 -m "Release 1.3 - Added audit logging"
-- git push origin v1.3
-- CI pipeline checks (e.g., Jenkins/GitHub Actions):
-- 1. sqlplus @deploy_all.sql -> returns 0 on success
-- 2. utplsql run HR -> unit tests
-- 3. Tag and notify on success| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Implementing a version control system for PL/SQL code involves using tools like Git or SVN to manage changes and revisions.
Automating the deployment of PL/SQL code is achieved through continuous integration and deployment tools such as Jenkins, Ansible, or Oracle Developer Cloud Service.
Scripts and tools automate the build, test, and deployment processes.
Version control systems manage the PL/SQL codebase, ensuring consistency across environments.
Automation reduces human error, increases efficiency, and supports scalable deployment practices.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- deploy_all.sql (master deploy script called by CI pipeline)
-- WHENEVER SQLERROR EXIT FAILURE (stops on error in SQL*Plus)
-- Run in dependency order
-- @migrations/V001__initial_schema.sql
-- @migrations/V002__add_audit_table.sql
-- @packages/pkg_order_mgmt.pks
-- @packages/pkg_order_mgmt.pkb
-- @triggers/trg_emp_audit.trg
-- Environment-specific config via substitution variables
-- DEFINE env_suffix = DEV (override on command line: sqlplus ... env_suffix=PROD)
-- Post-deploy validation
DECLARE
v_invalid NUMBER;
BEGIN
SELECT COUNT(*) INTO v_invalid
FROM all_objects
WHERE status = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER')
AND owner = 'HR';
IF v_invalid > 0 THEN
RAISE_APPLICATION_ERROR(-20999, v_invalid || ' invalid objects after deploy!');
END IF;
DBMS_OUTPUT.PUT_LINE('Deploy validated: all objects VALID');
END;
/
-- Run unit tests (utPLSQL framework)
-- EXEC ut.run('HR');| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
Automating the deployment of PL/SQL code is achieved through continuous integration and deployment tools such as Jenkins, Ansible, or Oracle Developer Cloud Service.
Strategies for PL/SQL code review and quality assurance include automated static code analysis, peer review processes, and adherence to coding standards.
Tools like SonarQube or PL/SQL Developer's Code Analyzer identify potential issues and deviations from best practices.
Peer reviews facilitate knowledge sharing and ensure adherence to project-specific guidelines.
Regular code reviews and the use of automated tools are essential for maintaining high-quality PL/SQL code.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- utPLSQL unit test example (industry standard for PL/SQL testing)
CREATE OR REPLACE PACKAGE ut_pkg_salary AS
--%suite(Salary Package Tests)
--%test(Give bonus increases salary by bonus rate)
PROCEDURE test_give_bonus;
--%test(Zero salary employee gets no bonus)
PROCEDURE test_zero_salary_no_bonus;
--%test(Non-existent employee raises exception)
PROCEDURE test_invalid_employee;
END ut_pkg_salary;
/
CREATE OR REPLACE PACKAGE BODY ut_pkg_salary AS
PROCEDURE test_give_bonus IS
v_before NUMBER;
v_after NUMBER;
BEGIN
SELECT salary INTO v_before FROM employees WHERE employee_id = 101;
pkg_emp.prc_give_bonus(101);
SELECT salary INTO v_after FROM employees WHERE employee_id = 101;
-- Assert salary increased by bonus rate
ut.expect(v_after).to_equal(ROUND(v_before * (1 + pkg_emp.c_bonus_rate)));
ROLLBACK;
END;
PROCEDURE test_zero_salary_no_bonus IS BEGIN NULL; END;
PROCEDURE test_invalid_employee IS BEGIN NULL; END;
END ut_pkg_salary;
/
-- Run all tests
-- EXEC ut.run('HR');
-- Static code analysis with external tools:
-- SonarQube with PL/SQL plugin
-- Checkstyle for naming conventions
-- Peer review checklist: bind variables, exception handling, comments| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Strategies for PL/SQL code review and quality assurance include automated static code analysis, peer review processes, and adherence to coding standards.
Integrating Java or C code with PL/SQL applications is achieved through Oracle's External Procedures feature.
This involves creating PL/SQL wrappers that call external Java or C functions.
The Java or C code is compiled and stored in the database or on the server, and accessed via Oracle's Java Virtual Machine or external procedure calls.
This integration allows leveraging the strengths of each language, expanding the functionality of PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Step 1: Write Java class (compile separately and load into Oracle)
-- javac FileUtils.java
-- loadjava -user hr/hr@db FileUtils.class
/*
public class FileUtils {
public static String getFileSize(String path) {
java.io.File f = new java.io.File(path);
return f.exists() ? String.valueOf(f.length()) : "NOT_FOUND";
}
}
*/
-- Step 2: Create PL/SQL wrapper calling the Java method
CREATE OR REPLACE FUNCTION fnc_get_file_size(p_path VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'FileUtils.getFileSize(java.lang.String) return java.lang.String';
/
-- Usage
DECLARE
v_size VARCHAR2(50);
BEGIN
v_size := fnc_get_file_size('/oracle/data/employees.csv');
DBMS_OUTPUT.PUT_LINE('File size: ' || v_size || ' bytes');
END;
/
-- External C procedure via shared library (DBA sets up extproc)
-- CREATE OR REPLACE LIBRARY c_lib AS '/oracle/lib/mylib.so';
-- CREATE OR REPLACE PROCEDURE prc_call_c(p_val NUMBER)
-- AS EXTERNAL NAME "c_function" LIBRARY c_lib LANGUAGE C;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| INPUT | RETURN_VALUE | NOTES |
|---|---|---|
| 101 | 50,000 | Salary of Alice Johnson |
| 102 | 62,000 | Salary of Bob Smith |
| 999 | NULL | Employee not found |
Function returns one value per call — callable from SELECT statements
Integrating Java or C code with PL/SQL applications is achieved through Oracle's External Procedures feature.
Best practices for managing database connections in PL/SQL include using connection pooling, minimizing the number of open connections, and properly closing connections after use.
Efficient transaction management ensures that resources are not held unnecessarily.
Applications should monitor connection usage and adjust pool sizes based on load.
Adhering to these practices ensures scalable and efficient database access within PL/SQL applications.
| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
-- Enable DRCP (Database Resident Connection Pooling) — DBA runs this:
-- EXEC DBMS_CONNECTION_POOL.START_POOL();
-- EXEC DBMS_CONNECTION_POOL.CONFIGURE_POOL(
-- pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
-- minsize => 10,
-- maxsize => 50,
-- incrsize => 5
-- );
-- Application connect string for DRCP:
-- jdbc:oracle:thin:@//host:1521/service:pooled
-- Best practice: use connection pooling (UCP / DRCP)
-- Best practice: close connections promptly after use
-- Best practice: use short transactions — COMMIT quickly
CREATE OR REPLACE PROCEDURE prc_short_transaction(p_emp_id NUMBER) IS
BEGIN
-- Keep transactions short to release locks quickly
UPDATE employees SET last_reviewed = SYSDATE WHERE employee_id = p_emp_id;
COMMIT; -- commit immediately, don't hold the connection open
END;
/
-- Monitor connection usage
SELECT username, count(*) AS sessions,
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active
FROM v$session
WHERE username IS NOT NULL
GROUP BY username
ORDER BY sessions DESC;| emp_id | emp_name | dept_id | salary |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
Data state before execution.
| DEPT_ID | EMP_COUNT | AVG_SALARY | MAX_SALARY |
|---|---|---|---|
| 10 | 2 | 52,500 | 55,000 |
| 20 | 2 | 66,000 | 70,000 |
2 groups — Alice+Carol in dept 10, Bob+Dave in dept 20
Best practices for managing database connections in PL/SQL include using connection pooling, minimizing the number of open connections, and properly closing connections after use.
Ensuring the maintainability of PL/SQL code in long-term projects involves adhering to coding standards, implementing comprehensive documentation, and using modular programming techniques.
Regular code reviews and refactoring sessions identify areas for improvement.
Version control systems track changes and facilitate collaboration.
These practices ensure that PL/SQL code remains clean, understandable, and adaptable to future requirements.
| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
-- 1. Constants over magic numbers
CREATE OR REPLACE PACKAGE pkg_constants AS
c_max_login_attempts CONSTANT PLS_INTEGER := 3;
c_session_timeout CONSTANT NUMBER := 30; -- minutes
c_currency CONSTANT VARCHAR2(3) := 'USD';
END pkg_constants;
/
-- 2. Self-documenting code with meaningful names
CREATE OR REPLACE PROCEDURE prc_transfer_funds(
p_from_account_id NUMBER,
p_to_account_id NUMBER,
p_amount NUMBER
) IS
v_from_balance NUMBER;
e_insufficient EXCEPTION;
BEGIN
SELECT balance INTO v_from_balance
FROM accounts WHERE account_id = p_from_account_id FOR UPDATE;
IF v_from_balance < p_amount THEN
RAISE e_insufficient;
END IF;
UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account_id;
UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account_id;
COMMIT;
EXCEPTION
WHEN e_insufficient THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20300, 'Insufficient funds: balance=' || v_from_balance);
END;
/
-- 3. Comprehensive header comments on every object
-- 4. Version-controlled in Git
-- 5. Unit tested with utPLSQL
-- 6. Peer reviewed before merge| account_id | owner | balance |
|---|---|---|
| 1001 | Alice | 5,000 |
| 1002 | Bob | 3,200 |
| 1003 | Carol | 8,750 |
Data state before execution.
| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Ensuring the maintainability of PL/SQL code in long-term projects involves adhering to coding standards, implementing comprehensive documentation, and using modular programming techniques.
Oracle's Advanced Queuing (AQ) is leveraged for messaging within PL/SQL by providing a robust and reliable mechanism for asynchronous communication between applications.
AQ supports point-to-point and publish-subscribe messaging models.
It facilitates the decoupling of application components, allowing for scalable and flexible application architectures.
PL/SQL procedures interact with queues to enqueue and dequeue messages using the DBMS_AQ and DBMS_AQADM packages, enabling efficient data exchange and event-driven programming.
-- Step 1: Create message payload type
CREATE OR REPLACE TYPE t_order_msg AS OBJECT (
order_id NUMBER,
customer_id NUMBER,
total_amount NUMBER
);
/
-- Step 2: Create queue table and queue (DBA or schema owner)
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'order_queue_tab',
queue_payload_type => 'T_ORDER_MSG'
);
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'order_queue',
queue_table => 'order_queue_tab'
);
DBMS_AQADM.START_QUEUE(queue_name => 'order_queue');
END;
/
-- Step 3: Enqueue (producer sends a message)
DECLARE
v_enq_opts DBMS_AQ.ENQUEUE_OPTIONS_T;
v_msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
v_msg_id RAW(16);
BEGIN
v_msg_props.expiration := 3600; -- message expires in 1 hour
DBMS_AQ.ENQUEUE(
queue_name => 'order_queue',
enqueue_options => v_enq_opts,
message_properties => v_msg_props,
payload => t_order_msg(1001, 555, 2500),
msgid => v_msg_id
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Order enqueued. MsgID: ' || RAWTOHEX(v_msg_id));
END;
/
-- Step 4: Dequeue (consumer processes the message)
DECLARE
v_deq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
v_msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
v_msg_id RAW(16);
v_message t_order_msg;
BEGIN
v_deq_opts.wait := DBMS_AQ.NO_WAIT;
DBMS_AQ.DEQUEUE('order_queue', v_deq_opts, v_msg_props, v_message, v_msg_id);
DBMS_OUTPUT.PUT_LINE('Order: ' || v_message.order_id ||
', Customer: ' || v_message.customer_id ||
', Amount: $' || v_message.total_amount);
COMMIT;
EXCEPTION
WHEN DBMS_AQ.DEQUEUE_TIMEOUT THEN
DBMS_OUTPUT.PUT_LINE('Queue is empty');
END;
/| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
Oracle's Advanced Queuing (AQ) is leveraged for messaging within PL/SQL by providing a robust and reliable mechanism for asynchronous communication between applications.