The Complete PL/SQL Reference Guide
Oracle Database's Procedural Programming Language

From beginner fundamentals to advanced database programming, performance, security, and production practices.

A practical learning resource and reference for Oracle Database PL/SQL. 

Examples may need adaptation to your Oracle version, schema, and privileges.

1. Getting started

PL/SQL (Procedural Language/SQL) is Oracle Database's procedural programming language. It combines SQL's data retrieval and manipulation capabilities with variables, conditions, loops, exceptions, procedures, functions, packages, cursors, and triggers.

PL/SQL is commonly used to implement database business rules, validate data, automate work, process large datasets, create reusable database APIs, schedule jobs, and maintain transactional consistency.

SQL versus PL/SQL

SQLPL/SQL
Primarily declarative: describes data operations.Procedural: describes a sequence of operations.
Usually executes individual statements.Can execute many statements in a block.
Queries and modifies data.Adds variables, loops, conditions, exceptions, and reusable program units.

Set up a practice table

Use an Oracle SQL worksheet or SQL*Plus/SQLcl session. Enable output in SQL*Plus or SQLcl with SET SERVEROUTPUT ON. In SQL Developer, enable the DBMS Output panel for your connection.


CREATE TABLE plsql_employees (
	employee_id   NUMBER GENERATED BY DEFAULT AS IDENTITY
				  PRIMARY KEY,
	employee_name VARCHAR2(100) NOT NULL,
	department_id NUMBER,
	salary        NUMBER(10, 2) DEFAULT 0 NOT NULL,
	hire_date     DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO plsql_employees (employee_name, department_id, salary)
VALUES ('Ayesha', 10, 60000);

INSERT INTO plsql_employees (employee_name, department_id, salary)
VALUES ('Bilal', 20, 45000);

INSERT INTO plsql_employees (employee_name, department_id, salary)
VALUES ('Sara', 10, 75000);

COMMIT;

Version note: Oracle syntax and feature availability vary by release. The examples use conventional syntax found in modern Oracle releases; verify version-specific behavior against the documentation for your database.

2. Understanding the PL/SQL block

A PL/SQL block has up to three sections: optional declarations, a required executable section, and optional exception handlers.


DECLARE
	-- Variables, constants, cursors, local types
BEGIN
	-- Executable statements
EXCEPTION
	-- Exception handlers
END;
/

The slash is a client command used by tools such as SQL*Plus and SQLcl to submit the block; it is not part of PL/SQL itself.


BEGIN
	DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/

Declarations and nested blocks


DECLARE
	v_message VARCHAR2(100) := 'Outer block';
BEGIN
	DBMS_OUTPUT.PUT_LINE(v_message);

	DECLARE
		v_message VARCHAR2(100) := 'Inner block';
	BEGIN
		DBMS_OUTPUT.PUT_LINE(v_message);
	END;

	DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

A nested block can declare its own variables and handlers. An inner declaration can shadow an outer variable with the same name; the inner variable is visible only within its scope.

3. Variables, constants, and data types

Declaring variables


DECLARE
	v_employee_id NUMBER;
	v_name        VARCHAR2(100);
	v_salary      NUMBER(10, 2) := 50000;
	v_is_active   BOOLEAN := TRUE;
BEGIN
	v_employee_id := 101;
	v_name := 'Ayesha';
	v_salary := v_salary + 5000;

	DBMS_OUTPUT.PUT_LINE(v_name);
	DBMS_OUTPUT.PUT_LINE(v_salary);
END;
/

Use := for assignment in PL/SQL and = for equality comparisons. Uninitialized variables normally start as NULL, unless a constraint such as NOT NULL requires an initial value.

Constants


DECLARE
	c_tax_rate CONSTANT NUMBER := 0.15;
	c_company  CONSTANT VARCHAR2(50) := 'Example Ltd';
BEGIN
	DBMS_OUTPUT.PUT_LINE(c_tax_rate);
	DBMS_OUTPUT.PUT_LINE(c_company);
END;
/

Common data types

TypePurpose
NUMBER(p,s)Integer or decimal numeric values.
PLS_INTEGEREfficient integer arithmetic in PL/SQL.
VARCHAR2(n)Variable-length character data.
CHAR(n)Fixed-length character data.
BOOLEANLogical values: TRUE, FALSE, NULL. SQL support depends on database version.
DATEDate and time to seconds.
TIMESTAMPDate/time with fractional seconds; variants support time zones.
CLOB / BLOBLarge character / binary data.
RAW(n)Binary data.
INTERVALDuration or time interval.

Anchored declarations: %TYPE and %ROWTYPE


DECLARE
	v_salary plsql_employees.salary%TYPE;
	v_emp    plsql_employees%ROWTYPE;
BEGIN
	SELECT salary
	INTO v_salary
	FROM plsql_employees
	WHERE employee_id = 1;

	SELECT *
	INTO v_emp
	FROM plsql_employees
	WHERE employee_id = 1;

	DBMS_OUTPUT.PUT_LINE(v_salary);
	DBMS_OUTPUT.PUT_LINE(v_emp.employee_name);
END;
/

%TYPE inherits a column or variable's type; %ROWTYPE creates a record shaped like a table or cursor row. These reduce duplicated type information and help code track schema changes.

NULL and conversion


IF v_salary IS NULL THEN
	DBMS_OUTPUT.PUT_LINE('Salary is unknown');
END IF;

v_number := TO_NUMBER('125.50');
v_date   := TO_DATE('2026-10-29', 'YYYY-MM-DD');
v_text   := TO_CHAR(v_date, 'DD-MON-YYYY');

Use IS NULL and IS NOT NULL, not = NULL. Oracle treats a zero-length string as NULL for conventional character types. Prefer explicit conversions and format masks to avoid dependence on session NLS settings.

4. Operators and expressions

CategoryOperators
Arithmetic+ - * / **
Comparison= <> != > < >= <=
LogicalAND OR NOT
Concatenation||

DECLARE
	v_price NUMBER := 200;
	v_qty   NUMBER := 3;
	v_total NUMBER;
BEGIN
	v_total := v_price * v_qty;
	DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END;
/

Boolean logic includes TRUE, FALSE, and NULL (unknown). PL/SQL also uses short-circuit evaluation, so the second condition in an AND/OR expression might not be evaluated if the result is already determined.

5. Conditional statements

IF, ELSIF, ELSE


IF v_marks >= 90 THEN
	v_grade := 'A';
ELSIF v_marks >= 80 THEN
	v_grade := 'B';
ELSIF v_marks >= 70 THEN
	v_grade := 'C';
ELSE
	v_grade := 'D';
END IF;

The first matching branch executes. Put conditions in the intended priority order.

CASE statement


CASE v_status
	WHEN 'A' THEN
		DBMS_OUTPUT.PUT_LINE('Active');
	WHEN 'I' THEN
		DBMS_OUTPUT.PUT_LINE('Inactive');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Unknown');
END CASE;

A searched CASE evaluates conditions:


CASE
	WHEN v_salary >= 100000 THEN
		DBMS_OUTPUT.PUT_LINE('Senior band');
	WHEN v_salary >= 50000 THEN
		DBMS_OUTPUT.PUT_LINE('Middle band');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Entry band');
END CASE;

A CASE expression returns a value; a CASE statement executes statements. Example: v_grade := CASE WHEN v_marks >= 80 THEN 'A' ELSE 'C' END;.

6. Loops and iteration

Basic LOOP


DECLARE
	v_counter PLS_INTEGER := 1;
BEGIN
	LOOP
		DBMS_OUTPUT.PUT_LINE(v_counter);
		v_counter := v_counter + 1;
		EXIT WHEN v_counter > 5;
	END LOOP;
END;
/

WHILE and FOR loops


WHILE v_counter <= 5 LOOP
	DBMS_OUTPUT.PUT_LINE(v_counter);
	v_counter := v_counter + 1;
END LOOP;

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

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

A WHILE loop tests before each iteration. A numeric FOR loop controls its own index; do not assign to the loop index. EXIT terminates a loop, and CONTINUE skips to the next iteration.


FOR i IN 1..10 LOOP
	CONTINUE WHEN MOD(i, 2) = 0;
	DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
LoopBest use
LOOPRepeat until an explicit exit.
WHILERepeat while a condition remains true.
Numeric FORKnown integer range.
Cursor FORProcess query results row by row.

Prefer set-based SQL over loops when a single SQL statement can do the work.

7. SQL inside PL/SQL

SELECT INTO


DECLARE
	v_name   plsql_employees.employee_name%TYPE;
	v_salary plsql_employees.salary%TYPE;
BEGIN
	SELECT employee_name, salary
	INTO v_name, v_salary
	FROM plsql_employees
	WHERE employee_id = 1;

	DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
END;
/

A SELECT INTO expects exactly one row. Zero rows raises NO_DATA_FOUND; more than one raises TOO_MANY_ROWS. Use a cursor or collection-based approach for multiple rows.

INSERT, UPDATE, DELETE


INSERT INTO plsql_employees (employee_name, department_id, salary)
VALUES ('Hamza', 30, 55000);

UPDATE plsql_employees
SET salary = salary * 1.10
WHERE department_id = 10;

DELETE FROM plsql_employees
WHERE employee_id = 2;

These statements can be executed directly inside a PL/SQL block. They do not automatically commit. An UPDATE or DELETE without a WHERE clause affects all rows, subject to constraints and triggers.

MERGE

MERGE can update matching rows and insert nonmatching rows. Assume a staging table with compatible columns and unique source keys:


MERGE INTO plsql_employees target
USING staging_employees source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
	UPDATE SET
		target.employee_name = source.employee_name,
		target.department_id = source.department_id,
		target.salary = source.salary
WHEN NOT MATCHED THEN
	INSERT (employee_id, employee_name, department_id, salary)
	VALUES (source.employee_id, source.employee_name,
			source.department_id, source.salary);

RETURNING INTO


DECLARE
	v_new_salary NUMBER;
BEGIN
	UPDATE plsql_employees
	SET salary = salary + 1000
	WHERE employee_id = 1
	RETURNING salary INTO v_new_salary;

	DBMS_OUTPUT.PUT_LINE(v_new_salary);
END;
/

Check SQL%ROWCOUNT when no matching row is possible; do not assume a RETURNING target contains a newly returned value if no row was affected.

8. Cursors

Implicit cursors are managed by Oracle. Explicit cursors let your code control query processing.

Implicit cursor attributes

AttributeMeaning
SQL%FOUNDWhether the latest SQL statement affected or returned at least one row.
SQL%NOTFOUNDWhether it affected or returned no rows.
SQL%ROWCOUNTNumber of rows affected or returned.
SQL%ISOPENAlways FALSE for the implicit SQL cursor.

Check these attributes immediately after the statement of interest, because a later SQL statement can replace the implicit cursor status.

Explicit cursor


DECLARE
	CURSOR c_employees IS
		SELECT employee_id, employee_name, salary
		FROM plsql_employees
		WHERE department_id = 10;

	v_id     plsql_employees.employee_id%TYPE;
	v_name   plsql_employees.employee_name%TYPE;
	v_salary plsql_employees.salary%TYPE;
BEGIN
	OPEN c_employees;
	LOOP
		FETCH c_employees INTO v_id, v_name, v_salary;
		EXIT WHEN c_employees%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
	END LOOP;
	CLOSE c_employees;
END;
/

The lifecycle is declare, open, fetch, stop at no rows, and close. Make sure a manually opened cursor is closed on all relevant paths.

Cursor FOR loop


BEGIN
	FOR emp IN (
		SELECT employee_id, employee_name, salary
		FROM plsql_employees
		WHERE department_id = 10
	) LOOP
		DBMS_OUTPUT.PUT_LINE(emp.employee_name || ': ' || emp.salary);
	END LOOP;
END;
/

Oracle opens, fetches, and closes the cursor automatically. Parameterized cursors accept arguments to filter a query:


DECLARE
	CURSOR c_employees(p_dept NUMBER) IS
		SELECT employee_name, salary
		FROM plsql_employees
		WHERE department_id = p_dept;
BEGIN
	FOR emp IN c_employees(10) LOOP
		DBMS_OUTPUT.PUT_LINE(emp.employee_name || ': ' || emp.salary);
	END LOOP;
END;
/

SELECT FOR UPDATE and REF CURSOR

SELECT ... FOR UPDATE locks selected rows for the transaction and can be used with WHERE CURRENT OF. Keep transactions short and avoid waiting on external services while holding locks.


DECLARE
	TYPE t_cursor IS REF CURSOR;
	v_cursor t_cursor;
	v_name VARCHAR2(100);
BEGIN
	OPEN v_cursor FOR
		SELECT employee_name
		FROM plsql_employees
		WHERE department_id = 10;

	LOOP
		FETCH v_cursor INTO v_name;
		EXIT WHEN v_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_name);
	END LOOP;

	CLOSE v_cursor;
