Documentation  ·  Curating

Working with SQL statements

This page is an outline. What is here is accurate, but it is not yet the whole story — each section ends with a note on what is still to be written. For anything it does not answer, DEPLOYMENT.md and USING-MCP.md in the repository are the complete references.

The fourth surface is your own SQL: statements you wrote, tested and chose to expose. The agent never composes SQL and never sees a SQL prompt — it calls a tool you named, with parameters you defined.

One config entry yields one tool, so declining to select a statement is the control, exactly as for sequences.

Test your SQL statements elsewhere first

Write and test the statement in SQL Developer, SQL*Plus or whatever you normally use, against realistic data, before it goes anywhere near a config. The generator’s job is to expose the statement you gave it — it will not tell you the query is wrong, slow, or returns the wrong rows.

Specifically, check before you expose:

• It returns what you meant on realistic data volumes, not on three test rows.
• Its plan is acceptable and it uses the indexes you think it does. An agent may call it far more often than a human would.
• It behaves at the edges: no rows, null parameters, the largest result you can imagine an agent asking for.
• You are content for it to run as written. A selected statement is exposed as written, destructive DML and DDL included. That is by design.

Pair this with DAO_QUERY_TIMEOUT_SECONDS — see Session management — so a statement that misbehaves in production stops rather than holding a connection.

Naming parameters and giving them types

A bind variable’s name becomes the tool’s argument name, and its declared type becomes the argument’s JSON type. Both are worth choosing deliberately: they are what the model reads.

-- Vague: an agent has to guess what :1 and :2 are
SELECT * FROM orders WHERE cust_id = :1 AND created > :2

-- Better: the names are the documentation
SELECT order_id, order_date, total_eur
FROM   orders
WHERE  customer_id = :customer_id
AND    order_date  > :placed_after

Name parameters as you would name a function’s arguments, not after the column they happen to filter. Then add a tool description saying what the statement answers and when to use it — that is what decides whether the model picks this tool or a different one.

To write. The exact syntax for declaring a parameter’s datatype in a config; worked examples for each type; what happens with a parameter used twice; how result columns are named.

Supported data types

Not crossable, so not exposed: SDO_GEOMETRY, BFILE, IN ref cursors, FLOAT16 vectors, and timestamps with time zones on the table-row path. Anything using one is skipped whole, and the generation log says which and why.

Schemas are strict

Tool schemas set additionalProperties: false, so a misspelled argument is rejected before the handler runs and the database is never touched. The refusal still comes back as a result with content, so it can look like a call that ran — check argument names against tools/list.

To write. Bind-variable syntax reference; multi-row versus single-row results; how nulls are represented in and out; worked examples of a report statement and a lookup statement.