Class OpenCursorCheck

java.lang.Object
com.mcpdbwizard.pub.OpenCursorCheck
public final class OpenCursorCheck extends Object
Warns, once at start-up, when a config publishes more tools than the database session can hold open cursors for.

Why this exists

A generated DAO prepares each statement once and keeps the handle — DmlStatement.createPreparedStatement() only prepares when thePreparedStatement is null, and closes it in freeConnection(). That is deliberate reuse and it is the right design: the second call to a tool costs no parse. The consequence is that a session's open-cursor count grows with the number of distinct tools it has been asked for, and never with how many times they are called. Once every tool has been used once, the count stops rising.

So a server that exposes more distinct tools than open_cursors — 300 by default, and 300 on every box this was measured against — will eventually raise ORA-01000: maximum open cursors for session exceeded, no matter how gently it is used.

Why it is worth a check rather than a note in a manual

Measured behaviour, because it decides how this failure presents. Hitting the cap throws, the throw releases the cursors, and the count climbs again from zero — so the server does not stop. It fails one call in every open_cursors, on a different tool each time. At 512 tools that was five failures in 1536 calls: 0.3%, scattered, which reads as a transient database blip rather than a configuration that has outgrown a parameter. A deployment can run in that state for a long time without anyone connecting the errors to the config's size.

It is also invisible to load testing, which is the other reason to say it at start-up. Because the cost is per distinct tool, a million calls through four tools opens four cursors. Only breadth finds this, and load tests are built for depth.

One cursor per tool is the FLOOR, not the rate

Measured per-tool cost against a real schema: most scalar routines cost 1, a routine that returns a REF CURSOR or moves a LOB cost 3, and one that executes no SQL costs 0 — an average of about 1.3 across a 17-tool sample. That is why WARN_AT_FRACTION fires well below the limit rather than at it: a server with 250 tools against open_cursors=300 looks safe by counting and is not.

Warn, never refuse

This logs and returns. Refusing to start would be wrong twice over: the estimate is an estimate, and a server whose callers only ever touch a handful of its tools will never reach the limit at all. The failure being prevented is a silent one, and a loud sentence prevents it.

When V$PARAMETER cannot be read

Reading it needs a privilege — usually SELECT_CATALOG_ROLE or an explicit grant — and whether a DAO account has it varies by account rather than by database: of four accounts on the development estate, three could read it and one could not. So this is neither the normal path nor a rare one.

When it cannot be read the check does not assume a limit. It reports that open_cursors is unknown and that the tool count may exceed it. Guessing Oracle's shipped default would be worse than saying nothing: a site that has raised open_cursors — the common adjustment on a busy system — would be warned on every start-up about a config that fits comfortably, and a start-up warning that cannot be silenced and is not true is exactly how a check gets ignored. ORACLE_DEFAULT_OPEN_CURSORS is therefore documentation for the reader of the message, not an input to any decision. Copyright 2003-2026 ATB Consultancy Services Ltd (formerly Orinda Software Ltd, Dublin, Ireland)

  • Field Details

    • ORACLE_DEFAULT_OPEN_CURSORS

      public static final int ORACLE_DEFAULT_OPEN_CURSORS
      Oracle's shipped open_cursors. Quoted in the unknown-limit message so the reader has a number to compare against; deliberately NOT used as a fallback limit — see the class comment.
      See Also:

    • WARN_AT_FRACTION

      public static final double WARN_AT_FRACTION
      Warn once the tool count reaches this share of the limit. Deliberately not 1.0: a tool costs one cursor at best and three at worst, so parity between the counts is already over.
      See Also:

  • Method Details

    • warnIfToolsExceedCursors

      public static void warnIfToolsExceedCursors(Connection theConnection, int theToolCount, LogInterface theLog)
      Log a warning if this config publishes enough tools to exhaust the session's cursors.
      Parameters:
      theConnection - the DAO's own connection — the session that will hold the cursors, which is why this cannot be checked from an application that merely knows about the config. A null connection is ignored.
      theToolCount - how many tools this server publishes
      theLog - where to say so; a null log is ignored