END;
/

REF CURSOR variables represent result sets and are useful when returning query results from procedures to client applications. SYS_REFCURSOR is a predefined weak REF CURSOR type.

9. Records and collections

Records


DECLARE
	TYPE t_employee IS RECORD (
		employee_id NUMBER,
		employee_name VARCHAR2(100),
		salary NUMBER
	);
	v_employee t_employee;
BEGIN
	v_employee.employee_id := 101;
	v_employee.employee_name := 'Ayesha';
	v_employee.salary := 60000;
	DBMS_OUTPUT.PUT_LINE(v_employee.employee_name);
END;
/

Associative arrays


DECLARE
	TYPE t_salary_map IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
	v_salaries t_salary_map;
BEGIN
	v_salaries(1) := 50000;
	v_salaries(2) := 60000;
	v_salaries(3) := 75000;
	DBMS_OUTPUT.PUT_LINE(v_salaries(2));
END;
/

Nested tables and VARRAYs


DECLARE
	TYPE t_numbers IS TABLE OF NUMBER;
	v_numbers t_numbers := t_numbers(10, 20, 30);
BEGIN
	v_numbers.EXTEND;
	v_numbers(4) := 40;
	DBMS_OUTPUT.PUT_LINE(v_numbers.COUNT);
END;
/

