Working with procedures

PL/SQL is the reason this product exists: call any Oracle routine, whatever its inputs and outputs.

The Procedures tab listing packaged procedures with checkboxes — The **Procedures** tab. The count beside the heading is what the connected account can see; the ticks are what gets generated. An unticked routine has no code emitted for it at all, which is the whole security model — not a runtime filter.

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.

Collection and record parameters need Oracle types

JDBC cannot bind a PL/SQL collection directly — only an array built on real SQL types crosses. So for any routine taking a collection or record parameter the generator invents a matching pair per record shape, an object type <prefix>_T and a collection type <prefix>_A, and binds through those. Their DDL goes into extraObjects.sql beside the generated code.

Usually you never see it. A generated server creates these types itself at start-up, when one of its tools actually needs them. It only becomes your problem if the connected account has no CREATE TYPE — and then every collection parameter is unusable until somebody runs the DDL.

Runtime → the config’s card → Download extraObjects.sql. One file, every type the config needs, offered as soon as the config has been generated rather than only after something has failed. Hand it to whoever holds CREATE TYPE on that schema and have them run it once as the schema owner.

The Runtime page also reports which types are still missing after a server starts, so you can tell “nobody has run it yet” from “the database was refreshed underneath a config that was working”.

These are created with CREATE OR REPLACE, which Oracle refuses once anything depends on the type. If the underlying record has changed, the old definition survives — and binding against it would move values into the wrong columns silently. The server stops and prints both definitions rather than guess. See the FAQ.

Your collection’s subscripts are preserved

A PL/SQL index-by table is keyed by whatever BINARY_INTEGERs your code chose — 0-based, sparse, negative — while the SQL collection it travels in is keyed 1..COUNT. Each element therefore carries its original subscript in a MCPDBWIZARD_POS attribute, so a collection you read back keeps its keys and goes back in at the same ones instead of being quietly renumbered. A collection you build yourself has no subscript to preserve and binds densely.

This is why the generated types have one more attribute than your record has fields. Types you declared are never altered; only the ones MCPDBWizard generates carry it.

On Oracle 12c, a collection cannot travel both ways in one call

A routine that takes a record collection in and returns one out will not bind on Oracle 12c. You get

Attempt to set a non-existent parameter number 4; legal range is '1' to '2'

12c only, and only that combination — a collection that is merely returned works there, and 18c and later are unaffected. Its ALL_ARGUMENTS expands a record parameter into child rows where later versions report only the parameter itself, and the bind positions are counted across the expansion.

On 12c, split the routine so the collection travels one way per call: one procedure that accepts it, one that returns it. Nothing is needed on 18c or later. See known issues.

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.