InsightWorker Logo
  • contact@verticalserve.com

Production-grade stored procedure automation with an AI worker

How InsightWorker turns a natural-language database request into generated SQL, syntax validation, target-database compilation, auto-fixes, and an auditable compile log.

← All posts
Database Automation May 7, 2026 by Hitesh Talesra 9 min read

Stored procedure work usually fails in the least convenient place: after a developer has already translated requirements into SQL, chosen a dialect, wired credentials, and pushed the code somewhere close to the real database. The first compile error sends everyone back into a manual edit-and-retry loop.

The sp-automation-app turns that loop into an InsightWorker app. It accepts a natural-language request, reads the target database profile from config.yaml, generates the stored procedure in the correct dialect, validates syntax, attempts compilation on the configured database, fixes compile errors, retries, and logs every attempt.

App snapshot

What runs in production

The app does not hardcode a database connection or target platform. The prompt chooses a configured database profile such as oracle_db or postgresql_db, and InsightWorker reads the host, schema, wallet path, retry count, and log path from the app config. Public pages and logs should always redact secrets.

Prompt-driven Turns natural language requirements into procedure logic and parameters.
Config-backed Reads database type and connection profile from config.yaml.
Compile-tested Validates syntax and compiles directly on the target database.
Self-correcting Uses compile errors to fix SQL and retry up to the configured limit.

The database problem this solves

Stored procedure automation has two common failure modes. First, generic SQL generation ignores the target dialect, so Oracle PL/SQL, PostgreSQL functions, and SQL Server procedures get blurred together. Second, generated SQL may look plausible but still fail when compiled against the real schema.

This app treats compilation as part of generation. It does not stop at a text artifact. It keeps going until the procedure is syntactically valid for the configured database, or until it reaches the configured retry ceiling and reports what still needs a human decision.

Production app design

1

Capture the request

The user describes the procedure goal, target table, database profile, filters, grouping rules, and expected output.

2

Read configuration

The app loads the requested database profile, retry limit, logging flag, and log file path from config.yaml.

3

Generate SQL

InsightWorker generates dialect-specific procedure code, including parameters, output cursor or result shape, and comments.

4

Validate syntax

The generated procedure is checked for dialect and structural issues before it is sent to the database compiler.

5

Compile and fix

If compilation fails, the app reads the database error, patches the SQL, and retries until it compiles or hits max attempts.

6

Archive artifacts

The final procedure and compile attempts are saved under the app output and log folders for review.

Configuration, not hardcoding

The important production detail is that the app does not bake Oracle, PostgreSQL, schema names, wallet paths, or retry counts into the article logic. It reads those values from app-level config. The example below is intentionally redacted for public use.

oracle_db: username: ADMIN password: "[REDACTED]" dsn: "[CONFIGURED_DSN]" wallet_path: "[CONFIGURED_WALLET_PATH]" wallet_password: "[REDACTED]" postgresql_db: username: "[CONFIGURED_USER]" password: "[REDACTED]" host: "[CONFIGURED_HOST]" port: 5432 database: "[CONFIGURED_DATABASE]" max_compile_attempts: 5 logging_enabled: true log_file_path: "logs/compile_attempts.log"

That means a prompt can say Use oracle_db or Use postgresql_db, and the app selects the matching connection profile from config.yaml. The stored procedure name, table, and business logic come from the prompt and schema context, not from a hardcoded demo path.

Dialect and type mappings

The app also keeps cross-database mappings in a separate mapping file. That gives the generator a structured way to translate common types and functions when the same business logic moves across platforms.

Mapping area Example Production purpose
Types NUMBER to NUMERIC, VARCHAR2 to VARCHAR Prevents simple dialect mismatches when generating or porting procedure code.
Functions SYSDATE to CURRENT_TIMESTAMP Keeps date/time logic platform-aware instead of relying on generic SQL.
Validation Compile attempt logs and retry status Separates a generated draft from a procedure proven valid by the target database.

Example prompt and generated procedure

One reference run asks the app to generate an Oracle ADW procedure that calculates total rental amount by customer for a specified year using the RENTAL table.

insightworker> /app run sp-automation-app "Please generate a stored procedure for calculating the total rental amount for each customer for the specified year. Use oracle_db as the database type and use table RENTAL."

The output is a dialect-specific procedure artifact. In the sample run, the app generated an Oracle procedure with p_year as input and a SYS_REFCURSOR output.

CREATE OR REPLACE PROCEDURE ADMIN.SP_CALC_TOTAL_RENTAL_BY_CUSTOMER ( p_year IN NUMBER, p_result OUT SYS_REFCURSOR ) AS BEGIN OPEN p_result FOR SELECT r.CUSTOMER_ID, SUM(r.ACTUAL_AMOUNT) AS TOTAL_RENTAL_AMOUNT FROM ADMIN.RENTAL r WHERE EXTRACT(YEAR FROM r.PICKUP_DATETIME) = p_year GROUP BY r.CUSTOMER_ID ORDER BY TOTAL_RENTAL_AMOUNT DESC; END SP_CALC_TOTAL_RENTAL_BY_CUSTOMER; /

Compile retry loop

The strongest part of this app is not the first draft. It is the loop after the first draft. The compile log shows a booking procedure fail on the first attempt with a PL/SQL syntax error, then compile successfully after the app patched the procedure and retried.

2026-05-07 15:50:09 | ATTEMPT 1 | SP_CALC_TOTAL_BOOKING_BY_CUSTOMER | FAILED | PLS-00103: missing semicolon after END 2026-05-07 15:50:09 | ATTEMPT 2 | SP_CALC_TOTAL_BOOKING_BY_CUSTOMER | COMPILED SUCCESSFULLY | Status: VALID 2026-05-07 15:51:47 | ATTEMPT 3 | SP_CALC_TOTAL_BOOKING_BY_CUSTOMER | COMPILED SUCCESSFULLY (v2 hardened) | Status: VALID

The hardened version added safer conversions for string-stored amount and date fields, explicit NLS date language handling, and exception cleanup for open cursors. That is the practical difference between code generation and production automation.

Production safeguards

  • Secrets stay in app config and should be redacted from public docs, logs, and screenshots.
  • The database profile is selected by name from config.yaml, so Oracle or PostgreSQL targets are not hardcoded in the app page.
  • max_compile_attempts controls the retry ceiling, preventing endless fix loops.
  • Every compile attempt is logged to logs/compile_attempts.log when logging is enabled.
  • Connection failures are reported separately from SQL syntax failures, so teams know whether to fix code or connectivity.

What it does not replace

This app does not replace schema design, indexing strategy, performance testing, or business-rule ownership. It accelerates the mechanical path from requirement to compiled procedure. Humans still decide whether the logic is correct, whether it is performant enough for production data volumes, and whether it belongs in the database layer.