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
| SQL | PL/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
| Type | Purpose |
|---|---|
NUMBER(p,s) | Integer or decimal numeric values. |
PLS_INTEGER | Efficient integer arithmetic in PL/SQL. |
VARCHAR2(n) | Variable-length character data. |
CHAR(n) | Fixed-length character data. |
BOOLEAN | Logical values: TRUE, FALSE, NULL. SQL support depends on database version. |
DATE | Date and time to seconds. |
TIMESTAMP | Date/time with fractional seconds; variants support time zones. |
CLOB / BLOB | Large character / binary data. |
RAW(n) | Binary data. |
INTERVAL | Duration 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
| Category | Operators |
|---|---|
| Arithmetic | + - * / ** |
| Comparison | = <> != > < >= <= |
| Logical | AND 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;
| Loop | Best use |
|---|---|
LOOP | Repeat until an explicit exit. |
WHILE | Repeat while a condition remains true. |
Numeric FOR | Known integer range. |
Cursor FOR | Process 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
| Attribute | Meaning |
|---|---|
SQL%FOUND | Whether the latest SQL statement affected or returned at least one row. |
SQL%NOTFOUND | Whether it affected or returned no rows. |
SQL%ROWCOUNT | Number of rows affected or returned. |
SQL%ISOPEN | Always 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
| Exception | Meaning |
|---|---|
NO_DATA_FOUND | Single-row query returned no rows. |
TOO_MANY_ROWS | Single-row query returned multiple rows. |
ZERO_DIVIDE | Division by zero. |
DUP_VAL_ON_INDEX | Duplicate key violates a unique constraint. |
INVALID_NUMBER | Character-to-number conversion failed in a relevant context. |
VALUE_ERROR | Value, conversion, or size constraint error. |
INVALID_CURSOR | Invalid 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
| Mode | Purpose |
|---|---|
IN | Pass input; default mode. |
OUT | Return a value through a parameter. |
IN OUT | Pass 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
BEFOREtriggers run before the triggering operation completes; row-level BEFORE triggers can assign to supported:NEWfields.AFTERtriggers run after the relevant row or statement operation at the corresponding timing point.FOR EACH ROWcreates 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
| Function | Purpose |
|---|---|
| UPPER / LOWER / INITCAP | Change character case. |
| LENGTH / SUBSTR / INSTR | Length, substring, and position. |
| TRIM / LTRIM / RTRIM | Remove selected characters. |
| REPLACE | Replace occurrences. |
| LPAD / RPAD | Pad character data. |
| REGEXP_REPLACE / REGEXP_SUBSTR | Regular-expression replacement and extraction. |
Numeric functions
| Function | Purpose |
|---|---|
| ROUND / TRUNC | Round or truncate a number. |
| CEIL / FLOOR | Round upward or downward to an integer. |
| ABS / MOD | Absolute value and remainder. |
| POWER / SQRT | Exponentiation 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
| Package | Common purpose |
|---|---|
DBMS_OUTPUT | Development messages. |
DBMS_UTILITY | Diagnostics and error backtraces. |
DBMS_SQL | Advanced dynamic SQL. |
DBMS_SCHEDULER | Scheduled database jobs. |
DBMS_LOB | Large object operations. |
DBMS_ASSERT | Validation of certain SQL identifiers and inputs. |
UTL_FILE | Server-side file access through configured directories. |
UTL_HTTP | HTTP requests subject to network controls. |
DBMS_APPLICATION_INFO | Module and action information for monitoring. |
DBMS_SESSION | Session operations. |
DBMS_LOCK | Application-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
| Error | Typical cause | What to check |
|---|---|---|
| ORA-01403 | NO_DATA_FOUND | Query conditions and missing-row handling. |
| ORA-01422 | TOO_MANY_ROWS | Single-row query returns more than one row. |
| ORA-00001 | Unique constraint violation | Duplicate keys and business rules. |
| ORA-00942 | Table/view absent or inaccessible | Object name, schema, privileges. |
| ORA-06502 | Value or conversion error | Variable sizes, conversions, assignments. |
| ORA-06512 | Call-stack location | Read the full error stack to locate the originating error. |
| ORA-06550 | PL/SQL parse/compile context | Read accompanying compiler message and line. |
| ORA-02291 | Parent key not found | Referenced parent row. |
| ORA-02292 | Child records exist | Dependent rows before deleting parent. |
| PLS-00103 | Unexpected symbol | Keywords, delimiters, parentheses, semicolons. |
| PLS-00201 | Identifier must be declared | Spelling, 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
- Fundamentals: blocks, variables, constants, types, operators, and output.
- Control flow: IF, CASE, loops, and nested blocks.
- SQL integration: SELECT INTO, DML, cursor attributes, and transactions.
- Reusable code: procedures, functions, parameter modes, and packages.
- Robust programming: exceptions, diagnostics, constraints, and transaction design.
- Advanced data processing: cursors, records, collections, bulk SQL, and dynamic SQL.
- Database automation: triggers, Scheduler jobs, and utility packages.
- 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.
- Oracle AI Database 26ai PL/SQL Language Reference
- Oracle Database 19c PL/SQL Language Reference
- Oracle SQL Language Reference
- Oracle SQL Developer
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.


