pythia-package
Use when designing or writing a new package, procedure, function or trigger, or restructuring one - "viet package", "tao procedure", "them function", "them trigger", an API another team will call, an error-handling or logging design, a spec change. Applies the industry-standard PL/SQL design rules - the spec as a contract, named exceptions, one transaction owner, thin triggers - beneath the house conventions, which always win.
How do I install this agent skill?
npx skills add https://github.com/thaildhe172591/pythia --skill pythia-packageIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill provides a structured workflow and coding standards for designing and implementing PL/SQL packages and triggers. It promotes security through best practices such as utilizing sanitization libraries for dynamic SQL and avoiding the use of hardcoded credentials.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
Designing Packages, Procedures, Functions, Triggers
Announce at start: "Using pythia-package — designing the spec before the body."
Phase: Learn → Ask → Do — neighbours and callers first, the contract agreed, then spec and body as two files
pythia-write makes new code indistinguishable from the codebase. This
skill adds what the codebase may not show: how a unit should be shaped
so that callers can trust it. When the house does it differently, the house
wins — .pythia/conventions.md first, reference/plsql-standards.md for
everything it does not decide.
The Iron Law
THE SPEC IS A CONTRACT: DESIGN IT FIRST, AND NEVER BREAK IT SILENTLY
A package spec change invalidates every dependent at once; a body change invalidates nothing. That asymmetry decides most of the design.
The Workflow
- Learn —
pythia similar <NAME>andpythia srcon the top two or three;pythia argsfor every unit you will call;pythia impact <PKG>when the package exists (a spec change reaches all of it);pythia colsfor every table touched. - Shape the contract (open behaviour goes to
pythia-specfirst):- Package, not standalone, unless the house writes standalone units: encapsulation, private helpers, and body changes that invalidate nobody.
- Public surface = what callers need. Everything else private, in the body only. New public items go at the end of the spec — with fine-grained dependency tracking (11g+) existing dependents stay VALID.
- Parameters anchored with
%TYPE;INby default;OUTonly in procedures;NOCOPYfor largeIN OUTcollections and LOBs; defaults only for genuinely optional inputs. - Functions return one thing, have no
OUTparameters, and do no DML when they can be called from SQL. - Error contract: named exceptions in the spec, or one documented
-20xxxrange per package; callers can tell "not found" from "broken". - Transaction contract: the outermost caller commits. A unit that commits says so in its name or its spec comment — it cannot be composed into a larger transaction.
- Logging/instrumentation through the house logger;
DBMS_APPLICATION_INFO.SET_MODULE/SET_ACTIONon long-running entry points so a DBA can see what runs. - Security:
AUTHID DEFINERunless the spec needs invoker rights;ACCESSIBLE BY(12c) for internal helpers;DBMS_ASSERTfor any identifier that reaches dynamic SQL; no credentials in code.
- Write two files — spec, then body (one object per file,
/at the end,pythia unistrfor Vietnamese messages). Templates inreference/plsql-standards.md; comments throughpythia-comment. - Triggers stay thin: derivation and audit only — no business process,
no
COMMIT, no calls to remote systems. Several timing points on one table → one compound trigger (also the cure for ORA-04091, mutating table). Business rules belong in the API package the trigger would otherwise hide. - Check —
pythia lint spec.sql body.sql, thenpythia-review. - Land — spec first, then body:
pythia deploy spec.sql body.sql(one approval, in order) or twopythia applyruns. Exit 3 is broken, never done. - Verify —
pythia errors <PKG>,pythia invalidagainst the baseline,pythia plscope <PKG>for unused or never-assigned identifiers.
Red Flags — STOP if you catch yourself thinking
| Thought | Reality |
|---|---|
| "I'll just add a parameter to the existing procedure" | Every caller breaks or recompiles. Add an overload or a new unit at the end of the spec. |
| "WHEN OTHERS THEN log; is enough" | Without RAISE the caller believes it worked. Log, then re-raise. |
| "COMMIT at the end to be safe" | Now nobody can call it inside their transaction. The caller owns the commit. |
| "The trigger is the easiest place for this rule" | And the hardest to find, test and bypass for a data fix. API package. |
| "PRAGMA AUTONOMOUS_TRANSACTION fixes the mutating table" | It hides the error and reads stale data. Compound trigger. |
| "Concatenating the table name is fine, it's internal" | Internal today. DBMS_ASSERT.SQL_OBJECT_NAME costs one call. |
| "Standards say X, the codebase does Y" | The codebase wins; propose X separately. |
When NOT to use this skill
- A small edit inside an existing unit →
pythia-writeis enough. - Table design →
pythia-table. Performance →pythia-tune. - Reviewing someone else's unit →
pythia-review.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/thaildhe172591/pythia/pythia-package">View pythia-package on skillZs</a>