Skip to content

Behavioral Differences from Real orafce

Every item below is a deliberate choice, not an oversight — most exist because real orafce relies on a runtime-configurable GUC that requires C to implement, or on a specific unreproducible behavior. See How to Read This Book for what this chapter does and doesn't cover.

oracle schema

oracle.sys_guid()

Real orafce dynamically calls whichever function the orafce.sys_guid_source GUC names, defaulting to uuid_generate_v1 from the uuid-ossp extension — confirmed empirically that real oracle.sys_guid() errors outright (extension "uuid-ossp" is not installed) if that extension isn't present.

orafce_no_c always uses core PostgreSQL's own gen_random_uuid() (a random v4 UUID) instead, regardless of any GUC — core has shipped this since PostgreSQL 13, and it needs no extra extension. Both produce a random 16-byte value with no meaningful cross-implementation value comparison (the same category of non-determinism as dbms_random, below) — this is a deliberate improvement in dependency-freedom, not a behavioral gap in the contract itself.

oracle.sysdate() / oracle.dbtimezone()

Real orafce's orafce.timezone GUC (default 'GMT', runtime-configurable) lets a DBA emulate an Oracle server timezone independent of the session's own timezone.

orafce_no_c hardcodes the GUC's own default value, 'GMT' — there's no way to configure this to anything else, since custom GUCs require C. The default behavior is preserved exactly; only the ability to change it away from GMT is lost.

oracle.to_date() (both forms)

Real orafce's 1-argument form uses the orafce.nls_date_format GUC if set (default: unset); orafce_no_c always takes the unset/default path (plain PostgreSQL timestamp parsing) — there's no way to configure a custom NLS date format.

The 2-argument form's behavior is additionally gated by orafce.emit_error_on_date_bug (default true), which — when true — replicates a documented historical bug in real Oracle Database itself: dates before 1582-10-05 (Julian-day format) or 1100-03-01 (any other format) raise an error, because Oracle itself cannot reliably verify such old dates. orafce_no_c always assumes the GUC's default (true) — this replication can never be turned off here, whereas real orafce could disable it via the GUC.

oracle.to_char(timestamp) has the same orafce.nls_date_format limitation, though in practice this makes no observable difference under default configuration (both fall back to the same plain output).

oracle.substr(text, int, int) / plvstr.substr(text, int, int)

Real orafce's behavior when the length argument is exactly 0 depends on the orafce.substring_length_is_zero GUC (four possible settings). orafce_no_c always assumes the documented default, warning_oracle: emit a WARNING and return NULL. Both functions share this exact same limitation, since they call the same underlying logic in real orafce.

oracle.to_multi_byte() / oracle.to_single_byte()

Real orafce supports three encodings via three separate lookup tables: UTF8, EUC_JP/EUC_JIS_2004, and EUC_CN. orafce_no_c supports UTF8 only — the legacy Japanese/Chinese mapping tables weren't judged worth transcribing. In a database running in EUC_JP or EUC_CN encoding, both functions silently do nothing (pass the input through unchanged) instead of real orafce's actual conversion. Within UTF8, fidelity is exact.

oracle.regexp_instr(...) with a group/subexpression argument

Getting a specific capture group's own position (not just its captured text) is something real orafce's C code gets directly from the regex engine's internal group tracking — there's no SQL-level equivalent, since regexp_matches() only returns captured text, not position. orafce_no_c instead locates the group's text within the already-found whole match via strpos().

This is correct for the overwhelming majority of patterns, but can misidentify the position if two groups within the same match capture identical text — e.g. pattern (a)(a) matching "aa": both groups capture "a", and strpos() reports the same (first) position for either group, where the real regex engine correctly distinguishes them. Worth knowing if you rely on regexp_instr(..., group => N) position values against patterns with duplicate-content capture groups.

plunit

plunit.assert_equals / plunit.assert_not_equals

When the two arguments' resolved type has no default = operator, real orafce raises a custom error (ERRCODE_INVALID_PARAMETER_VALUE, "unknown equal operand for datatype"). orafce_no_c instead surfaces PostgreSQL's own native "operator does not exist" error (SQLSTATE 42883) — replicating the custom message would need a pg_operator lookup ahead of every call, for a case that doesn't arise for any type actually used in practice. If you catch this specific SQLSTATE or message text from real orafce, expect a different one here.

plvstr

plvstr.is_prefix(text, text, boolean) — the case-insensitive path

Real orafce crashes here in a multibyte-encoding database ("could not determine which collation to use for lower() function"), confirmed on multiple PostgreSQL versions. orafce_no_c implements a correct, working case-insensitive comparison instead of reproducing the crash — unlike the two preserved bugs in Confirmed Bugs Found in Real orafce, this one had no legitimate successful behavior to protect: real orafce always errors here, so there's no existing application code that could be relying on a specific working-but-wrong answer.

oracle.substrb()

Real orafce's byte-oriented substring can return an invalid, truncated multi-byte sequence when the requested byte range splits a character — and that invalid result isn't just "different," it can crash the next call that touches it (length() raising invalid byte sequence for encoding). orafce_no_c always returns a valid result instead, by including only characters whose entire byte span falls within the requested window. This is also confirmed technically impossible to reproduce faithfully in pure SQL — every standard way of constructing a text value validates its encoding, so replicating the exact invalid output isn't an option even if it were desirable. See Confirmed Bugs Found in Real orafce for the full detail on the underlying bug.

Not divergences, but worth knowing

A few functions are faithful, unchanged ports of real orafce's actual behavior, but that behavior is different enough from ordinary SQL or plain PostgreSQL conventions that it's worth flagging so it isn't mistaken for a bug:

  • oracle.greatest/oracle.least are NULL-propagating — oracle.greatest(1, NULL::int) is NULL, not 1. This matches real orafce exactly, but differs from core PostgreSQL's own GREATEST()/LEAST(), which silently skip NULLs. Don't assume oracle.greatest/oracle.least behave like the plain SQL keywords of the same name.
  • oracle.decode treats NULL = NULL as a match (Oracle's own DECODE semantics), unlike ordinary SQL equality.
  • oracle.lnnvl and oracle.nanvl both implement Oracle-specific NULL/NaN substitution rules, not generic coalescing.

Continue to Confirmed Bugs Found in Real orafce.