Confirmed Bugs Found in Real orafce

These are genuine bugs and surprising quirks confirmed in real orafce itself — not introduced by this port. See How to Read This Book for the distinction between "preserved bug-for-bug" and "fixed," and why each choice was made case by case.

dbms_output

Six behavioral quirks were confirmed empirically against the real C extension — none obvious from a first read of the source — and all six are preserved exactly, since dbms_output is meant as an exact behavioral match:

1. A bare put() (no line-closing put_line()/new_line()) is immediately retrievable via get_line(), even before the line is closed.
2. Once any get_line()/get_lines() call has retrieved something, the next put()/put_line()/new_line() discards the entire remaining (even unread) buffer before appending new content.
3. Buffer-overflow accounting is cumulative bytes appended since the last full reset — it is not reduced by reading with get_line(). Interleaving a single read between writes resets the counter (via quirk 2 above).
4. dbms_output.enable(n) clamps n to [2000, 1000000] before comparing it against bytes already used — re-enabling with a small requested size can grow or shrink the effective buffer depending on the clamped value versus what's already buffered, not the raw requested value.
5. dbms_output.disable() does not reset the "server output" flag — only serveroutput() touches it, so disable() followed by a bare enable() can leave immediate-echo-to-client behavior active if it was turned on earlier.
6. When server-output mode is active, closing a line immediately raises the buffered content as a NOTICE and clears the buffer — that content is no longer retrievable via get_line()/get_lines() afterward.

dbms_random

Every randomness-producing function in this package (initialize, normal, random, seed, string, value) has one fundamental limitation worth understanding before relying on exact-value reproduction: real orafce's own reference implementation is not reproducible across platforms.

Confirmed empirically, not assumed: real orafce seeds and draws from the host libc's rand()/srand(), which is not a portable algorithm. Calling dbms_random.initialize(8) then dbms_random.normal() on the real, compiled C extension gives different values on macOS versus Linux glibc — for the identical seed and identical call sequence. orafce's own shipped regression test baseline is itself platform-specific.

This means byte-for-byte matching between orafce_no_c and real orafce was never an achievable or meaningful bar for these functions in the first place — orafce_no_c uses core PostgreSQL's own random()/setseed() instead, and testing is done by statistical shape and contract (same distribution characteristics, correct range, seedable determinism within a single platform) rather than exact-value comparison. If your own tests compare specific dbms_random output values against a real-orafce baseline captured on a different platform, expect them to disagree — that disagreement exists in real orafce too, from one platform to another.

Two smaller, related notes:

• dbms_random.seed(text) hashes its input to derive a seed. Real orafce's own C source explicitly warns that its hash function's result "is not stable across PostgreSQL major versions" — so this had no stronger cross-version guarantee to begin with, on either side.
• dbms_random.string()'s "printable" character set contains a confirmed typo in real orafce ('ZXCVVBNM', with a doubled V) — preserved verbatim here for bug-for-bug fidelity, though it has no functional consequence beyond the charset's exact contents.

oracle schema

oracle.substrb()

The most significant fix in this project. Real orafce's byte-oriented substring is a naive byte-level slice with no character-boundary awareness at all. On the string 'héllo' (raw bytes 68 c3 a9 6c 6c 6f), substrb(str, 1, 2) returns the raw two bytes 68 c3 verbatim — an invalid, truncated UTF-8 sequence (the é character's lead byte, with its continuation byte cut off) — with no validation and no error at the point of construction.

This isn't just "different," it's unsafe: calling length() on that returned value afterward raises invalid byte sequence for encoding UTF8. Real orafce's substrb can hand back a value that crashes the very next thing that touches it. This was confirmed via raw byte inspection, not just by eyeballing SELECT output — an earlier look at rendered output wrongly suggested it trimmed cleanly to whole characters, because the display layer silently absorbed the invalid byte.

orafce_no_c fixes this rather than preserving it — the same precedent used for plvstr.is_prefix's collation crash (see Behavioral Differences): only characters whose entire byte span falls within the requested range are included, so the result is always valid, safely-usable text. This is also confirmed technically impossible to reproduce faithfully in pure SQL in the first place — every standard way of constructing a text value validates its encoding, so there's no way to even build the exact invalid output on purpose.

oracle.to_date() — Oracle's own historical date-verification bug

This one isn't a bug in orafce — it's a documented bug in real Oracle Database itself that orafce faithfully reproduces, and orafce_no_c reproduces in turn: dates before 1582-10-05 (Julian-day format) or 1100-03-01 (any other format) raise an error, because Oracle itself cannot reliably verify dates that old. If you're relying on parsing very old historical dates through oracle.to_date(), this is expected, faithful behavior — not a defect in this port.

oracle.remainder()