DECLARE
	TYPE t_scores IS VARRAY(5) OF NUMBER;
	v_scores t_scores := t_scores(90, 85, 95);
BEGIN
	DBMS_OUTPUT.PUT_LINE(v_scores.COUNT);
END;
/

A nested table can become sparse after deletion. A VARRAY has a declared maximum size. Collection methods include COUNT, FIRST, LAST, PRIOR, NEXT, EXISTS, EXTEND, TRIM, and DELETE. Some methods apply only to certain collection types; associative arrays do not support EXTEND or TRIM.


DECLARE
	TYPE t_values IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
	v_values t_values;
	v_index PLS_INTEGER;
BEGIN
	v_values(2) := 'Alpha';
	v_values(5) := 'Beta';
	v_values(10) := 'Gamma';

	v_index := v_values.FIRST;
	WHILE v_index IS NOT NULL LOOP
		DBMS_OUTPUT.PUT_LINE(v_index || ': ' || v_values(v_index));
		v_index := v_values.NEXT(v_index);
	END LOOP;
END;
/

Use FIRST/NEXT to traverse sparse collections rather than assuming indexes are contiguous.

10. Exception handling

When an exception is raised, normal execution in the current block stops and control transfers to a matching handler. If none matches, the exception propagates to an enclosing block or caller.


BEGIN
	-- statements
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('No row found');
	WHEN OTHERS THEN
		RAISE;
END;
/

Common predefined exceptions

ExceptionMeaning
NO_DATA_FOUNDSingle-row query returned no rows.
TOO_MANY_ROWSSingle-row query returned multiple rows.
ZERO_DIVIDEDivision by zero.
DUP_VAL_ON_INDEXDuplicate key violates a unique constraint.
INVALID_NUMBERCharacter-to-number conversion failed in a relevant context.
VALUE_ERRORValue, conversion, or size constraint error.
INVALID_CURSORInvalid cursor operation.

