plsql — Claude Code skill: Oracle PL/SQL and how to install
A skill for Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI and OpenCode: Oracle PL/SQL — procedures, functions, packages, triggers, cursors, collections and exception handling.
MIT 106 installs ★ 50 updated 2026-10-23
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a claude-codeWhat plsql does
Runs on six platforms: Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI and OpenCode. The agent gets knowledge of Oracle procedures, functions, packages, triggers, cursors (explicit, FOR LOOP), associative arrays, predefined exception handling (NO_DATA_FOUND, TOO_MANY_ROWS) and RAISE_APPLICATION_ERROR. Includes examples of procedures with OUT parameters, deterministic functions, CURSOR FOR loops, row-level triggers. Recommends BULK COLLECT and FORALL for large datasets, %TYPE and %ROWTYPE for type safety.
Install via npx skills add (Claude Code, Codex, Cursor, OpenCode) or gh skill install (GitHub Copilot); the file lands in ~/.claude/skills/plsql/SKILL.md, .agents/skills/plsql/SKILL.md, .cursor/skills/plsql/SKILL.md or .github/skills/plsql/SKILL.md depending on the platform. For plain Oracle SQL queries there is a separate oracle skill; for PostgreSQL use plpgsql, for SQL Server use tsql.
What it helps with
- write an Oracle procedure with OUT parameters
- create a row-level trigger that validates salary
- handle NO_DATA_FOUND and TOO_MANY_ROWS in a PL/SQL block
- replace row-by-row fetch with BULK COLLECT
- build a cursor FOR LOOP over employees table
Example prompts for the agent
- Write a procedure
update_salarythat changes employee salary byemployee_idand percentage - Create a function
calculate_bonus: 15% of salary if tenure is over 10 years, otherwise 5% - Add a trigger on
employees: on insert or update,salarymust not exceed one million
How to install plsql
1Skills CLI (Vercel)
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a claude-code2Manually: put the files in place
~/.claude/skills/plsql/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/plsql/
1Skills CLI (Vercel)
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a codex2Manually: put the files in place
.agents/skills/plsql/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a cursor2Manually: put the files in place
.cursor/skills/plsql/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install claude-dev-suite/claude-dev-suite skills/databases/plsql2Manually: put the files in place
.github/skills/plsql/SKILL.md1Skills CLI (Vercel)
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a gemini-cli2Platform CLI
gemini skills install https://github.com/claude-dev-suite/claude-dev-suite --path skills/databases/plsqlManually: put the files in place
.devin/skills/plsql/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about plsql
How to install plsql in Claude Code?
Run npx skills add claude-dev-suite/claude-dev-suite --skill plsql -a claude-code. The file will appear in ~/.claude/skills/plsql/SKILL.md.
What does the plsql skill do?
Gives the agent knowledge of Oracle PL/SQL: procedures, functions, packages, triggers, cursors, collections and exception handling. Activates when you mention Oracle procedures, BULK COLLECT, FORALL or DBMS_OUTPUT.
How does plsql differ from the oracle skill?
The oracle skill covers plain Oracle SQL queries, data types and partitioning. plsql is for server-side code: procedures, functions, triggers and PL/SQL constructs like cursors and RAISE_APPLICATION_ERROR.
Source text
SKILL.md · MIT
---
name: plsql
description: |
Oracle PL/SQL procedural language. Covers stored procedures, functions,
packages, triggers, cursors, collections, and exception handling.
Use for Oracle database server-side programming.
USE WHEN: user mentions "plsql", "Oracle procedures", "Oracle packages",
"Oracle triggers", "BULK COLLECT", "FORALL", "DBMS_OUTPUT", "Oracle functions"
DO NOT USE FOR: basic Oracle SQL - use `oracle` instead,
PostgreSQL - use `plpgsql` instead, T-SQL - use `tsql` instead
allowed-tools: Read, Grep, Glob, Write, Edit
---
# Oracle PL/SQL Core Knowledge
> **Full Reference**: See [advanced.md](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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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)
```sql
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)
```sql
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
```sql
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
```sql
BEGIN
IF some_condition THEN
RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
END IF;
END;
/
```
## Triggers
### Row-Level Trigger
```sql
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](quick-ref/procedures.md)
- [Functions](quick-ref/functions.md)
- [Triggers](quick-ref/triggers.md)
- [Packages](quick-ref/packages.md)
- [Cursors](quick-ref/cursors.md)
© claude-dev-suite · MIT · claude-dev-suite/claude-dev-suite