PL/SQL online editor

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor for free. It's one of the robust, feature-rich online editors for Oracle PL/SQL running on Oracle 23c. Getting started with the OneCompiler's PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension for SQL. It combines SQL data manipulation with procedural programming constructs like variables, conditions, loops, and exception handling. PL/SQL code runs directly within the Oracle Database engine, enabling efficient execution of complex business logic without network round trips.

Syntax help

Anonymous Blocks

Anonymous blocks are unnamed PL/SQL code units that execute immediately. Every PL/SQL block has three sections: DECLARE (optional) for variables, BEGIN for executable statements, and EXCEPTION (optional) for error handling. Use anonymous blocks for quick scripts, testing, and one-time operations that don't need to be stored in the database.

-- Basic anonymous block
DECLARE
   message VARCHAR2(100) := 'Hello, World!';
BEGIN
   DBMS_OUTPUT.PUT_LINE(message);
END;
/

-- Block with calculations
DECLARE
  x NUMBER := 10;
  y NUMBER := 5;
  result NUMBER;
BEGIN
  result := x + y;
  DBMS_OUTPUT.PUT_LINE('Sum: ' || result);
  result := x * y;
  DBMS_OUTPUT.PUT_LINE('Product: ' || result);
END;
/

Variables and Data Types

PL/SQL supports various data types including NUMBER, VARCHAR2, DATE, BOOLEAN, and composite types. Variables must be declared in the DECLARE section before use. Use %TYPE to inherit a column's data type and %ROWTYPE for entire row structures, ensuring your code stays synchronized with table schema changes.

DECLARE
  emp_name VARCHAR2(50) := 'John Smith';
  emp_salary NUMBER(10,2) := 75000.00;
  hire_date DATE := SYSDATE;
  is_active BOOLEAN := TRUE;
  counter PLS_INTEGER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);
  DBMS_OUTPUT.PUT_LINE('Salary: $' || emp_salary);
  DBMS_OUTPUT.PUT_LINE('Hired: ' || TO_CHAR(hire_date, 'YYYY-MM-DD'));

  IF is_active THEN
    DBMS_OUTPUT.PUT_LINE('Status: Active');
  END IF;
END;
/

Conditional Statements

PL/SQL provides IF-THEN-ELSE and CASE statements for conditional logic. IF statements can be nested and combined with ELSIF for multiple conditions. CASE statements offer cleaner syntax when comparing a single expression against multiple values. Both are essential for implementing business rules and decision-making logic.

DECLARE
  score NUMBER := 85;
  grade VARCHAR2(1);
BEGIN
  -- IF-THEN-ELSIF-ELSE
  IF score >= 90 THEN
    grade := 'A';
  ELSIF score >= 80 THEN
    grade := 'B';
  ELSIF score >= 70 THEN
    grade := 'C';
  ELSIF score >= 60 THEN
    grade := 'D';
  ELSE
    grade := 'F';
  END IF;

  DBMS_OUTPUT.PUT_LINE('Score: ' || score || ', Grade: ' || grade);
END;
/

-- CASE expression
DECLARE
  day_num NUMBER := TO_CHAR(SYSDATE, 'D');
  day_name VARCHAR2(20);
BEGIN
  day_name := CASE day_num
    WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
  END;

  DBMS_OUTPUT.PUT_LINE('Today is ' || day_name);
END;
/

Loops

PL/SQL offers three loop types: basic LOOP with EXIT, WHILE loop, and FOR loop. Basic loops continue until an explicit EXIT condition. WHILE loops check the condition before each iteration. FOR loops are ideal for known iteration counts and automatically declare and increment the loop variable.

-- FOR loop (most common)
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
  END LOOP;
END;
/

-- FOR loop in reverse
BEGIN
  FOR i IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
  END LOOP;
END;
/

-- WHILE loop
DECLARE
  counter NUMBER := 1;
BEGIN
  WHILE counter <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
    counter := counter + 1;
  END LOOP;
END;
/

-- Basic LOOP with EXIT
DECLARE
  n NUMBER := 0;
BEGIN
  LOOP
    n := n + 1;
    DBMS_OUTPUT.PUT_LINE('N = ' || n);
    EXIT WHEN n >= 5;
  END LOOP;
END;
/

Functions

Functions are named PL/SQL blocks that return a single value. They can accept parameters and are called from SQL statements or other PL/SQL code. Functions must have a RETURN clause specifying the return data type. Use functions for calculations, data transformations, and reusable logic that produces a single result.

-- Create a function
CREATE OR REPLACE FUNCTION calculate_bonus(
  salary IN NUMBER,
  performance_rating IN NUMBER
) RETURN NUMBER
IS
  bonus NUMBER;
BEGIN
  bonus := salary * (performance_rating / 100);
  RETURN bonus;
END calculate_bonus;
/

-- Use the function
DECLARE
  emp_salary NUMBER := 50000;
  rating NUMBER := 15;
  emp_bonus NUMBER;
BEGIN
  emp_bonus := calculate_bonus(emp_salary, rating);
  DBMS_OUTPUT.PUT_LINE('Salary: $' || emp_salary);
  DBMS_OUTPUT.PUT_LINE('Performance Rating: ' || rating || '%');
  DBMS_OUTPUT.PUT_LINE('Bonus: $' || emp_bonus);
END;
/

Procedures

Procedures are named PL/SQL blocks that perform actions but don't return values directly. They can have IN (input), OUT (output), and IN OUT (bidirectional) parameters. Procedures are ideal for complex operations, data modifications, and business processes that perform multiple steps or return multiple values through OUT parameters.