User-defined exceptions


DECLARE
	e_invalid_salary EXCEPTION;
	v_salary NUMBER := -1000;
BEGIN
	IF v_salary < 0 THEN
		RAISE e_invalid_salary;
	END IF;
EXCEPTION
	WHEN e_invalid_salary THEN
		DBMS_OUTPUT.PUT_LINE('Salary cannot be negative');
END;
/

Application errors and diagnostics


RAISE_APPLICATION_ERROR(-20001, 'Operation is not permitted.');

Application error numbers must be in the range -20000 through -20999. Use SQLCODE and SQLERRM in a handler to inspect an error. For deeper diagnostics, use DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.


BEGIN
	-- potentially failing operation
	NULL;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
		DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
		RAISE;
END;
/

Use PRAGMA EXCEPTION_INIT to associate a named exception with an Oracle error number. Avoid WHEN OTHERS THEN NULL; it hides failures and can make broken operations appear successful.

11. Procedures and functions

Stored procedure


CREATE OR REPLACE PROCEDURE increase_salary (
	p_employee_id IN plsql_employees.employee_id%TYPE,
	p_amount IN NUMBER
)
AS
BEGIN
	IF p_amount < 0 THEN
		RAISE_APPLICATION_ERROR(-20002, 'Increase cannot be negative.');
	END IF;

	UPDATE plsql_employees
	SET salary = salary + p_amount
	WHERE employee_id = p_employee_id;

	IF SQL%ROWCOUNT = 0 THEN
		RAISE_APPLICATION_ERROR(-20003, 'Employee not found.');
	END IF;
END increase_salary;
/

Call it using a PL/SQL block: BEGIN increase_salary(1, 1000); END; /. This procedure deliberately does not commit; the caller controls the transaction.

Function


CREATE OR REPLACE FUNCTION calculate_bonus(p_salary IN NUMBER)
RETURN NUMBER
AS
BEGIN
	RETURN p_salary * 0.10;
END calculate_bonus;
/

Functions return a value and can be called in PL/SQL expressions. They can also be called from SQL where their signature and implementation are compatible with SQL execution restrictions.

Parameter modes

ModePurpose
INPass input; default mode.
OUTReturn a value through a parameter.
IN OUTPass a value in and return its modified value.

CREATE OR REPLACE PROCEDURE calculate_total (
	p_price IN NUMBER,
	p_quantity IN NUMBER,
	p_total OUT NUMBER
)
AS
BEGIN
	p_total := p_price * p_quantity;
END calculate_total;
/

DECLARE
	v_total NUMBER;
BEGIN
	calculate_total(p_price => 100, p_quantity => 5, p_total => v_total);
	DBMS_OUTPUT.PUT_LINE(v_total);
END;
/

Named notation uses parameter_name => value and is useful for long parameter lists. Parameters can have defaults. Local subprograms can be declared inside a block. PL/SQL supports overloading when parameter signatures distinguish the subprograms in a permitted way; return type alone cannot distinguish overloads.

Procedure versus function

Use procedures for operations and functions for computations that return a value. Avoid surprising side effects in functions, especially those called from SQL.

12. Packages

A package groups related types, constants, variables, cursors, exceptions, procedures, and functions. Its specification is the public interface; its body implements that interface and can contain private elements.


CREATE OR REPLACE PACKAGE employee_api AS
	PROCEDURE increase_salary(p_employee_id IN NUMBER, p_amount IN NUMBER);
	FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER;
END employee_api;
/

CREATE OR REPLACE PACKAGE BODY employee_api AS
	PROCEDURE increase_salary(p_employee_id IN NUMBER, p_amount IN NUMBER) AS
	BEGIN
		UPDATE plsql_employees
		SET salary = salary + p_amount
		WHERE employee_id = p_employee_id;

		IF SQL%ROWCOUNT = 0 THEN
			RAISE_APPLICATION_ERROR(-20003, 'Employee not found.');
		END IF;
	END increase_salary;

	FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER AS
		v_salary NUMBER;
	BEGIN
		SELECT salary INTO v_salary
		FROM plsql_employees
		WHERE employee_id = p_employee_id;
		RETURN v_salary;
	END get_salary;
END employee_api;
/

Call public elements with dot notation, such as employee_api.get_salary(1). Package variables can retain session-specific state. Avoid using package state as durable storage, especially when applications use connection pools or long-lived sessions.

13. Triggers

A trigger runs automatically in response to a specified DML, DDL, or database event, depending on the trigger type.

Audit table and row-level trigger


CREATE TABLE employee_salary_audit (
	audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	employee_id NUMBER NOT NULL,
	old_salary NUMBER(10, 2),
	new_salary NUMBER(10, 2),
	changed_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON plsql_employees
FOR EACH ROW
WHEN (OLD.salary <> NEW.salary)
BEGIN
	INSERT INTO employee_salary_audit (employee_id, old_salary, new_salary)
	VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary);
END;
/

:OLD and :NEW refer to the old and new row images. The example's comparison does not catch transitions involving NULL, because comparisons with NULL evaluate to unknown. Design a null-safe change test if salary is nullable.

Trigger timing and design

  • BEFORE triggers run before the triggering operation completes; row-level BEFORE triggers can assign to supported :NEW fields.
  • AFTER triggers run after the relevant row or statement operation at the corresponding timing point.
  • FOR EACH ROW creates a row-level trigger; without it, the trigger is statement-level.

