Finding orafce Usage in Your Code¶

Before switching anything, it's worth knowing exactly which orafce functions your own code actually calls — and whether orafce_no_c includes all of them. oracle.orafce_audit() answers that directly, against your own database, rather than making you cross-reference the Compatibility tables by hand.

Installing it¶

oracle.orafce_audit() is not part of orafce_no_c and is never installed by the extension or the standalone script — it's a one-off tool you run by hand, and only against a database that still has the real orafce extension installed (it introspects pg_depend/pg_extension to work out exactly which objects belong to that extension, in that database, at that version — so it stays accurate whichever orafce version you actually have, rather than assuming a hardcoded function list).

psql -d <database> -f audit/oracle_orafce_audit.sql

Requires PostgreSQL 14 or later. It raises a clear error immediately if orafce isn't installed in the target database — there's nothing to audit against otherwise.

Running it¶

-- Summary: every unconverted orafce object found in use, with a count.
SELECT * FROM oracle.orafce_audit();

-- Summary across ALL orafce objects found in use (converted and not),
-- not just the ones missing from orafce_no_c:
SELECT * FROM oracle.orafce_audit(p_missing_only => false);

-- Detail: every individual call site -- schema, function, line number.
SELECT * FROM oracle.orafce_audit(p_summary => false);

-- Restrict the search to specific orafce schemas only:
SELECT * FROM oracle.orafce_audit(p_schemas => ARRAY['oracle', 'plvdate']);

p_schemas filters which orafce schemas to look for usage of — it does not restrict which of your own schemas get scanned. Every non-extension-owned PL/pgSQL function, procedure, and trigger function in the database is always scanned, regardless of which schema it lives in.

Reading the output¶

Summary mode (p_summary => true, the default) gives one row per orafce object actually found in use:

Column	Meaning
`orafce_schema` / `orafce_object` / `orafce_kind`	The real orafce object being called (schema, name, and function/procedure/aggregate/type/view).
`in_orafce_no_c`	Whether orafce_no_c includes it — the question you're actually here to answer.
`occurrence_count`	How many call sites across your code use it.

Detail mode (p_summary => false) instead gives one row per call site:

Column	Meaning
`target_schema` / `target_object` / `target_kind`	The function/procedure in your code that made the call.
`line_number`	The line within that function's stored body (PostgreSQL doesn't retain the original source file, so this is the best available anchor — it's not a file/line in your original `.sql` sources).
`matched_text`	The exact text that matched, for eyeballing false positives (see below).

What actually gets scanned¶

The audit matches both schema-qualified calls (oracle.instr(...)) and bare, unqualified calls (instr(...)), case-insensitively — since orafce's own documentation recommends adding its schemas to search_path, bare calls are common in practice. This trades precision for recall: a bare match against a very ordinary word is inherently noisy. oracle.date's bare form is the single word date — expect a real false-positive rate there specifically, since any PL/pgSQL code declaring a plain date-typed variable will match. That's exactly what the matched_text column in detail mode is for — a quick eyeball, not a guess.

In scope: functions, procedures, and aggregates (called with ordinary call syntax); the handful of orafce type/domain objects (matched as a declared type, e.g. v_x oracle.varchar2; or RETURNS oracle.date); and every orafce-owned view (matched the same way — FROM oracle.dual or bare FROM dual both match).

Out of scope: table and column definitions, and non-PL/pgSQL (plain SQL-language) functions. If your migrated code declares table columns typed VARCHAR2/NVARCHAR2, oracle.orafce_audit() won't see them — VARCHAR2 and NVARCHAR2 gives a separate catalog query specifically for that case.

Next step¶

Whatever the audit turns up as not included, cross-reference it against the Compatibility chapters to understand exactly why — and if any of it is oracle.varchar2/oracle.nvarchar2, the next few chapters cover the dedicated workaround for those two.

Continue to Working with Types.