Oracle DB

What This Skill Does

The Oracle DB skill empowers the OpenClaw agent to act as a proficient Oracle database developer and administrator. It provides the agent with deep knowledge of Oracle-specific SQL dialect, PL/SQL procedural programming, and performance optimization patterns. The skill ensures that the agent follows Oracle best practices, such as utilizing bind variables to prevent shared pool fragmentation, mastering hierarchical CONNECT BY queries, and correctly handling Oracle's unique constraints—like the fact that empty strings are treated as NULL.

Installation

To integrate this capability into your OpenClaw environment, execute the following command in your terminal: clawhub install openclaw/skills/skills/ivangdavila/oracle-db

Use Cases

• Database Migration & Refactoring: Automating the translation of standard SQL queries or queries from other databases (like PostgreSQL or MySQL) into compliant, high-performance Oracle SQL.
• Performance Tuning: Analyzing slow-running queries and suggesting the correct SQL hints (e.g., INDEX, PARALLEL, FULL) to optimize execution plans.
• Complex Reporting: Writing hierarchical queries using START WITH and CONNECT BY to extract tree-structured data from relational tables.
• PL/SQL Automation: Generating stored procedures, triggers, and packages, including robust exception handling and dynamic SQL execution.
• Pagination Logic: Handling row limiting and offset pagination, even for legacy Oracle versions that lack the OFFSET/FETCH syntax.

Example Prompts

1. "Refactor this query to use an Oracle-compliant approach for pagination, ensuring it supports versions prior to 12c, and include an index hint for the 'orders' table."
2. "Write a PL/SQL block that iterates through a result set of customer IDs, calculates a running discount, and updates the 'total_due' column using a transaction savepoint."
3. "Convert this list of timestamps into a date format compatible with Oracle using TO_DATE, and explain how to perform date arithmetic to find all entries from exactly 48 hours ago."

Tips & Limitations

• Bind Variables: Always favor bind variables over literals. Hard-coded values cause unnecessary hard parses, which lead to significant memory contention in the Oracle Shared Pool.
• Versioning: Note the differences between Oracle 11g and 12c+. The agent is optimized to suggest modern features (like Identity columns) when appropriate but can fall back to legacy methods like Sequences and ROWNUM when needed.
• Data Integrity: Remember that DDL statements in Oracle (like CREATE TABLE or ALTER) trigger an implicit COMMIT. Always plan your transaction blocks carefully when mixing DDL and DML.
• NULL Handling: Be extremely cautious when comparing strings; since '' is treated as NULL, equality checks should use NVL or explicit IS NULL checks to avoid logical bugs.