Triggers can introduce mutating-table errors, hidden side effects, recursive behavior, and per-row performance costs. Compound triggers can help with certain multi-row patterns. Keep triggers focused and prefer explicit procedures for complex workflows.

14. Transactions and transaction control

A transaction is a logical unit of work. Oracle provides COMMIT, ROLLBACK, and SAVEPOINT.


UPDATE plsql_employees
SET salary = salary + 500
WHERE employee_id = 1;

SAVEPOINT after_first_update;

UPDATE plsql_employees
SET salary = salary + 500
WHERE employee_id = 2;

ROLLBACK TO after_first_update;
COMMIT;

The second update is undone; the first remains and is committed. A savepoint is not a separate transaction.

Transaction ownership

COMMIT makes pending changes permanent; ROLLBACK undoes uncommitted work in the current transaction. A procedure that commits may also commit earlier work performed by its caller in the same transaction. Ordinary reusable business procedures should usually leave transaction control to the caller.

When a statement fails, Oracle rolls back that failed statement, but earlier successful statements may remain pending. An exception handler that simply re-raises does not automatically roll back the entire transaction.

Autonomous transactions


CREATE OR REPLACE PROCEDURE log_message(p_message IN VARCHAR2)
AS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO application_log(message_text, logged_at)
	VALUES (p_message, SYSTIMESTAMP);
	COMMIT;
END log_message;
/

This requires a compatible application_log table. Autonomous transactions run independently and are sometimes used for diagnostics that must survive a parent rollback. Avoid depending on uncommitted parent data or modifying rows locked by the parent transaction.

15. Dynamic SQL

Static SQL is written directly in source code. Dynamic SQL is constructed at runtime and is useful when statement structure or identifiers must vary.

EXECUTE IMMEDIATE


DECLARE
	v_count NUMBER;
BEGIN
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM plsql_employees'
	INTO v_count;
	DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

Bind variables


DECLARE
	v_salary NUMBER;
	v_id NUMBER := 1;
BEGIN
	EXECUTE IMMEDIATE
		'SELECT salary FROM plsql_employees WHERE employee_id = :id'
		INTO v_salary
		USING v_id;
	DBMS_OUTPUT.PUT_LINE(v_salary);
END;
/

For dynamic DML, bind values with USING. For ordinary dynamic SQL statements, positional binding follows placeholder occurrences; do not assume repeated names always behave like named-parameter maps in every statement type.

Dynamic identifiers and injection

Table and column names generally cannot be bind variables. Validate identifiers against a strict allowlist and consider DBMS_ASSERT where applicable.


DECLARE
	v_table_name VARCHAR2(30) := 'PLSQL_EMPLOYEES';
	v_count NUMBER;
BEGIN
	IF v_table_name NOT IN ('PLSQL_EMPLOYEES', 'EMPLOYEE_SALARY_AUDIT') THEN
		RAISE_APPLICATION_ERROR(-20010, 'Unsupported table name.');
	END IF;

	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name
		INTO v_count;
	DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

Prefer static SQL when the statement structure is known. DBMS_SQL is useful for advanced cases where result columns or their types are unknown until runtime; native dynamic SQL is simpler for most routine cases.

16. Bulk processing and performance

Repeatedly switching between the PL/SQL and SQL engines for individual rows can create overhead. Prefer set-based SQL first; use bulk operations when procedural processing is truly required.

Prefer set-based DML


-- Usually preferable to an UPDATE inside a loop:
UPDATE plsql_employees
SET salary = salary + 100
WHERE department_id = 10;

BULK COLLECT


DECLARE
	TYPE t_names IS TABLE OF plsql_employees.employee_name%TYPE;
	v_names t_names;
BEGIN
	SELECT employee_name BULK COLLECT INTO v_names
	FROM plsql_employees
	WHERE department_id = 10;

	FOR i IN 1..v_names.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(v_names(i));
	END LOOP;
END;
/

BULK COLLECT retrieves many rows into collections. For large datasets, use LIMIT to control memory:


DECLARE
	CURSOR c_employees IS SELECT employee_id, employee_name FROM plsql_employees;
	TYPE t_rows IS TABLE OF c_employees%ROWTYPE;
	v_rows t_rows;
BEGIN
	OPEN c_employees;
	LOOP
		FETCH c_employees BULK COLLECT INTO v_rows LIMIT 500;
		EXIT WHEN v_rows.COUNT = 0;

		FOR i IN 1..v_rows.COUNT LOOP
			DBMS_OUTPUT.PUT_LINE(v_rows(i).employee_name);
		END LOOP;
	END LOOP;
	CLOSE c_employees;
END;
/

For production, ensure the cursor is closed on exceptional paths. Prefer a cursor FOR loop when explicit batching is unnecessary.

FORALL


DECLARE
	TYPE t_ids IS TABLE OF plsql_employees.employee_id%TYPE;
	v_ids t_ids;
BEGIN
	SELECT employee_id BULK COLLECT INTO v_ids
	FROM plsql_employees
	WHERE department_id = 10;

	FORALL i IN 1..v_ids.COUNT
		UPDATE plsql_employees
		SET salary = salary + 100
		WHERE employee_id = v_ids(i);
END;
/

FORALL bulk-binds collection values to DML. For sparse collections, use the appropriate INDICES OF or VALUES OF form rather than assuming contiguous indexes.

