plsql — Skillopedia

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 with technology: for comprehensive documentation. Basic Structure Procedures Basic Procedure Procedure with OUT Parameters Functions Scalar Function Cursors Implicit Cursor Explicit Cursor Cursor FOR Loop (Preferred) Collections Associative Array (INDEX BY) Exception Handling Predefined Exceptions | Exception | Description | |-----------|-------------| | | SELECT INTO returned no rows | | | SELECT…

);\nEND;\n/\n```\n\n### Lookup Function\n\n```sql\nCREATE OR REPLACE FUNCTION get_department_name(p_dept_id NUMBER)\nRETURN VARCHAR2\nRESULT_CACHE RELIES_ON (departments)\nIS\n v_name VARCHAR2(100);\nBEGIN\n SELECT department_name INTO v_name\n FROM departments WHERE department_id = p_dept_id;\n RETURN v_name;\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN NULL;\nEND;\n/\n```\n\n### Aggregation Function\n\n```sql\nCREATE OR REPLACE FUNCTION get_dept_stats(p_dept_id NUMBER)\nRETURN VARCHAR2\nIS\n v_count NUMBER;\n v_total NUMBER;\n v_avg NUMBER;\nBEGIN\n SELECT COUNT(*), SUM(salary), AVG(salary)\n INTO v_count, v_total, v_avg\n FROM employees WHERE department_id = p_dept_id;\n\n RETURN 'Count: ' || v_count ||\n ', Total: ' || TO_CHAR(v_total, 'FM$999,999') ||\n ', Avg: ' || TO_CHAR(v_avg, 'FM$999,999');\nEND;\n/\n```\n\n## Drop Function\n\n```sql\nDROP FUNCTION function_name;\n```\n\n## View Function Code\n\n```sql\nSELECT text\nFROM user_source\nWHERE name = 'FUNCTION_NAME'\nAND type = 'FUNCTION'\nORDER BY line;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5972,"content_sha256":"b12924cf696751753a64e38951b8a932cdb5cc4b06f4971038f89c8d9b29a11c"},{"filename":"quick-ref/packages.md","content":"# PL/SQL Packages Quick Reference\n\n## Package Structure\n\n```\nPackage = Specification (public interface) + Body (implementation)\n```\n\n## Package Specification\n\n```sql\nCREATE [OR REPLACE] PACKAGE package_name\nAS | IS\n -- Public type declarations\n TYPE type_name IS ...;\n\n -- Public constant declarations\n constant_name CONSTANT datatype := value;\n\n -- Public variable declarations\n variable_name datatype;\n\n -- Public cursor declarations\n CURSOR cursor_name IS SELECT ...;\n\n -- Public exception declarations\n exception_name EXCEPTION;\n\n -- Public procedure declarations\n PROCEDURE procedure_name(parameters);\n\n -- Public function declarations\n FUNCTION function_name(parameters) RETURN datatype;\n\nEND [package_name];\n/\n```\n\n## Package Body\n\n```sql\nCREATE [OR REPLACE] PACKAGE BODY package_name\nAS | IS\n -- Private type declarations\n -- Private constants\n -- Private variables\n -- Private cursors\n -- Private procedures/functions\n\n -- Public procedure implementations\n PROCEDURE procedure_name(parameters)\n IS\n BEGIN\n -- implementation\n END procedure_name;\n\n -- Public function implementations\n FUNCTION function_name(parameters) RETURN datatype\n IS\n BEGIN\n RETURN value;\n END function_name;\n\n-- Package initialization (optional)\nBEGIN\n -- Runs once when package first loaded\nEND [package_name];\n/\n```\n\n## Complete Example\n\n```sql\n-- Specification\nCREATE OR REPLACE PACKAGE employee_api AS\n -- Types\n TYPE emp_rec IS RECORD (\n id NUMBER,\n full_name VARCHAR2(200),\n salary NUMBER,\n dept_name VARCHAR2(100)\n );\n TYPE emp_tab IS TABLE OF emp_rec;\n\n -- Constants\n c_max_salary CONSTANT NUMBER := 500000;\n c_min_salary CONSTANT NUMBER := 30000;\n\n -- Exceptions\n e_invalid_salary EXCEPTION;\n PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);\n\n -- Procedures\n PROCEDURE hire_employee(\n p_first_name IN VARCHAR2,\n p_last_name IN VARCHAR2,\n p_email IN VARCHAR2,\n p_salary IN NUMBER,\n p_dept_id IN NUMBER,\n p_employee_id OUT NUMBER\n );\n\n PROCEDURE terminate_employee(p_employee_id IN NUMBER);\n\n PROCEDURE give_raise(\n p_employee_id IN NUMBER,\n p_percentage IN NUMBER\n );\n\n -- Functions\n FUNCTION get_employee(p_employee_id NUMBER) RETURN emp_rec;\n FUNCTION get_dept_employees(p_dept_id NUMBER) RETURN emp_tab PIPELINED;\n FUNCTION get_total_salary(p_dept_id NUMBER) RETURN NUMBER;\n\nEND employee_api;\n/\n\n-- Body\nCREATE OR REPLACE PACKAGE BODY employee_api AS\n\n -- Private variables\n g_last_employee_id NUMBER;\n\n -- Private procedures\n PROCEDURE log_action(p_action VARCHAR2, p_employee_id NUMBER) IS\n PRAGMA AUTONOMOUS_TRANSACTION;\n BEGIN\n INSERT INTO audit_log (action, employee_id, action_date, action_by)\n VALUES (p_action, p_employee_id, SYSDATE, USER);\n COMMIT;\n END log_action;\n\n PROCEDURE validate_salary(p_salary NUMBER) IS\n BEGIN\n IF p_salary \u003c c_min_salary OR p_salary > c_max_salary THEN\n RAISE e_invalid_salary;\n END IF;\n END validate_salary;\n\n -- Public implementations\n PROCEDURE hire_employee(\n p_first_name IN VARCHAR2,\n p_last_name IN VARCHAR2,\n p_email IN VARCHAR2,\n p_salary IN NUMBER,\n p_dept_id IN NUMBER,\n p_employee_id OUT NUMBER\n ) IS\n BEGIN\n validate_salary(p_salary);\n\n INSERT INTO employees (\n employee_id, first_name, last_name, email,\n salary, department_id, hire_date\n ) VALUES (\n emp_seq.NEXTVAL, p_first_name, p_last_name, p_email,\n p_salary, p_dept_id, SYSDATE\n ) RETURNING employee_id INTO p_employee_id;\n\n g_last_employee_id := p_employee_id;\n log_action('HIRE', p_employee_id);\n COMMIT;\n EXCEPTION\n WHEN e_invalid_salary THEN\n RAISE_APPLICATION_ERROR(-20001,\n 'Salary must be between ' || c_min_salary || ' and ' || c_max_salary);\n END hire_employee;\n\n PROCEDURE terminate_employee(p_employee_id IN NUMBER) IS\n BEGIN\n UPDATE employees\n SET termination_date = SYSDATE,\n status = 'TERMINATED'\n WHERE employee_id = p_employee_id;\n\n IF SQL%ROWCOUNT = 0 THEN\n RAISE_APPLICATION_ERROR(-20002, 'Employee not found');\n END IF;\n\n log_action('TERMINATE', p_employee_id);\n COMMIT;\n END terminate_employee;\n\n PROCEDURE give_raise(\n p_employee_id IN NUMBER,\n p_percentage IN NUMBER\n ) IS\n v_new_salary NUMBER;\n BEGIN\n SELECT salary * (1 + p_percentage / 100)\n INTO v_new_salary\n FROM employees WHERE employee_id = p_employee_id;\n\n validate_salary(v_new_salary);\n\n UPDATE employees\n SET salary = v_new_salary\n WHERE employee_id = p_employee_id;\n\n log_action('RAISE', p_employee_id);\n COMMIT;\n END give_raise;\n\n FUNCTION get_employee(p_employee_id NUMBER) RETURN emp_rec IS\n v_result emp_rec;\n BEGIN\n SELECT e.employee_id,\n e.first_name || ' ' || e.last_name,\n e.salary,\n d.department_name\n INTO v_result.id, v_result.full_name, v_result.salary, v_result.dept_name\n FROM employees e\n JOIN departments d ON e.department_id = d.department_id\n WHERE e.employee_id = p_employee_id;\n\n RETURN v_result;\n EXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN NULL;\n END get_employee;\n\n FUNCTION get_dept_employees(p_dept_id NUMBER) RETURN emp_tab PIPELINED IS\n BEGIN\n FOR rec IN (\n SELECT e.employee_id,\n e.first_name || ' ' || e.last_name AS full_name,\n e.salary,\n d.department_name\n FROM employees e\n JOIN departments d ON e.department_id = d.department_id\n WHERE e.department_id = p_dept_id\n ) LOOP\n PIPE ROW(rec);\n END LOOP;\n RETURN;\n END get_dept_employees;\n\n FUNCTION get_total_salary(p_dept_id NUMBER) RETURN NUMBER IS\n v_total NUMBER;\n BEGIN\n SELECT NVL(SUM(salary), 0) INTO v_total\n FROM employees WHERE department_id = p_dept_id;\n RETURN v_total;\n END get_total_salary;\n\n-- Package initialization\nBEGIN\n g_last_employee_id := 0;\nEND employee_api;\n/\n```\n\n## Using Packages\n\n```sql\n-- Call procedure\nBEGIN\n employee_api.give_raise(100, 10);\nEND;\n/\n\n-- Call function in SQL\nSELECT employee_api.get_total_salary(10) FROM DUAL;\n\n-- Use type\nDECLARE\n v_emp employee_api.emp_rec;\nBEGIN\n v_emp := employee_api.get_employee(100);\n DBMS_OUTPUT.PUT_LINE(v_emp.full_name);\nEND;\n/\n\n-- Use pipelined function\nSELECT * FROM TABLE(employee_api.get_dept_employees(10));\n\n-- Access constant\nDECLARE\n v_max NUMBER := employee_api.c_max_salary;\nBEGIN\n NULL;\nEND;\n/\n```\n\n## Package State\n\n```sql\n-- Package variables persist for session duration\nCREATE OR REPLACE PACKAGE counter_pkg AS\n PROCEDURE increment;\n FUNCTION get_count RETURN NUMBER;\nEND;\n/\n\nCREATE OR REPLACE PACKAGE BODY counter_pkg AS\n g_count NUMBER := 0; -- Session-level state\n\n PROCEDURE increment IS\n BEGIN\n g_count := g_count + 1;\n END;\n\n FUNCTION get_count RETURN NUMBER IS\n BEGIN\n RETURN g_count;\n END;\nEND;\n/\n\n-- Different sessions have independent state\n```\n\n## SERIALLY_REUSABLE\n\n```sql\n-- Reset package state after each call\nCREATE OR REPLACE PACKAGE stateless_pkg AS\n PRAGMA SERIALLY_REUSABLE;\n g_counter NUMBER := 0;\n PROCEDURE do_something;\nEND;\n/\n```\n\n## Package Dependencies\n\n```sql\n-- Check dependencies\nSELECT * FROM user_dependencies\nWHERE name = 'PACKAGE_NAME';\n\n-- Recompile package\nALTER PACKAGE package_name COMPILE;\nALTER PACKAGE package_name COMPILE BODY;\nALTER PACKAGE package_name COMPILE SPECIFICATION;\n```\n\n## Drop Package\n\n```sql\n-- Drop entire package\nDROP PACKAGE package_name;\n\n-- Drop only body (keep specification)\nDROP PACKAGE BODY package_name;\n```\n\n## View Package Code\n\n```sql\n-- Specification\nSELECT text FROM user_source\nWHERE name = 'PACKAGE_NAME' AND type = 'PACKAGE'\nORDER BY line;\n\n-- Body\nSELECT text FROM user_source\nWHERE name = 'PACKAGE_NAME' AND type = 'PACKAGE BODY'\nORDER BY line;\n```\n\n## Best Practices\n\n1. **One package per business domain** - Group related functionality\n2. **Keep specification minimal** - Only expose what's necessary\n3. **Use private procedures** - Hide implementation details\n4. **Initialize in body** - Use package initialization block\n5. **Document public interface** - Add comments in specification\n6. **Use constants** - Define magic numbers as package constants\n7. **Handle exceptions** - Wrap exceptions with meaningful messages\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":8864,"content_sha256":"b720783b7c4965ff4db0c9bdb82d571ba7a29ac2fe08a63275c12ffd58f96fd6"},{"filename":"quick-ref/procedures.md","content":"# PL/SQL Procedures Quick Reference\n\n## Basic Syntax\n\n```sql\nCREATE [OR REPLACE] PROCEDURE procedure_name\n [(parameter1 [IN | OUT | IN OUT] datatype [DEFAULT value],\n parameter2 [IN | OUT | IN OUT] datatype [DEFAULT value], ...)]\nIS | AS\n -- Declaration section\nBEGIN\n -- Executable section\n[EXCEPTION\n -- Exception handling section]\nEND [procedure_name];\n/\n```\n\n## Parameter Modes\n\n| Mode | Description | Default |\n|------|-------------|---------|\n| `IN` | Read-only, passed by value | Yes |\n| `OUT` | Write-only, returns value | No |\n| `IN OUT` | Read-write | No |\n\n```sql\nCREATE OR REPLACE PROCEDURE example_proc(\n p_input IN VARCHAR2, -- Input only\n p_output OUT NUMBER, -- Output only\n p_inout IN OUT VARCHAR2, -- Both\n p_default IN VARCHAR2 DEFAULT 'X' -- With default\n)\nIS\nBEGIN\n p_output := LENGTH(p_input);\n p_inout := p_inout || '-modified';\nEND;\n/\n```\n\n## Calling Procedures\n\n```sql\n-- Positional notation\nEXEC my_procedure(100, 'test', v_result);\n\n-- Named notation\nEXEC my_procedure(p_id => 100, p_name => 'test', p_result => v_result);\n\n-- Mixed notation\nEXEC my_procedure(100, p_name => 'test', p_result => v_result);\n\n-- From PL/SQL block\nBEGIN\n my_procedure(100, 'test', v_result);\nEND;\n/\n```\n\n## NOCOPY Hint (Performance)\n\n```sql\nCREATE OR REPLACE PROCEDURE process_large_data(\n p_data IN OUT NOCOPY large_collection_type\n)\nIS\nBEGIN\n -- NOCOPY passes by reference instead of value\n -- Faster for large parameters but no rollback on exception\n NULL;\nEND;\n/\n```\n\n## Autonomous Transactions\n\n```sql\nCREATE OR REPLACE PROCEDURE log_error(\n p_error_msg IN VARCHAR2\n)\nIS\n PRAGMA AUTONOMOUS_TRANSACTION;\nBEGIN\n INSERT INTO error_log (message, created_at)\n VALUES (p_error_msg, SYSDATE);\n COMMIT; -- Commits only this transaction\nEND;\n/\n\n-- Usage: Log persists even if main transaction rolls back\nBEGIN\n -- Some operation\n UPDATE accounts SET balance = balance - 100 WHERE id = 1;\n\n IF some_error THEN\n log_error('Error occurred'); -- This commits independently\n ROLLBACK; -- Main transaction rolls back\n END IF;\nEND;\n/\n```\n\n## Procedure with REF CURSOR\n\n```sql\nCREATE OR REPLACE PROCEDURE get_employees(\n p_dept_id IN NUMBER,\n p_cursor OUT SYS_REFCURSOR\n)\nIS\nBEGIN\n OPEN p_cursor FOR\n SELECT employee_id, first_name, last_name, salary\n FROM employees\n WHERE department_id = p_dept_id;\nEND;\n/\n\n-- Usage\nDECLARE\n v_cursor SYS_REFCURSOR;\n v_emp_id NUMBER;\n v_name VARCHAR2(100);\nBEGIN\n get_employees(10, v_cursor);\n LOOP\n FETCH v_cursor INTO v_emp_id, v_name;\n EXIT WHEN v_cursor%NOTFOUND;\n DBMS_OUTPUT.PUT_LINE(v_name);\n END LOOP;\n CLOSE v_cursor;\nEND;\n/\n```\n\n## Overloading\n\n```sql\nCREATE OR REPLACE PACKAGE calc_pkg AS\n PROCEDURE calculate(p_value NUMBER);\n PROCEDURE calculate(p_value VARCHAR2);\n PROCEDURE calculate(p_value DATE);\nEND;\n/\n\nCREATE OR REPLACE PACKAGE BODY calc_pkg AS\n PROCEDURE calculate(p_value NUMBER) IS\n BEGIN\n DBMS_OUTPUT.PUT_LINE('Number: ' || p_value);\n END;\n\n PROCEDURE calculate(p_value VARCHAR2) IS\n BEGIN\n DBMS_OUTPUT.PUT_LINE('String: ' || p_value);\n END;\n\n PROCEDURE calculate(p_value DATE) IS\n BEGIN\n DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(p_value));\n END;\nEND;\n/\n```\n\n## Recursive Procedure\n\n```sql\nCREATE OR REPLACE PROCEDURE print_hierarchy(\n p_parent_id IN NUMBER,\n p_level IN NUMBER DEFAULT 0\n)\nIS\nBEGIN\n FOR rec IN (SELECT employee_id, first_name\n FROM employees\n WHERE manager_id = p_parent_id\n OR (p_parent_id IS NULL AND manager_id IS NULL))\n LOOP\n DBMS_OUTPUT.PUT_LINE(LPAD(' ', p_level * 2) || rec.first_name);\n print_hierarchy(rec.employee_id, p_level + 1);\n END LOOP;\nEND;\n/\n```\n\n## Common Patterns\n\n### Error Logging Pattern\n\n```sql\nCREATE OR REPLACE PROCEDURE process_data(p_id NUMBER)\nIS\n v_step VARCHAR2(100);\nBEGIN\n v_step := 'Fetching data';\n -- ... fetch logic\n\n v_step := 'Processing data';\n -- ... process logic\n\n v_step := 'Saving results';\n -- ... save logic\n\n COMMIT;\nEXCEPTION\n WHEN OTHERS THEN\n log_error(v_step || ': ' || SQLERRM);\n RAISE;\nEND;\n/\n```\n\n### Batch Processing\n\n```sql\nCREATE OR REPLACE PROCEDURE batch_update(p_batch_size NUMBER DEFAULT 1000)\nIS\n TYPE id_tab IS TABLE OF NUMBER;\n v_ids id_tab;\n\n CURSOR c_pending IS\n SELECT id FROM orders WHERE status = 'PENDING';\nBEGIN\n OPEN c_pending;\n LOOP\n FETCH c_pending BULK COLLECT INTO v_ids LIMIT p_batch_size;\n EXIT WHEN v_ids.COUNT = 0;\n\n FORALL i IN 1..v_ids.COUNT\n UPDATE orders SET status = 'PROCESSING'\n WHERE id = v_ids(i);\n\n COMMIT;\n END LOOP;\n CLOSE c_pending;\nEND;\n/\n```\n\n### Validation Procedure\n\n```sql\nCREATE OR REPLACE PROCEDURE validate_order(\n p_order_id IN NUMBER,\n p_is_valid OUT BOOLEAN,\n p_message OUT VARCHAR2\n)\nIS\n v_order orders%ROWTYPE;\nBEGIN\n p_is_valid := TRUE;\n p_message := NULL;\n\n SELECT * INTO v_order FROM orders WHERE id = p_order_id;\n\n IF v_order.total \u003c= 0 THEN\n p_is_valid := FALSE;\n p_message := 'Order total must be positive';\n RETURN;\n END IF;\n\n IF v_order.customer_id IS NULL THEN\n p_is_valid := FALSE;\n p_message := 'Customer is required';\n RETURN;\n END IF;\n\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n p_is_valid := FALSE;\n p_message := 'Order not found';\nEND;\n/\n```\n\n## Drop Procedure\n\n```sql\nDROP PROCEDURE procedure_name;\n```\n\n## View Procedure Code\n\n```sql\nSELECT text\nFROM user_source\nWHERE name = 'PROCEDURE_NAME'\nAND type = 'PROCEDURE'\nORDER BY line;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5793,"content_sha256":"2544b6e83c0d8685aaf1f35b6e72654c0347f5591d9dc846eab251f9b7eafe59"},{"filename":"quick-ref/triggers.md","content":"# PL/SQL Triggers Quick Reference\n\n## Trigger Types\n\n| Type | Description |\n|------|-------------|\n| DML Trigger | Fires on INSERT, UPDATE, DELETE |\n| DDL Trigger | Fires on CREATE, ALTER, DROP |\n| System Trigger | Fires on database events (LOGON, STARTUP) |\n| INSTEAD OF Trigger | Replaces DML on views |\n| Compound Trigger | Multiple timing points in one trigger |\n\n## Basic DML Trigger Syntax\n\n```sql\nCREATE [OR REPLACE] TRIGGER trigger_name\n{BEFORE | AFTER | INSTEAD OF}\n{INSERT | UPDATE [OF column_list] | DELETE}\n[OR {INSERT | UPDATE | DELETE}]\nON table_name\n[REFERENCING OLD AS old NEW AS new]\n[FOR EACH ROW]\n[WHEN (condition)]\n[DECLARE\n -- declarations]\nBEGIN\n -- trigger body\n[EXCEPTION\n -- exception handlers]\nEND;\n/\n```\n\n## Timing Points\n\n| Timing | Description |\n|--------|-------------|\n| BEFORE | Before DML executes |\n| AFTER | After DML executes |\n| INSTEAD OF | Replace DML (views only) |\n\n## Row-Level vs Statement-Level\n\n```sql\n-- Row-level: Fires once per affected row\nCREATE TRIGGER trg_row_level\nAFTER INSERT ON employees\nFOR EACH ROW\nBEGIN\n -- :NEW and :OLD available\nEND;\n/\n\n-- Statement-level: Fires once per statement\nCREATE TRIGGER trg_statement_level\nAFTER INSERT ON employees\n-- No FOR EACH ROW\nBEGIN\n -- :NEW and :OLD NOT available\nEND;\n/\n```\n\n## Trigger Variables\n\n| Variable | INSERT | UPDATE | DELETE |\n|----------|--------|--------|--------|\n| `:NEW.column` | New value | New value | NULL |\n| `:OLD.column` | NULL | Old value | Old value |\n| `INSERTING` | TRUE | FALSE | FALSE |\n| `UPDATING` | FALSE | TRUE | FALSE |\n| `DELETING` | FALSE | FALSE | TRUE |\n\n## Common Patterns\n\n### Auto-Populate Columns\n\n```sql\nCREATE OR REPLACE TRIGGER trg_employees_bi\nBEFORE INSERT ON employees\nFOR EACH ROW\nBEGIN\n :NEW.employee_id := emp_seq.NEXTVAL;\n :NEW.created_at := SYSDATE;\n :NEW.created_by := USER;\nEND;\n/\n\nCREATE OR REPLACE TRIGGER trg_employees_bu\nBEFORE UPDATE ON employees\nFOR EACH ROW\nBEGIN\n :NEW.updated_at := SYSDATE;\n :NEW.updated_by := USER;\nEND;\n/\n```\n\n### Audit Trail\n\n```sql\nCREATE OR REPLACE TRIGGER trg_employees_audit\nAFTER INSERT OR UPDATE OR DELETE ON employees\nFOR EACH ROW\nDECLARE\n v_action VARCHAR2(10);\nBEGIN\n IF INSERTING THEN\n v_action := 'INSERT';\n ELSIF UPDATING THEN\n v_action := 'UPDATE';\n ELSE\n v_action := 'DELETE';\n END IF;\n\n INSERT INTO employees_audit (\n action,\n employee_id,\n old_salary,\n new_salary,\n changed_by,\n changed_at\n ) VALUES (\n v_action,\n NVL(:NEW.employee_id, :OLD.employee_id),\n :OLD.salary,\n :NEW.salary,\n USER,\n SYSDATE\n );\nEND;\n/\n```\n\n### Validation Trigger\n\n```sql\nCREATE OR REPLACE TRIGGER trg_validate_salary\nBEFORE INSERT OR UPDATE OF salary ON employees\nFOR EACH ROW\nBEGIN\n IF :NEW.salary \u003c 0 THEN\n RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');\n END IF;\n\n IF :NEW.salary > 1000000 THEN\n RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum allowed');\n END IF;\n\n -- Prevent salary decrease (except by managers)\n IF UPDATING AND :NEW.salary \u003c :OLD.salary THEN\n IF USER NOT IN ('HR_MANAGER', 'ADMIN') THEN\n RAISE_APPLICATION_ERROR(-20003, 'Only managers can decrease salary');\n END IF;\n END IF;\nEND;\n/\n```\n\n### Prevent Delete\n\n```sql\nCREATE OR REPLACE TRIGGER trg_prevent_delete\nBEFORE DELETE ON critical_data\nFOR EACH ROW\nBEGIN\n RAISE_APPLICATION_ERROR(-20001, 'Deletion not allowed on this table');\nEND;\n/\n```\n\n### Soft Delete\n\n```sql\nCREATE OR REPLACE TRIGGER trg_soft_delete\nBEFORE DELETE ON employees\nFOR EACH ROW\nBEGIN\n UPDATE employees\n SET deleted_at = SYSDATE,\n status = 'DELETED'\n WHERE employee_id = :OLD.employee_id;\n\n -- Prevent actual delete\n RAISE_APPLICATION_ERROR(-20001, 'Row soft-deleted');\nEND;\n/\n```\n\n### Cross-Table Validation\n\n```sql\nCREATE OR REPLACE TRIGGER trg_check_budget\nBEFORE INSERT OR UPDATE ON employees\nFOR EACH ROW\nDECLARE\n v_budget NUMBER;\n v_total NUMBER;\nBEGIN\n SELECT budget INTO v_budget\n FROM departments WHERE department_id = :NEW.department_id;\n\n SELECT NVL(SUM(salary), 0) INTO v_total\n FROM employees\n WHERE department_id = :NEW.department_id\n AND employee_id != NVL(:NEW.employee_id, -1);\n\n IF v_total + :NEW.salary > v_budget THEN\n RAISE_APPLICATION_ERROR(-20001,\n 'Salary would exceed department budget');\n END IF;\nEND;\n/\n```\n\n## Compound Trigger (11g+)\n\n```sql\nCREATE OR REPLACE TRIGGER trg_employees_compound\nFOR INSERT OR UPDATE ON employees\nCOMPOUND TRIGGER\n\n -- Declare collection for bulk processing\n TYPE emp_id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;\n g_emp_ids emp_id_tab;\n g_idx PLS_INTEGER := 0;\n\nBEFORE STATEMENT IS\nBEGIN\n g_idx := 0;\nEND BEFORE STATEMENT;\n\nBEFORE EACH ROW IS\nBEGIN\n -- Auto-set timestamps\n IF INSERTING THEN\n :NEW.created_at := SYSDATE;\n END IF;\n :NEW.updated_at := SYSDATE;\nEND BEFORE EACH ROW;\n\nAFTER EACH ROW IS\nBEGIN\n -- Collect IDs for bulk processing\n g_idx := g_idx + 1;\n g_emp_ids(g_idx) := :NEW.employee_id;\nEND AFTER EACH ROW;\n\nAFTER STATEMENT IS\nBEGIN\n -- Bulk operation after all rows processed\n FORALL i IN 1..g_idx\n INSERT INTO employee_changes (employee_id, change_date)\n VALUES (g_emp_ids(i), SYSDATE);\nEND AFTER STATEMENT;\n\nEND trg_employees_compound;\n/\n```\n\n## INSTEAD OF Trigger (Views)\n\n```sql\nCREATE VIEW emp_dept_view AS\n SELECT e.employee_id, e.first_name, e.salary,\n d.department_id, d.department_name\n FROM employees e\n JOIN departments d ON e.department_id = d.department_id;\n\nCREATE OR REPLACE TRIGGER trg_emp_dept_insert\nINSTEAD OF INSERT ON emp_dept_view\nFOR EACH ROW\nBEGIN\n INSERT INTO employees (employee_id, first_name, salary, department_id)\n VALUES (:NEW.employee_id, :NEW.first_name, :NEW.salary, :NEW.department_id);\nEND;\n/\n\n-- Now this works:\nINSERT INTO emp_dept_view (employee_id, first_name, salary, department_id)\nVALUES (999, 'John', 50000, 10);\n```\n\n## DDL Trigger\n\n```sql\nCREATE OR REPLACE TRIGGER trg_ddl_audit\nAFTER DDL ON SCHEMA\nBEGIN\n INSERT INTO ddl_audit (\n event_type,\n object_type,\n object_name,\n sql_text,\n username,\n event_date\n ) VALUES (\n ORA_SYSEVENT,\n ORA_DICT_OBJ_TYPE,\n ORA_DICT_OBJ_NAME,\n NULL, -- SQL text requires additional setup\n USER,\n SYSDATE\n );\nEND;\n/\n```\n\n## System Trigger\n\n```sql\n-- LOGON trigger\nCREATE OR REPLACE TRIGGER trg_logon_audit\nAFTER LOGON ON DATABASE\nBEGIN\n INSERT INTO logon_audit (username, logon_time, ip_address)\n VALUES (USER, SYSDATE, SYS_CONTEXT('USERENV', 'IP_ADDRESS'));\nEND;\n/\n```\n\n## Enable/Disable Triggers\n\n```sql\n-- Disable trigger\nALTER TRIGGER trigger_name DISABLE;\n\n-- Enable trigger\nALTER TRIGGER trigger_name ENABLE;\n\n-- Disable all triggers on table\nALTER TABLE table_name DISABLE ALL TRIGGERS;\n\n-- Enable all triggers on table\nALTER TABLE table_name ENABLE ALL TRIGGERS;\n```\n\n## Drop Trigger\n\n```sql\nDROP TRIGGER trigger_name;\n```\n\n## View Trigger Information\n\n```sql\n-- All triggers\nSELECT trigger_name, table_name, triggering_event, status\nFROM user_triggers;\n\n-- Trigger source code\nSELECT text\nFROM user_source\nWHERE name = 'TRIGGER_NAME'\nAND type = 'TRIGGER'\nORDER BY line;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7354,"content_sha256":"6966341bc4f749335b6379b046e5dca6831c03fdfb4cc4741c91cc3eb1d35700"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Oracle PL/SQL Core Knowledge","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Full Reference","type":"text","marks":[{"type":"strong"}]},{"text":": See ","type":"text"},{"text":"advanced.md","type":"text","marks":[{"type":"link","attrs":{"href":"advanced.md","title":null}}]},{"text":" for pipelined table functions, packages, collections, BULK COLLECT/FORALL, compound triggers, and advanced cursors.","type":"text"}]}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Deep Knowledge","type":"text","marks":[{"type":"strong"}]},{"text":": Use ","type":"text"},{"text":"mcp__documentation__fetch_docs","type":"text","marks":[{"type":"code_inline"}]},{"text":" with technology: ","type":"text"},{"text":"oracle","type":"text","marks":[{"type":"code_inline"}]},{"text":" for comprehensive documentation.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Basic Structure","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE\n -- Variable declarations\n v_count NUMBER := 0;\nBEGIN\n -- Executable statements\n DBMS_OUTPUT.PUT_LINE('Hello World');\nEXCEPTION\n WHEN OTHERS THEN\n -- Exception handling\n DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Procedures","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Basic Procedure","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE PROCEDURE update_salary(\n p_employee_id IN NUMBER,\n p_percentage IN NUMBER\n)\nIS\n v_current_salary NUMBER;\nBEGIN\n SELECT salary INTO v_current_salary\n FROM employees\n WHERE employee_id = p_employee_id;\n\n UPDATE employees\n SET salary = salary * (1 + p_percentage / 100)\n WHERE employee_id = p_employee_id;\n\n COMMIT;\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_employee_id);\nEND update_salary;\n/\n\n-- Execute\nEXEC update_salary(100, 10);\n-- or\nBEGIN\n update_salary(100, 10);\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Procedure with OUT Parameters","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE PROCEDURE get_employee_info(\n p_employee_id IN NUMBER,\n p_name OUT VARCHAR2,\n p_salary OUT NUMBER,\n p_dept_name OUT VARCHAR2\n)\nIS\nBEGIN\n SELECT e.first_name || ' ' || e.last_name,\n e.salary,\n d.department_name\n INTO p_name, p_salary, p_dept_name\n FROM employees e\n JOIN departments d ON e.department_id = d.department_id\n WHERE e.employee_id = p_employee_id;\nEND;\n/\n\n-- Call with OUT parameters\nDECLARE\n v_name VARCHAR2(100);\n v_salary NUMBER;\n v_dept VARCHAR2(100);\nBEGIN\n get_employee_info(100, v_name, v_salary, v_dept);\n DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Functions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Scalar Function","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE FUNCTION calculate_bonus(\n p_salary IN NUMBER,\n p_years IN NUMBER\n)\nRETURN NUMBER\nDETERMINISTIC -- Same inputs always return same output\nIS\n v_bonus NUMBER;\nBEGIN\n IF p_years >= 10 THEN\n v_bonus := p_salary * 0.15;\n ELSIF p_years >= 5 THEN\n v_bonus := p_salary * 0.10;\n ELSE\n v_bonus := p_salary * 0.05;\n END IF;\n\n RETURN v_bonus;\nEND;\n/\n\n-- Usage in SQL\nSELECT employee_id, salary, calculate_bonus(salary, years_of_service) as bonus\nFROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Cursors","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Implicit Cursor","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN\n UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;\n DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);\n\n IF SQL%NOTFOUND THEN\n DBMS_OUTPUT.PUT_LINE('No rows found');\n END IF;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Explicit Cursor","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE\n CURSOR emp_cursor IS\n SELECT employee_id, first_name, salary\n FROM employees\n WHERE department_id = 10;\n\n v_emp emp_cursor%ROWTYPE;\nBEGIN\n OPEN emp_cursor;\n LOOP\n FETCH emp_cursor INTO v_emp;\n EXIT WHEN emp_cursor%NOTFOUND;\n DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);\n END LOOP;\n CLOSE emp_cursor;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Cursor FOR Loop (Preferred)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN\n FOR emp_rec IN (SELECT employee_id, first_name, salary\n FROM employees WHERE department_id = 10)\n LOOP\n DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);\n END LOOP;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Collections","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Associative Array (INDEX BY)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE\n TYPE salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;\n TYPE name_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);\n\n salaries salary_tab;\n names name_tab;\nBEGIN\n salaries(1) := 50000;\n salaries(2) := 60000;\n\n names('EMP001') := 'John Doe';\n names('EMP002') := 'Jane Smith';\n\n DBMS_OUTPUT.PUT_LINE(salaries(1));\n DBMS_OUTPUT.PUT_LINE(names('EMP001'));\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Exception Handling","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Predefined Exceptions","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Exception","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"NO_DATA_FOUND","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT INTO returned no rows","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"TOO_MANY_ROWS","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT INTO returned multiple rows","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ZERO_DIVIDE","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Division by zero","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"VALUE_ERROR","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Numeric or value error","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"INVALID_CURSOR","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Invalid cursor operation","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"DUP_VAL_ON_INDEX","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Duplicate value on unique index","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Exception Handling","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DECLARE\n v_salary NUMBER;\nBEGIN\n SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n DBMS_OUTPUT.PUT_LINE('Employee not found');\n WHEN TOO_MANY_ROWS THEN\n DBMS_OUTPUT.PUT_LINE('Multiple employees found');\n WHEN OTHERS THEN\n DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);\n RAISE;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"RAISE_APPLICATION_ERROR","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN\n IF some_condition THEN\n RAISE_APPLICATION_ERROR(-20001, 'Custom error message');\n END IF;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Triggers","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Row-Level Trigger","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE TRIGGER trg_emp_salary_check\nBEFORE INSERT OR UPDATE OF salary ON employees\nFOR EACH ROW\nBEGIN\n IF :NEW.salary \u003c 0 THEN\n RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');\n END IF;\n\n IF :NEW.salary > 1000000 THEN\n RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum');\n END IF;\nEND;\n/","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DO","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use packages to organize related code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use BULK COLLECT and FORALL for large datasets","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use cursor FOR loops (auto open/close)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define exceptions at package level","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use %TYPE and %ROWTYPE for type safety","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use bind variables to prevent SQL injection","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"DON'T","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use implicit commits in triggers","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ignore exceptions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SELECT INTO without handling NO_DATA_FOUND","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create excessive triggers (performance impact)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When NOT to Use This Skill","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Basic Oracle SQL","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"oracle","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for queries, data types, partitioning","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PL/pgSQL (PostgreSQL)","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"plpgsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for PostgreSQL procedures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"T-SQL (SQL Server)","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"tsql","type":"text","marks":[{"type":"code_inline"}]},{"text":" skill for SQL Server procedures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Basic SQL","type":"text","marks":[{"type":"strong"}]},{"text":" - Use ","type":"text"},{"text":"sql-fundamentals","type":"text","marks":[{"type":"code_inline"}]},{"text":" for ANSI SQL basics","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Anti-Patterns","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Anti-Pattern","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Problem","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Solution","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Not using BULK COLLECT","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Row-by-row processing","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use BULK COLLECT for large datasets","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT INTO without exception","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Runtime errors","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Handle NO_DATA_FOUND","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Not using packages","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Code disorganization","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Organize related code in packages","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Excessive triggers","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Performance issues","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Minimize trigger logic","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"WHEN OTHERS without RAISE","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Silent failures","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Re-raise or log exceptions","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Implicit cursors for large sets","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Memory issues","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use explicit cursors with LIMIT","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Troubleshooting","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Problem","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Diagnostic","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"NO_DATA_FOUND","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT INTO with no rows","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add exception handler","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"TOO_MANY_ROWS","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT INTO with multiple rows","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add WHERE or use cursor","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ORA-06502 numeric error","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Type conversion failure","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Check data types, use TO_NUMBER","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Slow procedure","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"DBMS_PROFILER","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use BULK operations","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Package state lost","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Session reset","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use PRAGMA SERIALLY_REUSABLE or re-initialize","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Documentation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Procedures","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/procedures.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Functions","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/functions.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Triggers","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/triggers.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Packages","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/packages.md","title":null}}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Cursors","type":"text","marks":[{"type":"link","attrs":{"href":"quick-ref/cursors.md","title":null}}]}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"plsql","author":"@skillopedia","source":{"stars":19,"repo_name":"claude-dev-suite","origin_url":"https://github.com/claude-dev-suite/claude-dev-suite/blob/HEAD/skills/databases/plsql/SKILL.md","repo_owner":"claude-dev-suite","body_sha256":"2926ddff2c9931c461368bdf79e0054fed4214b132f7cede9e35958897e563ad","cluster_key":"ac6c9b1e5ee5624275c1b654a60615d07713b8299f9ae37d5895fdba0f420e70","clean_bundle":{"format":"clean-skill-bundle-v1","source":"claude-dev-suite/claude-dev-suite/skills/databases/plsql/SKILL.md","attachments":[{"id":"c9439ead-c8eb-57dc-a5e6-d6a6464f90f5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/c9439ead-c8eb-57dc-a5e6-d6a6464f90f5/attachment.md","path":"advanced.md","size":6877,"sha256":"882d71a03b0bc64a74dd0166d28d4855b249da130222f1cb72b0bda5a6474c3c","contentType":"text/markdown; charset=utf-8"},{"id":"76e7bbec-40ad-5374-a706-7f597929a189","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/76e7bbec-40ad-5374-a706-7f597929a189/attachment.md","path":"quick-ref/cursors.md","size":8103,"sha256":"8fec624ab0a4cb2270faf912a8169386cbc481e4463b090449f3440870da3ac9","contentType":"text/markdown; charset=utf-8"},{"id":"eac20ce6-b914-5d6f-82c2-52f48555975a","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/eac20ce6-b914-5d6f-82c2-52f48555975a/attachment.md","path":"quick-ref/functions.md","size":5972,"sha256":"b12924cf696751753a64e38951b8a932cdb5cc4b06f4971038f89c8d9b29a11c","contentType":"text/markdown; charset=utf-8"},{"id":"2ab5b496-d18c-5b86-bf66-832b2c60aa09","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/2ab5b496-d18c-5b86-bf66-832b2c60aa09/attachment.md","path":"quick-ref/packages.md","size":8864,"sha256":"b720783b7c4965ff4db0c9bdb82d571ba7a29ac2fe08a63275c12ffd58f96fd6","contentType":"text/markdown; charset=utf-8"},{"id":"8adaf131-1d1e-5784-a897-71e0253e8f51","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/8adaf131-1d1e-5784-a897-71e0253e8f51/attachment.md","path":"quick-ref/procedures.md","size":5793,"sha256":"2544b6e83c0d8685aaf1f35b6e72654c0347f5591d9dc846eab251f9b7eafe59","contentType":"text/markdown; charset=utf-8"},{"id":"42d7f925-e500-53ee-93f3-1689788d71b5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/42d7f925-e500-53ee-93f3-1689788d71b5/attachment.md","path":"quick-ref/triggers.md","size":7354,"sha256":"6966341bc4f749335b6379b046e5dca6831c03fdfb4cc4741c91cc3eb1d35700","contentType":"text/markdown; charset=utf-8"}],"bundle_sha256":"317738194d04fb7977caa485b60e852925797f9f31832f7c9b19c339a79e46bd","attachment_count":6,"text_attachments":6,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/databases/plsql/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"clean-skills-v1","description":"Oracle PL/SQL procedural language. Covers stored procedures, functions,\npackages, triggers, cursors, collections, and exception handling.\nUse for Oracle database server-side programming.\n\nUSE WHEN: user mentions \"plsql\", \"Oracle procedures\", \"Oracle packages\",\n\"Oracle triggers\", \"BULK COLLECT\", \"FORALL\", \"DBMS_OUTPUT\", \"Oracle functions\"\n\nDO NOT USE FOR: basic Oracle SQL - use `oracle` instead,\nPostgreSQL - use `plpgsql` instead, T-SQL - use `tsql` instead\n","allowed-tools":"Read, Grep, Glob, Write, Edit"}},"renderedAt":1782979213239}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.