skills.rest
ZzZueszZ
ZzZueszZCOMMUNITY
Explore all skills by this author →
GitHub

Oracle PL/SQL

Standardizes writing Oracle PL/SQL stored procedures, functions, packages, cursors, and exception handling.

Updated May 12, 2026
Search Tags:#database#packages#oracle#stored procedures#exception handling#jpa#plsql
One-click install
npx skills add https://github.com/ZzZueszZ/claude-kit --skill oracle-pl-sql-zzzueszz
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: Oracle PL/SQL
Source: https://github.com/ZzZueszZ/claude-kit/tree/main/.claude/skills/oracle-plsql
Command: npx skills add https://github.com/ZzZueszZ/claude-kit --skill oracle-pl-sql-zzzueszz

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Writing PL/SQL without consistent conventions leads to unmaintainable stored procedures, missing exception handling, and unsafe transaction behavior in Oracle databases. This Skill enforces naming standards, error handling patterns, and structural best practices for all PL/SQL code. ## Core Features & Use Cases - Naming Conventions: Enforces standard prefixes for variables (v_), parameters (p_, o_), cursors (cur_), constants (c_), and exceptions (e_). - Procedures, Functions & Packages: Provides templates for side-effect procedures with COMMIT/ROLLBACK, side-effect-free deterministic functions, and grouped packages callable from JPA. - Cursors & Bulk Operations: Covers cursor FOR loops and BULK COLLECT with LIMIT for processing large datasets safely. - Exception Handling: Documents predefined Oracle exceptions and custom errors via RAISE_APPLICATION_ERROR in the -20000 to -20999 range. - Use Case: When building a Spring Boot backend on Oracle, use this Skill to generate a package like pkg_message_mgmt with proper exception handling and call it from a JPA StoredProcedureQuery. ## Quick Start Write an Oracle stored procedure to soft-delete a user following the PL/SQL naming and exception handling standards.

Frequently Asked Questions about Oracle PL/SQL

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I write an Oracle stored procedure with proper exception handling?▼

Wrap the procedure body in a BEGIN/EXCEPTION/END block, use OUT parameters for result codes, COMMIT on success, and ROLLBACK in the WHEN OTHERS handler. Prefix parameters with p_ or o_ and local variables with v_ for readability.

What naming conventions should I use for PL/SQL variables and parameters?▼

Use v_ for local variables, p_ for IN parameters, o_ for OUT parameters, cur_ for cursors, c_ for constants, and e_ for exceptions. Consistent prefixes make scope and intent immediately visible in procedure bodies.

How do I call an Oracle package from Spring JPA?▼

Use EntityManager.createStoredProcedureQuery with the fully qualified name like pkg_message_mgmt.mark_as_seen, register each parameter with registerStoredProcedureParameter, set values, then call execute. This works for procedures inside packages.

How do I process millions of rows in PL/SQL without memory issues?▼

Use BULK COLLECT with a LIMIT clause inside a loop, typically fetching 1000 rows at a time, then apply FORALL for batch DML and COMMIT per batch. This bounds memory usage and keeps undo segments manageable.

When should I avoid using triggers in Oracle?▼

Avoid triggers for business logic; keep that in the application layer. Reserve triggers for narrow mechanical tasks like auto-updating updated_at timestamps or audit logging, named with the trg_{table}_{action} pattern.

How do I raise custom errors in Oracle PL/SQL?▼

Use RAISE_APPLICATION_ERROR with an error code between -20000 and -20999 and a descriptive message, for example RAISE_APPLICATION_ERROR(-20001, 'Label not found'). This range is reserved for user-defined errors and propagates cleanly to callers.

Related Skills

Explore More Software Engineering Skills→
tinkermonkeytinkermonkey

LAYER_08_DATASTORE

Model physical database design with tables, indexes, constraints, and triggers.

Community
Intermediate
rizaldiemrizaldiem

spring-boot-crud-patterns

Generate Spring Boot CRUD REST endpoints and JPA persistence adapters.

Community
Advanced
githubgithub
38.5k

scaffolding-oracle-to-postgres-migration-test-project

Scaffolds an xUnit integration test project with transaction rollback and seed data infrastructure for Oracle databases.

Official
Intermediate
jeremylongshorejeremylongshore
2.6k

generating-stored-procedures

Generate stored procedures, functions, and triggers for PostgreSQL, MySQL, and SQL Server.

Community
Intermediate