SAVE EXCEPTIONS


DECLARE
	TYPE t_ids IS TABLE OF NUMBER;
	v_ids t_ids := t_ids(1, 2, 999999);
	e_bulk_errors EXCEPTION;
	PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
	FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
		DELETE FROM plsql_employees WHERE employee_id = v_ids(i);
EXCEPTION
	WHEN e_bulk_errors THEN
		FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
			DBMS_OUTPUT.PUT_LINE(
				'Index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
				', code ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
			);
		END LOOP;
END;
/

A missing row in the example does not itself cause a DML exception. SAVE EXCEPTIONS reports actual DML failures. Decide explicitly whether partial successes should be retained or rolled back.

Performance checklist

  • Use set-based SQL where possible.
  • Avoid queries inside loops if one query can do the work.
  • Use bulk operations for appropriate large-volume processing.
  • Select only required columns.
  • Use bind variables and suitable indexes.
  • Measure actual execution plans and timings before optimizing.
  • Choose transaction boundaries deliberately; do not commit each row by default.

17. Dates and times

Current date and timestamp


BEGIN
	DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
	DBMS_OUTPUT.PUT_LINE(
		TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM')
	);
END;
/

SYSDATE returns DATE; SYSTIMESTAMP returns a timestamp with time-zone information. Other useful functions include CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, TRUNC, and EXTRACT.

Adding a number to DATE adds days. Use ADD_MONTHS(date_value, 1) to advance by a calendar month according to Oracle's month-end rules.

Compare a whole calendar day


SELECT *
FROM plsql_employees
WHERE hire_date >= DATE '2026-10-29'
  AND hire_date <  DATE '2026-10-30';

This range pattern includes times throughout the day and can be more index-friendly than applying TRUNC to the column. For applications spanning time zones, distinguish database, session, and business time zones and convert consistently.

18. Strings, numbers, and built-in functions

String functions

FunctionPurpose
UPPER / LOWER / INITCAPChange character case.
LENGTH / SUBSTR / INSTRLength, substring, and position.
TRIM / LTRIM / RTRIMRemove selected characters.
REPLACEReplace occurrences.
LPAD / RPADPad character data.
REGEXP_REPLACE / REGEXP_SUBSTRRegular-expression replacement and extraction.

Numeric functions

FunctionPurpose
ROUND / TRUNCRound or truncate a number.
CEIL / FLOORRound upward or downward to an integer.
ABS / MODAbsolute value and remainder.
POWER / SQRTExponentiation and square root.

NULL handling


SELECT employee_name, NVL(salary, 0) AS salary
FROM plsql_employees;

SELECT COALESCE(NULL, NULL, 'Available')
FROM dual;

NVL, NVL2, and COALESCE handle missing values. Replace NULL with zero only when zero has the intended business meaning.

19. Oracle utility packages

PackageCommon purpose
DBMS_OUTPUTDevelopment messages.
DBMS_UTILITYDiagnostics and error backtraces.
DBMS_SQLAdvanced dynamic SQL.
DBMS_SCHEDULERScheduled database jobs.
DBMS_LOBLarge object operations.
DBMS_ASSERTValidation of certain SQL identifiers and inputs.
UTL_FILEServer-side file access through configured directories.
UTL_HTTPHTTP requests subject to network controls.
DBMS_APPLICATION_INFOModule and action information for monitoring.
DBMS_SESSIONSession operations.
DBMS_LOCKApplication-level coordination locks.

Scheduler example


BEGIN
	DBMS_SCHEDULER.CREATE_JOB(
		job_name        => 'DAILY_REPORT_JOB',
		job_type        => 'STORED_PROCEDURE',
		job_action      => 'GENERATE_DAILY_REPORT',
		start_date      => SYSTIMESTAMP,
		repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
		enabled         => TRUE,
		comments        => 'Generate the daily report'
	);
END;
/

This assumes the stored procedure exists and is accessible to the job owner. Verify privileges, logging, and time-zone expectations. Creating jobs generally requires appropriate privileges.

20. File handling and external integration

UTL_FILE

UTL_FILE accesses files on the database server through Oracle directory objects, not automatically on the developer's workstation. An administrator may configure a directory and grant access:


CREATE OR REPLACE DIRECTORY REPORT_DIR AS '/opt/oracle/reports';

DECLARE
	v_file UTL_FILE.FILE_TYPE;
BEGIN
	v_file := UTL_FILE.FOPEN('REPORT_DIR', 'example.txt', 'W');
	UTL_FILE.PUT_LINE(v_file, 'Report generated successfully');
	UTL_FILE.FCLOSE(v_file);
EXCEPTION
	WHEN OTHERS THEN
		IF UTL_FILE.IS_OPEN(v_file) THEN
			UTL_FILE.FCLOSE(v_file);
		END IF;
		RAISE;
END;
/

Production code should account for failures during writing and closing and decide how file errors affect the transaction.

HTTP and LOBs

UTL_HTTP can make HTTP requests when network ACLs, proxy settings, and TLS wallet configuration permit it. Use HTTPS, validate responses, and avoid exposing credentials in URLs or logs. Consider application-tier integration when remote availability should not be coupled to database transaction time.

Use CLOB for large character data and BLOB for binary content. DBMS_LOB provides operations for reading, writing, copying, and measuring LOBs; they should not always be treated like short VARCHAR2 values.

21. Advanced language features

Object types