Confirmed empirically: a divisor of -1 always short-circuits to exactly 0 (avoiding a rounding edge case), and remainder(NaN, n)/remainder(n, NaN)/remainder(Infinity, n)/remainder(n, Infinity) all evaluate to NaN. Real orafce's own C source has explicit NaN/Infinity handling for some of these cases that turns out to be dead code — never actually reached, since PostgreSQL's own numeric arithmetic already produces the same NaN results naturally. orafce_no_c relies on that natural behavior directly; the observable output matches exactly either way.

The shared "negative-start substring" helper

oracle._ora_substr, plvchr._ora_substr, and plvstr._ora_substr all implement the same underlying real-orafce C function (ora_substr), used internally by plvchr.nth/first/last among others. Its handling of a negative start position is a genuine, confirmed quirk worth knowing: a negative start counts back from the end of the string, and if that computed position lands at or before position 0, the function returns an empty string — it is not clamped to position 1 the way some substring implementations handle an out-of-range start. This is faithfully preserved throughout.

plvdate

plvdate.bizdays_between()

Real orafce's business-day-counting loop has a confirmed, non-obvious quirk in how its internal day-of-week tracking advances relative to the day being counted. The net, empirically-confirmed effect: calling with day1 == day2 (a single day) returns 1 if that day is a business day — meaning the loop's true semantics count business days over the range (day1, day2] (day1 exclusive, day2 inclusive), with the separate include_start flag afterward deciding whether to add day1 back in when it was itself a business day. orafce_no_c reproduces this exact arithmetic, variable for variable — there is no "fixed" alternative offered here, so if you use bizdays_between, know that its core counting is exclusive-of-start by default.

plvstr

Two confirmed bugs here are deliberately preserved by default, each with an explicit, opt-in fixed alternative — because each produces a deterministic, reproducible result that existing application code could plausibly already depend on, unlike is_prefix's crash (see Behavioral Differences), which had nothing legitimate to protect.

plvstr.normalize() — the stuck-loop bug

In a multibyte-encoding database (this includes UTF8 and a dozen others), encountering a single-byte control character (ASCII 1–31, excluding tab/newline/CR) makes real orafce's implementation get permanently stuck — every character from that point onward is silently dropped from the output. Confirmed: normalize('a' || chr(1) || 'bc') returns 'a', not 'abc'. (In single-byte encodings, no such bug occurs.)

-- Default: exact bug-for-bug replica of real orafce.
SELECT plvstr.normalize('a' || chr(1) || 'bc');           --> 'a'

-- Opt in to the fixed behavior: drops just the control character,
-- keeps processing the rest of the string.
SELECT plvstr.normalize('a' || chr(1) || 'bc', true);     --> 'abc'

The fixed version is also available directly as plvstr._normalize_fix(str), and the bug-for-bug version as plvstr._normalize_bug(str), if you need to call either explicitly rather than through the dispatcher's boolean flag.

plvstr.betwn() — the off-by-one bug (integer-position form)

When start is negative, real orafce's recomputed "end" position reuses the just-reassigned start value instead of the caller's actual end argument — the end parameter is silently discarded in this branch. Confirmed: betwn('abcdefghij', -5, -1) and betwn('abcdefghij', -5, -2) return byte-identical results, since end is ignored either way.

-- Default: exact bug-for-bug replica of real orafce.
SELECT plvstr.betwn('abcdefghij', -5, -1, true);            -- ignores the -1

-- Opt in to the fixed behavior: recomputes "end" from the actual
-- end argument, giving negative start/end the same symmetric
-- "count from the end" meaning you'd expect.
SELECT plvstr.betwn('abcdefghij', -5, -1, true, true);

The fixed version is also available directly as plvstr._betwn_fix(str, start, end, inclusive), and the bug-for-bug version as plvstr._betwn_bug(str, start, end, inclusive). Note the 3-argument convenience form, plvstr.betwn(str, start, end), keeps its original signature unchanged — to get the fix through a short call, use the 4-argument dispatcher explicitly: plvstr.betwn(str, start, end, true, true).

plvsubst

plvsubst.string() — empty substitution keyword

An empty keyword ('') matches at every character position in the template, and since the scan advances by the keyword's own (zero) length, it repeatedly "matches" the same position rather than advancing through the template. This is bounded, not a true infinite loop — real orafce's own values-array exhaustion check kicks in almost immediately, always terminating via a "too few parameters specified for template string" error rather than looping character-by-character over the whole template. Confirmed and preserved exactly, with no fixed alternative offered.

plvsubst.setsubst(NULL)

Real orafce's C function body has an explicit NULL-check that would raise "substitution is NULL" — but the function is declared STRICT, so PostgreSQL intercepts the NULL argument at the call boundary and returns NULL without ever invoking that check. The error path is genuinely unreachable through the actual SQL interface — confirmed empirically: setsubst(NULL) is a silent no-op on the real extension, not an error. orafce_no_c is declared STRICT the same way, so the observable behavior (silent no-op) already matches without needing to reproduce the unreachable error text.