Plsql Code Quality

PL/SQL Code Quality Guide

This skill covers naming conventions, anti-patterns, and structural patterns for Oracle PL/SQL. It does not cover formatting (indentation, alignment, whitespace) — that belongs to the plsql-formatter skill.

The philosophy here is to trust the developer. These are guardrails, not handcuffs. Every rule has a reason, and when the reason doesn't apply, the developer can override it — as long as they leave a comment explaining why.

Stamp

On success, run: python3 /Users/dobby/Library/CloudStorage/Dropbox/BRAIN/AI/SCRIPTS/skills_log.py stamp plsql-code-quality

Comment Markers for Intentional Overrides

Two special comment markers signal conscious decisions:

• -- WHY: — explains a workaround, a non-obvious choice, or an intentional rule violation. Any pattern that would normally be flagged as an anti-pattern is acceptable if it has a -- WHY: comment.
• -- VERIFY: — flags uncertain code that needs validation. Use this when you're not sure an approach is correct, to invite review rather than hide doubt.

EXCEPTION
WHEN OTHERS THEN
    NULL;  -- WHY: called from APEX error handler, re-raising would crash the page

v_timeout := 30;  -- VERIFY: is 30s enough for large batch jobs? check with DBA

These markers serve as documentation for future maintainers and as signals during code review. A -- WHY: comment turns a red flag into a design decision. A -- VERIFY: comment turns hidden uncertainty into an invitation to collaborate.

Naming Conventions

Variable and Parameter Prefixes

| Prefix | Scope | Example | | ------ | ------------------------------- | -------------------------- | | in_ | IN parameter | in_user_id | | out_ | OUT parameter | out_status | | io_ | IN OUT parameter | io_payload | | v_ | Local variable | v_count | | g_ | Package-level (global) variable | g_debug_mode | | c_ | Local constant | c_max_retries | | rec | Record / %ROWTYPE variable | rec, rec_employee | | t_ | Type definition | t_id_list, t_order_rec | | e_ | Exception variable | e_lock_timeout | | cur_ | Explicit cursor | cur_pending_orders |

The in_/out_/io_ convention is preferred over the generic p_ because it communicates data flow direction at a glance — a reader immediately knows whether a parameter is input, output, or both without checking the signature.

Database Object Naming

The naming pattern follows a consistent structure: optional app/project prefix + descriptive name + type postfix. The postfix identifies the object type; the prefix groups objects by application. This way, querying WHERE object_name LIKE 'myapp\_%' ESCAPE '\' returns everything belonging to that application.

| Object | Convention | Example | | --------------- | ---------------------------- | --------------------------------------- | | Table | Plural noun, snake_case | orders, order_items | | View | Descriptive name + _v | active_orders_v, user_permissions_v | | Package | Domain name, no postfix | core, auth, billing | | Procedure | Verb + noun | create_order, validate_input | | Function | get_, is_, has_ + noun | get_tax_rate, is_valid_email | | Sequence | Column name + _seq | order_id_seq, user_id_seq | | Index | Table + column(s) + _ix | orders_customer_id_ix | | Constraint (PK) | Table + _pk | orders_pk | | Constraint (UQ) | Table + column(s) + _uq | users_email_uq | | Constraint (FK) | Table + column + _fk | orders_customer_id_fk | | Constraint (NN) | Table + column + _nn | users_email_nn | | Trigger | Table + descriptive + _trg | orders_audit_trg |

Why postfixes, not prefixes for types? Postfixes keep related objects grouped together when sorted alphabetically. All orders_* objects appear together in the data dictionary — orders_pk, orders_customer_id_fk, orders_customer_id_ix, orders_audit_trg. With prefixes (pk_orders, fk_orders_..., ix_orders_...) they scatter across the alphabet.

Sequences should match column names. If the column is order_id, the sequence is order_id_seq. Prefer identity columns (GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY) over explicit sequences when possible — they're self-documenting and require no separate DDL. Use explicit sequences only when you need cross-table sharing, pre-fetching, or specific caching behavior.

Naming all constraints explicitly matters because when a constraint violation fires, the constraint name appears in the error message. ORA-00001: unique constraint (SCHEMA.USERS_EMAIL_UQ) violated is immediately actionable; ORA-00001: unique constraint (SCHEMA.SYS_C007234) violated requires a dictionary lookup.

Column Naming

Columns should include the entity context in their name — avoid generic names like id, name, desc, status, type on their own. When you join two tables that both have a column called id, ambiguity is guaranteed.

-- Avoid: generic, ambiguous across joins
CREATE TABLE projects (
    id          NUMBER,
    name        VARCHAR2(200),
    desc        VARCHAR2(4000)      -- also a reserved word
);