CREATE OR REPLACE TYPE address_type AS OBJECT (
	street VARCHAR2(100),
	city VARCHAR2(50),
	postal_code VARCHAR2(20)
);
/

Object types represent structured values and can be used in supported SQL contexts, including object columns and collections. Ordinary relational tables and PL/SQL records are simpler for many applications.

Other advanced topics

  • Pipelined table functions: produce rows incrementally for SQL to consume as a table; require appropriate collection and return types.
  • Recursive subprograms: useful for naturally recursive algorithms, but consume call-stack resources.
  • Conditional compilation: includes or excludes code according to compilation settings.
  • Native compilation and optimization: can help in suitable workloads, but SQL design and avoiding unnecessary work are often more important.

Use advanced features when they solve a clear problem. Measure performance rather than assuming a particular coding style is faster.

22. Security and safe programming

Prevent SQL injection

Never concatenate untrusted values directly into dynamic SQL. Use bind variables:


EXECUTE IMMEDIATE
	'SELECT COUNT(*) FROM users WHERE username = :username'
INTO v_count
USING p_username;

Bind variables treat input as data. Dynamic identifiers need allowlists and appropriate validation.

Least privilege

  • Grant only the privileges an application needs.
  • Avoid broad administrative privileges on application accounts.
  • Review who can execute privileged procedures.
  • Restrict access to sensitive tables and packages.
  • Keep credentials out of source code and logs.

Definer's and invoker's rights

AUTHID DEFINER is the default stored-unit privilege model; AUTHID CURRENT_USER uses invoker-rights behavior subject to Oracle's rules. Definer-rights procedures can provide controlled access to tables without granting callers direct access, but must be designed carefully to avoid privilege escalation.

Validate inputs


IF p_amount IS NULL OR p_amount <= 0 THEN
	RAISE_APPLICATION_ERROR(-20020, 'Amount must be greater than zero.');
END IF;

Validate at appropriate boundaries, enforce integrity with constraints where possible, and never rely solely on client-side validation.

23. Debugging, testing, and deployment

Inspect compilation errors


SHOW ERRORS;

SELECT line, position, text
FROM user_errors
WHERE name = 'INCREASE_SALARY'
  AND type = 'PROCEDURE'
ORDER BY sequence;

SELECT object_name, object_type, status
FROM user_objects
WHERE object_name = 'INCREASE_SALARY';

Dictionary object names are commonly uppercase unless quoted identifiers were used. Check whether a stored object is VALID before relying on it.

Testing checklist

  • Test valid inputs and expected results.
  • Test missing rows, duplicate keys, NULLs, and invalid values.
  • Test rollback and commit behavior.
  • Test concurrency and locking where relevant.
  • Verify that unexpected errors propagate.
  • Test realistic data volumes and execution plans.

Deployment checklist

  • Review code and dependencies.
  • Compile and test in development first.
  • Verify privileges and security.
  • Review dynamic SQL and transaction boundaries.
  • Prepare recovery or rollback steps.
  • Deploy through version-controlled scripts.
  • Verify objects and monitor the application afterward.

24. Common errors and fixes

ErrorTypical causeWhat to check
ORA-01403NO_DATA_FOUNDQuery conditions and missing-row handling.
ORA-01422TOO_MANY_ROWSSingle-row query returns more than one row.
ORA-00001Unique constraint violationDuplicate keys and business rules.
ORA-00942Table/view absent or inaccessibleObject name, schema, privileges.
ORA-06502Value or conversion errorVariable sizes, conversions, assignments.
ORA-06512Call-stack locationRead the full error stack to locate the originating error.
ORA-06550PL/SQL parse/compile contextRead accompanying compiler message and line.
ORA-02291Parent key not foundReferenced parent row.
ORA-02292Child records existDependent rows before deleting parent.
PLS-00103Unexpected symbolKeywords, delimiters, parentheses, semicolons.
PLS-00201Identifier must be declaredSpelling, scope, declarations, privileges.

Debug systematically: read the entire error stack, locate the line, reproduce the smallest failing case, inspect variables and query results, then fix the cause rather than suppressing the exception. ORA-06512 often identifies a call-stack location, not the root cause.

25. Complete example: employee management API

This example combines packages, procedures, functions, validation, exceptions, and transaction ownership. It assumes the practice table exists.

Package specification


CREATE OR REPLACE PACKAGE employee_management AS
	PROCEDURE create_employee (
		p_name IN VARCHAR2,
		p_department_id IN NUMBER,
		p_salary IN NUMBER,
		p_employee_id OUT NUMBER
	);

	PROCEDURE increase_salary (
		p_employee_id IN NUMBER,
		p_amount IN NUMBER
	);

	FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER;
END employee_management;
/

Package body


