orafce_no_c v1.0.0 is out — a complete reimplementation of a large subset of orafce, the popular PostgreSQL extension that emulates Oracle's built-in packages (DBMS_OUTPUT, DECODE, NVL, TO_CHAR, business-day calendars, and a lot more), written entirely in PL/pgSQL and SQL. No C. No compiled '.so'. No 'make USE_PGXS=1 install'.
The obvious pitch is portability: if you're on a managed or cloud PostgreSQL service that won't grant filesystem access to the extension directory, real orafce is simply off the table, and orafce_no_c isn't. But that's not the only reason to reach for it — including on servers where the real, compiled extension is available to you right now. Here's the fuller case, plus the practical detail on what's included, what isn't, how to check your own code before you switch, and what it actually costs in performance.
It fixes real bugs, not just reproduces them. Porting every function meant testing it side-by-side against the real, compiled C extension across five PostgreSQL versions — and that process surfaced genuine defects in orafce itself, not just porting challenges:
oracle.substrb(), orafce's byte-oriented substring, does a naive byte-level slice with no character-boundary awareness. Ask it for the first two bytes of 'héllo' and it hands you an invalid, truncated UTF-8 sequence — the lead byte of é with its continuation byte cut off. The very next thing that touches that value (even a plain length() call) raises 'invalid byte sequence for encoding UTF8'. That is a quirky edge case, but it is a possibility of a function that can crash its caller.plvstr.normalize() gets stuck the moment it hits a single-byte control character in a multibyte-encoding database — every character after that point is silently dropped from the output.orafce_no_c fixes the two structurally-fixable bugs outright (the substring crash and a related collation-resolution crash) and preserves the others bug-for-bug by default, with an explicit opt-in flag where a fix is available — because some of those quirks are old enough that real application code may already depend on the exact broken behaviour. Either way, you get to make that call deliberately, function by function, documented in detail, instead of inheriting undocumented C behaviour you can't easily inspect.
There's no compiled artifact tied to a specific PostgreSQL ABI. Real orafce's .so is built against one server's pg_config; every major version bump is a rebuild-and-reinstall event, and pg_upgrade across major versions means getting a matching binary in place for both the old and new cluster before the upgrade will even proceed. orafce_no_c has nothing to recompile, ever — the identical SQL script installs unchanged on PostgreSQL 14 through 18, tested and confirmed.
You can actually read it. Every function is plain PL/pgSQL or SQL, sitting in your own schema, \ef-able, EXPLAIN-able, diffable in a code review. A compiled shared library is opaque by comparison — you're trusting behavior you can't easily audit yourself.
You are not paying a performance tax for any of this. in some cases the SQL version is measurably faster than the C original, for architectural reasons, not noise.
None of this is an argument that real orafce is bad software — it's mature and widely used. It's an argument that "no C toolchain available" isn't the only good reason to choose orafce_no_c. We will prepare test information and supply to orafce so that the identified problems can be fixed in future versions. We used orafce 4.16.7 as our base version for porting.
Not everything can be done without C, and orafce_no_c doesn't pretend otherwise. 10 of orafce's 15 packages are fully included; 5 are excluded outright, each for a specific structural reason — a genuine OS or shared-memory capability that PL/pgSQL cannot reach, not a gap in effort:
Across all 15 packages that's 247 objects total, of which 162 ship in the core install (plus an optional VARCHAR2/NVARCHAR2 add-on, more on that below). Every excluded function is documented with the specific reason it's excluded — nothing is silently dropped. We decided to not include partially portable packages -- it is all or nothing (except for functions in the oracle schema where the majority are implemented, but some are not).
You don't have to guess or grep your codebase by hand. oracle.orafce_audit() is a one-off tool (not installed by orafce_no_c itself) that you run against a database that still has real orafce installed:
-- Everything you use that orafce_no_c does NOT cover:
SELECT * FROM oracle.orafce_audit();
-- Every individual call site: schema, function, line number:
SELECT * FROM oracle.orafce_audit(p_summary => false);t works by introspecting pg_depend/pg_extension to figure out, live, exactly which objects belong to orafce in your database — so it stays accurate regardless of which orafce version you have, rather than relying on a hardcoded function list. It scans every PL/pgSQL function, procedure, and trigger function you own, matching both schema-qualified calls (oracle.instr(...)) and bare calls (instr(...), since orafce's own docs recommend adding its schemas to search_path). Run it, cross-reference anything it flags as unconverted against the compatibility tables, and you'll know exactly where you stand before you commit to anything.
Real orafce's VARCHAR2/NVARCHAR2 are C-only parameterised base types — and it turns out there's no way to reproduce a single parameterised type like that without C: PostgreSQL domains can't carry a typmod, and no custom 'CREATE CAST' can ever target a domain either (we tried, but it failed). So instead of one type, orafce_no_c ships a generated family of fixed-length domains — "oracle.varchar_<N>b" (byte-length), "oracle.varchar_<N>c" (char-length), "oracle.nvarchar_<N>c" — covering every length from 1 to 300 then increments by 10 to 4000, plus a companion Perl tool (audit/rewrite_varchar2_declarations.pl) that mechanically rewrites VARCHAR2(n)/NVARCHAR2(n) in your existing source to the matching domain name. Need something longer than 4000 (Oracle's own PL/SQL variables can go to 32767)? Pass --out-of-range and the tool generates an exact-length domain plus the CREATE DOMAIN statement to run before you install. You can edit the build/generate_varchar2_domains.sh to build the domain/type lengths you need -- you can also use the audit script in detail mode p_summary => false to see all lengths used in your declarations.
One deliberate, documented divergence: these domains error on overflow rather than truncating, in both assignment and explicit-cast contexts. Real orafce's type truncates silently on an explicit cast — PostgreSQL flatly refuses to let a custom cast target a domain, so replicating that exact truncation behavior isn't achievable here at all. Given the choice between "silently truncates your data" and "tells you loudly the moment it doesn't fit," we picked loudly.
Short answer: essentially nothing, measured across five PostgreSQL versions and every included package. The headline results:
dbms_assert: the four core validation functions land within 3–4% of the C original — both are dominated by PostgreSQL's own per-call dispatch overhead, not the actual validation logic. Two thin pass-through wrappers (enquote_literal, enquote_name) are actually faster in PL/pgSQL — not noise, but architecture: the C version calls the target Postgres builtin via a second 'DirectFunctionCall' dispatch layer, while a plain 'LANGUAGE sql' wrapper gets inlined straight into the calling query by the planner. The only measurable gap is on error paths for functions with hand-written character-by-character parsing — up to ~3×, but that's 3× of a few microseconds, immaterial outside an extremely hot, high-volume validation loop.oracle schema (93 objects — scalar wrappers, string functions, date arithmetic, regex, aggregates, to_char/to_number): performance at parity with the C original across the board; the only reproducible gap is PL/pgSQL's exception-handling overhead on error paths (up to ~1.56× on to_number's invalid-input case — a sub-2-3-microsecond absolute difference).plvchr, plvdate, plvsubst: parity with the C original, no measurable cost from anything — including plvdate's stateful business-day calendars, where a session-scoped temp table replaces the C code's process-lifetime static state with no measurable overhead.plvstr: parity with, and in places marginally faster than the C original.Every one of these numbers comes from side-by-side comparison against the real, compiled orafce extension in the same database. We synthesised data as inputs to all of the applicable orafce functions, captured the performance and outputs, then repeated the same in PostgreSQL 14-18 (on Fedora 44) with the same inputs and validated that the same output was achieved (that is how we discovered the issues with orafce) and compared the performance.
orafce_no_c v1.0.0 is the first public release. If you're migrating an Oracle-derived PostgreSQL application off real orafce — or just curious what a pure-SQL version of it looks like — the audit function above is the fastest way to find out exactly where you'd stand. As new versions of PostgreSQL and orafce are released, we will keep testing against the new versions.
A note to orafce -- we have proven that 10 of the 15 packages can be pure SQL -- and that N/VARCHAR2(N) cannot be replicated in SQL. You already have pure SQL functions in orafce, why not start minimising the use of C and start bringing them across to SQL ?
We have a focus on independent and secure PostgreSQL extensions that work on-premises as well as on Cloud managed instances with a focus of usage for users that have come from Oracle. We have released our pg_relay solution that is a durable queue and run solution that we built for our pg_auto_mv solution to automate the update of Materialised Views out of your current process. We are working on an extensible Health Framework for PostgreSQL & we have plans for UTL_MAIL (via our pg_notifier extension that will also cater for Microsoft 365, Teams, Slack, SMS, Jira, ServiceNow, etc...) and maybe UTL_FILE, both in a secure manner. We are also looking at an XML implementation that is traditionally tough to conquer in a secure manner. Keep checking back if those are of interest to you. We believe in PostgreSQL and we believe in portability, one of the greatest features of PostgreSQL is that it is everywhere without penalty, yes there are some variations of how it is implemented, but if you build for PostgreSQL, you can deploy in more places than if you built for another database. We help customers migrate to PostgreSQL from Microsoft SQL or Oracle, and we manage PostgreSQL. As our support for the community and product shows, we are passionate about PostgreSQL and we are innovative with PostgreSQL; run on-premise or run in any Cloud.