-- Preferred: self-describing, unambiguous in any join
CREATE TABLE projects (
    project_id          NUMBER,
    project_name        VARCHAR2(200),
    project_desc        VARCHAR2(4000)
);

This pays off in every query — WHERE t.project_id = ... is unambiguous regardless of how many tables are in the FROM clause. It also means foreign key columns naturally match their parent: orders.project_id references projects.project_id.

General Naming Principles

• Use meaningful, descriptive names — a reader should understand purpose without looking at the declaration
• Abbreviations are fine when they're domain-standard (id, qty, amt, desc, seq, ts for timestamp) but avoid inventing new ones
• Don't repeat the object type in the name — core not core_pkg, get_user not func_get_user
• For multi-app schemas, use an app prefix: shop_orders, shop_order_items, shop_orders_pk

Type Anchoring

Use %TYPE and %ROWTYPE wherever possible. They keep your code synchronized with the schema — when a column type changes, anchored variables adapt automatically on recompile. Hardcoded types break silently when the schema evolves.

-- Preferred: adapts to schema changes automatically
v_salary    employees.salary%TYPE;
rec         employees%ROWTYPE;

-- Avoid: breaks if column precision changes
v_salary    NUMBER(8,2);

When to use each:

• %TYPE — for variables that hold a single column value
• %ROWTYPE — for variables that hold an entire row, especially with SELECT ... INTO rec or DML using record variables
• Hardcoded types — only when there is no corresponding column (e.g., a computed ratio, a loop counter, a flag that exists only in PL/SQL logic)

Anti-Patterns

Swallowing Exceptions

-- Problem: silently discards all errors, impossible to diagnose in production
EXCEPTION
WHEN OTHERS THEN
    NULL;

This hides bugs. The caller has no idea the operation failed, and data may be in an inconsistent state. The correct pattern is to use a centralized error handler that logs the full error (including backtrace) and re-raises it to the caller:

EXCEPTION
WHEN core.app_exception THEN
    RAISE;
WHEN OTHERS THEN
    core.raise_error();
END;

The core.raise_error() call does both — it logs the error with full context (SQLCODE, SQLERRM, FORMATERRORBACKTRACE) and raises it to the caller. Known application exceptions (like core.app_exception) are re-raised directly since they've already been logged at the point of origin.

Note: core here is a project-specific utility package — substitute your own central error-handling package, or build one with a single raise_error entry point that captures SQLCODE, SQLERRM, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE before re-raising.

Override: sometimes swallowing makes sense — for instance, in an APEX error handler callback where re-raising would crash the page, or in a cleanup routine where failure is expected and harmless. In these cases, add a -- WHY: comment and it's fine.

Hardcoded Values

-- Problem: what is 3? what is 0.0825? will anyone remember in 6 months?
IF v_status = 3 THEN ...
v_tax := v_amount * 0.0825;

At minimum, hardcoded values should be package-level constants at the top of the package. This gives them a name, a single point of change, and visibility in the spec if other packages need them.

-- Package level
c_status_shipped    CONSTANT PLS_INTEGER := 3;
c_tax_rate_tx       CONSTANT NUMBER      := 0.0825;

-- Usage
IF v_status = c_status_shipped THEN ...
v_tax := v_amount * c_tax_rate_tx;

The exception is truly universal values that need no explanation: 0, 1, NULL, TRUE, FALSE, empty string. Don't wrap these in constants — that adds noise without clarity.

Implicit Type Conversions

-- Problem: depends on NLS_DATE_FORMAT, breaks across environments
WHERE hire_date = '01-JAN-2024'

-- Problem: implicit VARCHAR2-to-NUMBER, fragile
WHERE employee_id = '100'

Always use explicit conversions. ANSI date literals are the cleanest option for dates:

WHERE hire_date = DATE '2024-01-01'
WHERE employee_id = 100

DML Inside Cursor Loops

-- Problem: one context switch per row, extremely slow on large sets
FOR rec IN (SELECT employee_id FROM employees WHERE department_id = 50) LOOP
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = rec.employee_id;
END LOOP;

Push the work into a single SQL statement whenever possible. When row-by-row logic is truly needed, use BULK COLLECT + FORALL to batch the context switches.

Hardcoded Schema Names

-- Problem: breaks when deployed to a different schema
SELECT * FROM hr.employees;

Use synonyms or rely on the current schema. If cross-schema access is genuinely needed, make the schema configurable rather than hardcoded.

Unqualified Columns in SQL

Every column reference in a SQL statement must be prefixed with its table alias. No exceptions — even in single-table queries. This prevents ambiguity now and protects against future ambiguity when joins are added.