CREATE OR REPLACE PACKAGE BODY employee_management AS
	PROCEDURE create_employee (
		p_name IN VARCHAR2,
		p_department_id IN NUMBER,
		p_salary IN NUMBER,
		p_employee_id OUT NUMBER
	) AS
	BEGIN
		IF p_name IS NULL THEN
			RAISE_APPLICATION_ERROR(-20030, 'Employee name is required.');
		END IF;

		IF p_salary IS NULL OR p_salary < 0 THEN
			RAISE_APPLICATION_ERROR(-20031, 'Salary must be non-negative.');
		END IF;

		INSERT INTO plsql_employees (employee_name, department_id, salary)
		VALUES (p_name, p_department_id, p_salary)
		RETURNING employee_id INTO p_employee_id;
	END create_employee;

	PROCEDURE increase_salary (
		p_employee_id IN NUMBER,
		p_amount IN NUMBER
	) AS
	BEGIN
		IF p_amount IS NULL OR p_amount < 0 THEN
			RAISE_APPLICATION_ERROR(-20032, 'Increase must be non-negative.');
		END IF;

		UPDATE plsql_employees
		SET salary = salary + p_amount
		WHERE employee_id = p_employee_id;

		IF SQL%ROWCOUNT = 0 THEN
			RAISE_APPLICATION_ERROR(-20033, 'Employee not found.');
		END IF;
	END increase_salary;

	FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER AS
		v_salary NUMBER;
	BEGIN
		SELECT salary INTO v_salary
		FROM plsql_employees
		WHERE employee_id = p_employee_id;
		RETURN v_salary;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			RAISE_APPLICATION_ERROR(-20034, 'Employee not found.');
	END get_salary;
END employee_management;
/

Test the package


DECLARE
	v_employee_id NUMBER;
	v_salary NUMBER;
BEGIN
	employee_management.create_employee(
		p_name => 'Mariam',
		p_department_id => 10,
		p_salary => 65000,
		p_employee_id => v_employee_id
	);

	employee_management.increase_salary(
		p_employee_id => v_employee_id,
		p_amount => 2500
	);

	v_salary := employee_management.get_salary(v_employee_id);

	DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
	DBMS_OUTPUT.PUT_LINE('Updated salary: ' || v_salary);

	COMMIT;
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
END;
/

The calling block controls the transaction. In a real application, avoid exposing sensitive information in errors and consider retry behavior after failures.

26. Best practices at a glance

  • Use SQL for set-based work: prefer one well-designed statement over row-by-row operations.
  • Use anchored types: %TYPE for columns and %ROWTYPE for row-shaped records.
  • Keep transaction boundaries clear: let callers control commits for ordinary business procedures.
  • Handle exceptions meaningfully: recover safely, report when appropriate, and re-raise unexpected failures.
  • Use bind variables: never concatenate untrusted values into dynamic SQL.
  • Organize related code into packages: separate public interfaces from private implementations.
  • Keep triggers focused: avoid hidden side effects and complex trigger chains.
  • Use bulk processing appropriately: apply BULK COLLECT and FORALL when useful.
  • Enforce data integrity: use database constraints as well as procedural validation.
  • Test failures and concurrency: test missing rows, locks, rollback, and unexpected exceptions.

Common mistakes to avoid

  • Using = instead of := for assignment.
  • Forgetting INTO in a single-row query.
  • Assuming SELECT INTO can return multiple rows.
  • Comparing NULL with =.
  • Forgetting to close manually managed cursors.
  • Committing inside every reusable procedure.
  • Suppressing all exceptions.
  • Using dynamic SQL where static SQL is simpler.
  • Fetching huge datasets into memory without batching.
  • Ignoring compilation errors.

27. Quick syntax reference

Anonymous block


DECLARE
	v_value NUMBER := 10;
BEGIN
	DBMS_OUTPUT.PUT_LINE(v_value);
EXCEPTION
	WHEN OTHERS THEN
		RAISE;
END;
/

Condition


IF condition THEN
	statements;
ELSIF other_condition THEN
	statements;
ELSE
	statements;
END IF;

Loop


FOR i IN 1..10 LOOP
	statements;
END LOOP;

Query one row


SELECT column_name
INTO v_value
FROM table_name
WHERE id = p_id;

Cursor loop


FOR r IN (SELECT column_name FROM table_name) LOOP
	DBMS_OUTPUT.PUT_LINE(r.column_name);
END LOOP;

Stored procedure and function


CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
	NULL;
END;
/

CREATE OR REPLACE FUNCTION func_name RETURN NUMBER AS
BEGIN
	RETURN 0;
END;
/

Dynamic SQL and transactions


EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_name'
INTO v_count;

SAVEPOINT before_change;
-- SQL statements
-- COMMIT;
-- ROLLBACK;
-- ROLLBACK TO before_change;

28. Suggested learning path

  1. Fundamentals: blocks, variables, constants, types, operators, and output.
  2. Control flow: IF, CASE, loops, and nested blocks.
  3. SQL integration: SELECT INTO, DML, cursor attributes, and transactions.
  4. Reusable code: procedures, functions, parameter modes, and packages.
  5. Robust programming: exceptions, diagnostics, constraints, and transaction design.
  6. Advanced data processing: cursors, records, collections, bulk SQL, and dynamic SQL.
  7. Database automation: triggers, Scheduler jobs, and utility packages.
  8. Production readiness: security, performance, automated tests, deployment, and monitoring.

Practice by creating an employee record, calculating a bonus, updating salaries, handling missing employee IDs, and then refactoring related operations into a package.

29. Official documentation and further reference

Oracle Database PL/SQL Language Reference

The definitive reference for blocks, types, collections, static and dynamic SQL, subprograms, triggers, packages, error handling, and optimization.

Use documentation matching your installed database version. Newer releases may support features that older installations do not, including changes to SQL BOOLEAN support and language syntax.


Article content

ChatGPT

Banner image

Bing, Dezynor

Article Series

Reference Guides

Categories

Programming & Software, Data & Databases

Created: 17/Nov/2026 – 03:47pm
Updated: 17/Nov/2026 – 04:59pm