Working with procedures

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.

PL/SQL is the reason this product exists: call any Oracle routine, whatever its inputs and outputs. A single-OUT shortcut would be a defect, not a design.

What a routine tool looks like

Tool names are lower-cased Oracle names joined by underscores:

<owner>_<package>_<routine>

Overloads get a number appended, so two never collide.

Every OUT parameter comes back

A PL/SQL tool returns every OUT and IN OUT parameter as one object keyed by parameter name, with a function’s return value under result. A routine with six OUT parameters gives you all six.

Records and SQL object types cross as objects keyed by field, collections as arrays, and an OUT ref cursor as an array of row objects. See supported data types.

What is skipped, and why

Types that cannot cross JSON honestly are not exposed: SDO_GEOMETRY, BFILE, IN ref cursors and FLOAT16 vectors. A routine using one is skipped whole, and the generation log says which and why — so an object missing from your tool list has a reason you can read.

Commit handling in called procedures

This is the part to think about before you expose anything that writes.

Nothing in Oracle’s dictionary says whether a procedure writes — ALL_ARGUMENTS and ALL_OBJECTS are silent on it, and the generator never parses bodies. That has three consequences worth stating plainly:

PL/SQL routines carry no readOnlyHint at all. An absent hint means unknown, not safe. MCP clients use that hint to decide whether to auto-approve a call without asking the user, so a wrong true could cause a silent write. Absent is the honest answer.
A read-only master switch is a won’t-do for the same reason. It could only filter the structurally-knowable surfaces — table insert/update/delete, sequence nextval — while every exposed procedure stayed callable and free to write. A switch that reads as a guarantee and is not one is worse than no switch.
Your procedure’s own transaction control still applies. If it commits, it commits.

Where the surrounding transaction ends depends on how the server is configured:

	Transaction ends
Unpooled	when the connection is released (`CLOSE_CONNECTIONS`)
Pooled	when the caller finishes and returns its factory (`DAO_POOL_ON_RETURN`)

Turning pooling on moves that boundary. If your PL/SQL relies on the caller committing, or does its own COMMIT, check the behaviour before and after.

To write. A worked example with records and collections; what an autonomous transaction does here; error mapping from RAISE_APPLICATION_ERROR; guidance on writing tool descriptions for routines whose names do not explain themselves.