Oracle PL/SQL Core Knowledge
Full Reference: See advanced.md for pipelined table functions, packages, collections, BULK COLLECT/FORALL, compound triggers, and advanced cursors.
Deep Knowledge: Use mcp__documentation__fetch_docs with technology: oracle for comprehensive documentation.
Basic Structure
DECLARE -- Variable declarations v_count NUMBER := 0; BEGIN -- Executable statements DBMS_OUTPUT.PUT_LINE('Hello World'); EXCEPTION WHEN OTHERS THEN -- Exception handling DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END; /
Procedures
Basic Procedure
CREATE OR REPLACE PROCEDURE update_salary( p_employee_id IN NUMBER, p_percentage IN NUMBER ) IS v_current_salary NUMBER; BEGIN SELECT salary INTO v_current_salary FROM employees WHERE employee_id = p_employee_id;
UPDATE employees
SET salary = salary * (1 + p_percentage / 100)
WHERE employee_id = p_employee_id;
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_employee_id); END update_salary; /
-- Execute EXEC update_salary(100, 10); -- or BEGIN update_salary(100, 10); END; /
Procedure with OUT Parameters
CREATE OR REPLACE PROCEDURE get_employee_info( p_employee_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER, p_dept_name OUT VARCHAR2 ) IS BEGIN SELECT e.first_name || ' ' || e.last_name, e.salary, d.department_name INTO p_name, p_salary, p_dept_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id = p_employee_id; END; /
-- Call with OUT parameters DECLARE v_name VARCHAR2(100); v_salary NUMBER; v_dept VARCHAR2(100); BEGIN get_employee_info(100, v_name, v_salary, v_dept); DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary); END; /
Functions
Scalar Function
CREATE OR REPLACE FUNCTION calculate_bonus( p_salary IN NUMBER, p_years IN NUMBER ) RETURN NUMBER DETERMINISTIC -- Same inputs always return same output IS v_bonus NUMBER; BEGIN IF p_years >= 10 THEN v_bonus := p_salary * 0.15; ELSIF p_years >= 5 THEN v_bonus := p_salary * 0.10; ELSE v_bonus := p_salary * 0.05; END IF;
RETURN v_bonus;
END; /
-- Usage in SQL SELECT employee_id, salary, calculate_bonus(salary, years_of_service) as bonus FROM employees;
Cursors
Implicit Cursor
BEGIN UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10; DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows found');
END IF;
END; /
Explicit Cursor
DECLARE CURSOR emp_cursor IS SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10;
v_emp emp_cursor%ROWTYPE;
BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_emp; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary); END LOOP; CLOSE emp_cursor; END; /
Cursor FOR Loop (Preferred)
BEGIN FOR emp_rec IN (SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10) LOOP DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary); END LOOP; END; /
Collections
Associative Array (INDEX BY)
DECLARE TYPE salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE name_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);
salaries salary_tab;
names name_tab;
BEGIN salaries(1) := 50000; salaries(2) := 60000;
names('EMP001') := 'John Doe';
names('EMP002') := 'Jane Smith';
DBMS_OUTPUT.PUT_LINE(salaries(1));
DBMS_OUTPUT.PUT_LINE(names('EMP001'));
END; /
Exception Handling
Predefined Exceptions
Exception Description
NO_DATA_FOUND
SELECT INTO returned no rows
TOO_MANY_ROWS
SELECT INTO returned multiple rows
ZERO_DIVIDE
Division by zero
VALUE_ERROR
Numeric or value error
INVALID_CURSOR
Invalid cursor operation
DUP_VAL_ON_INDEX
Duplicate value on unique index
Exception Handling
DECLARE v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee not found'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Multiple employees found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM); RAISE; END; /
RAISE_APPLICATION_ERROR
BEGIN IF some_condition THEN RAISE_APPLICATION_ERROR(-20001, 'Custom error message'); END IF; END; /
Triggers
Row-Level Trigger
CREATE OR REPLACE TRIGGER trg_emp_salary_check BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative'); END IF;
IF :NEW.salary > 1000000 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum');
END IF;
END; /
Best Practices
DO
-
Use packages to organize related code
-
Use BULK COLLECT and FORALL for large datasets
-
Use cursor FOR loops (auto open/close)
-
Define exceptions at package level
-
Use %TYPE and %ROWTYPE for type safety
-
Use bind variables to prevent SQL injection
DON'T
-
Use implicit commits in triggers
-
Ignore exceptions
-
Use SELECT INTO without handling NO_DATA_FOUND
-
Create excessive triggers (performance impact)
When NOT to Use This Skill
-
Basic Oracle SQL - Use oracle skill for queries, data types, partitioning
-
PL/pgSQL (PostgreSQL) - Use plpgsql skill for PostgreSQL procedures
-
T-SQL (SQL Server) - Use tsql skill for SQL Server procedures
-
Basic SQL - Use sql-fundamentals for ANSI SQL basics
Anti-Patterns
Anti-Pattern Problem Solution
Not using BULK COLLECT Row-by-row processing Use BULK COLLECT for large datasets
SELECT INTO without exception Runtime errors Handle NO_DATA_FOUND
Not using packages Code disorganization Organize related code in packages
Excessive triggers Performance issues Minimize trigger logic
WHEN OTHERS without RAISE Silent failures Re-raise or log exceptions
Implicit cursors for large sets Memory issues Use explicit cursors with LIMIT
Quick Troubleshooting
Problem Diagnostic Fix
NO_DATA_FOUND SELECT INTO with no rows Add exception handler
TOO_MANY_ROWS SELECT INTO with multiple rows Add WHERE or use cursor
ORA-06502 numeric error Type conversion failure Check data types, use TO_NUMBER
Slow procedure DBMS_PROFILER Use BULK operations
Package state lost Session reset Use PRAGMA SERIALLY_REUSABLE or re-initialize
Reference Documentation
-
Procedures
-
Functions
-
Triggers
-
Packages
-
Cursors