-- Problem: works today, breaks tomorrow when someone adds a JOIN
SELECT employee_id, salary
FROM employees
WHERE department_id = 50;

-- Correct: every column prefixed, alias is short and intuitive
SELECT t.employee_id, t.salary
FROM employees t
WHERE t.department_id = 50;

For single-table queries, t is a perfectly good alias. For multi-table queries, pick the most intuitive single letter per table — e for employees, d for departments, o for orders. When letters collide or would be confusing, use 2-3 character abbreviations.

SELECT e.employee_id, e.salary, d.department_name
FROM employees e
JOIN departments d
    ON d.department_id = e.department_id
WHERE e.hire_date > DATE '2024-01-01';

The shorter the alias, the better — it reduces noise while still disambiguating. The goal is that every column in the query can be traced to its source table at a glance.

Named Parameters for Custom Package Calls

When calling a procedure or function from a custom package (not Oracle built-in APIs), always use named parameter notation. This makes calls self-documenting and immune to parameter reordering.

-- Avoid: positional — what does each argument mean?
core.create_job('REBUILD_APP_' || v_app_id, v_stmt, 'DEFAULT', USER, v_app_id, NULL, 3, 'Rescan');

-- Correct: named — reads like documentation
core.create_job (
    in_job_name     => 'REBUILD_APP_' || v_app_id,
    in_statement    => v_stmt,
    in_job_class    => 'DEFAULT',
    in_user_id      => USER,
    in_app_id       => v_app_id,
    in_session_id   => NULL,
    in_priority     => 3,
    in_comments     => 'Rescan'
);

Oracle built-in APIs (DBMS_OUTPUT.PUT_LINE, APEX_STRING.SPLIT, TO_CHAR, etc.) can use positional notation since their signatures are well-known and stable. But for anything in your own codebase, named parameters are required — your signatures will evolve, and positional calls will silently break when they do.

Structural Patterns

Long SELECT Statements Belong in Views

If a SELECT statement exceeds roughly 100 lines, it's doing too much for inline SQL. Extract it into a database view. Views provide reuse, testability, and a clean abstraction layer. They're also easier to tune — a DBA can work on a view without touching PL/SQL.

The PL/SQL code then becomes a simple SELECT ... FROM my_view WHERE ... which is readable and maintainable.

Use Records for Large INSERT and UPDATE

When an INSERT or UPDATE touches many columns, use a %ROWTYPE record variable instead of listing every column. Records are less error-prone (you can't accidentally swap two values), adapt to schema changes, and dramatically reduce line count.

-- Instead of a 30-column INSERT:
rec         orders%ROWTYPE;
...
rec.order_id    := order_id_seq.NEXTVAL;
rec.customer_id := in_customer_id;
rec.status      := c_status_pending;
rec.created_at  := SYSDATE;
INSERT INTO orders VALUES rec;

-- Instead of a 20-column UPDATE, update via ROWTYPE where practical

Package Size Limits

| Measure | Guideline | | ----------------------- | --------------------------------------------- | | Package body | Under 10,000 lines | | Procedure/function body | Under 200 lines (excluding declarations) | | Inline SELECT | Under ~100 lines (move to a view beyond this) |

When a package approaches the limit, split by domain or feature area. When a procedure grows too long, extract helper procedures — the original becomes a coordinator that reads like an outline.

Views Over Tables for Data Display

When showing data to users (in APEX, ORDS, reports), query views rather than tables directly. Views provide an abstraction layer: you can change the underlying table structure without breaking the application, add computed columns, filter sensitive data, and join multiple tables into a clean read model. The application layer stays simple and the data logic stays in the database where it's governed.

Dynamic Views for APEX Pages

Views can reference APEX page items directly using APEX_UTIL.GET_SESSION_STATE or NV/V functions. This keeps APEX regions clean — the region source is just a view reference, and all WHERE filtering lives in the view where it benefits from compile-time validation and dependency tracking.

For views tied to a specific APEX page, use the naming convention {app_prefix}_p{page_number}_{description}_v (e.g. shop_p100_orders_v). This makes the relationship between views and pages immediately obvious. When the same view serves multiple pages, drop the page prefix and use a descriptive domain name instead.

You can test these views from PL/SQL by setting session state before querying:

APEX_UTIL.SET_SESSION_STATE('P100_CUSTOMER_ID', 42);
SELECT * FROM shop_p100_orders_v;

Package-Per-Page Pattern

Create a dedicated package for each APEX page or page group. Name the package `{appprefix}p{page

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: jkvetina
• Source: jkvetina/AI_SKILLS
• License: MIT

Install and usage instructions live in the source repository linked above.