-- Create a procedure
CREATE OR REPLACE PROCEDURE greet_user(
  user_name IN VARCHAR2,
  greeting OUT VARCHAR2
)
IS
BEGIN
  greeting := 'Hello, ' || user_name || '! Welcome to PL/SQL.';
END greet_user;
/

-- Call the procedure
DECLARE
  message VARCHAR2(100);
BEGIN
  greet_user('Alice', message);
  DBMS_OUTPUT.PUT_LINE(message);
END;
/

-- Procedure with IN OUT parameter
CREATE OR REPLACE PROCEDURE double_value(
  num IN OUT NUMBER
)
IS
BEGIN
  num := num * 2;
END double_value;
/

DECLARE
  my_number NUMBER := 25;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before: ' || my_number);
  double_value(my_number);
  DBMS_OUTPUT.PUT_LINE('After: ' || my_number);
END;
/

Exception Handling

Exception handling catches and manages runtime errors gracefully. The EXCEPTION section catches specific named exceptions (like NO_DATA_FOUND, TOO_MANY_ROWS) or uses WHEN OTHERS for all errors. SQLERRM and SQLCODE provide error details. Proper exception handling prevents program crashes and enables meaningful error messages or recovery actions.

-- Basic exception handling
DECLARE
  result NUMBER;
BEGIN
  result := 10 / 0;
  DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero!');
END;
/

-- Multiple exception handlers
DECLARE
  v_name VARCHAR2(10);
BEGIN
  v_name := 'This string is too long for the variable';
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error: Value too large for variable');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
END;
/

-- User-defined exception
DECLARE
  age NUMBER := 15;
  invalid_age EXCEPTION;
BEGIN
  IF age < 18 THEN
    RAISE invalid_age;
  END IF;
  DBMS_OUTPUT.PUT_LINE('Access granted');
EXCEPTION
  WHEN invalid_age THEN
    DBMS_OUTPUT.PUT_LINE('Error: Must be 18 or older');
END;
/

Cursors

Cursors process query results row by row. Implicit cursors are created automatically for single-row queries. Explicit cursors give you control over multi-row result sets with OPEN, FETCH, and CLOSE operations. Cursor FOR loops provide the cleanest syntax by automatically handling cursor lifecycle. Use cursors when you need to process records individually.

-- Implicit cursor with SQL%ROWCOUNT
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE dept = 'Sales';
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
  ROLLBACK;
END;
/

-- Explicit cursor with FOR loop (recommended)
DECLARE
  CURSOR emp_cursor IS
    SELECT empId, name, salary FROM employees WHERE dept = 'Sales';
BEGIN
  FOR emp_rec IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.name || ': $' || emp_rec.salary);
  END LOOP;
END;
/

-- Cursor with parameters
DECLARE
  CURSOR dept_cursor(p_dept VARCHAR2) IS
    SELECT name, salary FROM employees WHERE dept = p_dept;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Sales Department:');
  FOR emp IN dept_cursor('Sales') LOOP
    DBMS_OUTPUT.PUT_LINE('  ' || emp.name || ': $' || emp.salary);
  END LOOP;
END;
/

Records and Collections

Records group related data items into a single unit, similar to structs. Collections (VARRAYs, nested tables, associative arrays) store multiple elements of the same type. Use %ROWTYPE to create records matching table structure. Collections are essential for bulk operations and passing multiple values between procedures.

-- Record with %ROWTYPE
DECLARE
  emp_rec employees%ROWTYPE;
BEGIN
  SELECT * INTO emp_rec FROM employees WHERE empId = 1;
  DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.name);
  DBMS_OUTPUT.PUT_LINE('Dept: ' || emp_rec.dept);
  DBMS_OUTPUT.PUT_LINE('Salary: $' || emp_rec.salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

-- Associative array (index-by table)
DECLARE
  TYPE name_table IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
  names name_table;
BEGIN
  names(1) := 'Alice';
  names(2) := 'Bob';
  names(3) := 'Charlie';

  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Person ' || i || ': ' || names(i));
  END LOOP;
END;
/

Dynamic SQL

Dynamic SQL builds and executes SQL statements at runtime using EXECUTE IMMEDIATE. It's useful when table names, column names, or entire queries are determined dynamically. Bind variables (using placeholders) should be used to prevent SQL injection and improve performance. Dynamic SQL enables flexible, generic database operations.

-- Simple dynamic SQL
DECLARE
  table_name VARCHAR2(30) := 'employees';
  row_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name INTO row_count;
  DBMS_OUTPUT.PUT_LINE('Rows in ' || table_name || ': ' || row_count);
END;
/

-- Dynamic SQL with bind variables
DECLARE
  dept_name VARCHAR2(30) := 'Sales';
  emp_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM employees WHERE dept = :1'
    INTO emp_count
    USING dept_name;
  DBMS_OUTPUT.PUT_LINE('Employees in ' || dept_name || ': ' || emp_count);
END;
/

Bulk Operations

Bulk operations (BULK COLLECT and FORALL) dramatically improve performance by reducing context switches between PL/SQL and SQL engines. BULK COLLECT fetches multiple rows into collections in one operation. FORALL executes DML statements for all collection elements in a single pass. Use bulk operations whenever processing large datasets.

-- BULK COLLECT example
DECLARE
  TYPE name_list IS TABLE OF employees.name%TYPE;
  names name_list;
BEGIN
  SELECT name BULK COLLECT INTO names FROM employees;

  DBMS_OUTPUT.PUT_LINE('Total employees: ' || names.COUNT);
  FOR i IN 1..names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || '. ' || names(i));
  END LOOP;
END;
/