orafce

Overview

Upstream distribution 4.16.13 fixes possible dbms_sql crashes; SQL/control version is 4.16. Local PGDG package matrix has RPM 4.16.12 and DEB 4.16.13 on 2026-10-10.

Version

Install

You can install orafce directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install orafce;          # Install for current active PG version

pig ext install -y orafce -v 18  # PG 18
pig ext install -y orafce -v 17  # PG 17
pig ext install -y orafce -v 16  # PG 16
pig ext install -y orafce -v 15  # PG 15
pig ext install -y orafce -v 14  # PG 14

dnf install -y orafce_18       # PG 18
dnf install -y orafce_17       # PG 17
dnf install -y orafce_16       # PG 16
dnf install -y orafce_15       # PG 15
dnf install -y orafce_14       # PG 14

apt install -y postgresql-18-orafce   # PG 18
apt install -y postgresql-17-orafce   # PG 17
apt install -y postgresql-16-orafce   # PG 16
apt install -y postgresql-15-orafce   # PG 15
apt install -y postgresql-14-orafce   # PG 14

Create Extension:

CREATE EXTENSION orafce;

Usage

Sources:

• 4.16.13 release
• README.asciidoc
• orafce.control
• orafce–4.16.sql
• 4.16.12 release notes
• File-access implementation

orafce provides Oracle-compatible functions, types and utility packages. Distribution 4.16.13 still uses control and SQL extension version 4.16; the two numbers describe different layers.

Core Workflow

CREATE EXTENSION orafce;
SELECT oracle.add_months(date '2026-01-31', 1);
SELECT oracle.nvl(NULL::text, 'fallback');
SELECT oracle.decode(1, 1, 'one', 2, 'two', 'other');
SELECT dbms_output.enable();
SELECT dbms_output.put_line('Hello');
SELECT * FROM dbms_output.get_line();

Types and Packages

Use oracle.date when an Oracle-style date must retain the time of day. Date functions include oracle.add_months, oracle.last_day, oracle.next_day, oracle.months_between, rounding and truncation. Qualify oracle.decode, oracle.greatest and oracle.least explicitly because PostgreSQL parser handling can otherwise select built-in semantics.

dbms_output manages buffered output; dbms_pipe and dbms_alert support session communication. dbms_sql exposes dynamic cursors and typed column retrieval. dbms_utility, dbms_assert, plvstr, plvchr and plvsubst supply diagnostic, validation and string helpers. These compatibility functions do not turn PostgreSQL into Oracle or provide an Oracle procedural-language runtime.

File Access and 4.16.12 Changes

utl_file accesses server-side files within administrator-configured allowed directories; restrict grants and file-system permissions. The implementation rejects parent-directory references that remain after path canonicalization. Avoid parent references in file paths. The 4.16.12 release specifically fixes possible crashes in dbms_sql.

Installation requires a superuser and creates fixed schemas; no preload is required. Follow the upstream configuration guidance before altering search_path. Since the SQL version remains 4.16, an installed 4.16 extension need not acquire a new SQL version merely because its binary distribution was patched. Reconnect as required to use the updated library and verify behavior against the exact installed distribution.

4.16.13 Patch

Distribution 4.16.13 fixes additional possible dbms_sql crashes. The control version remains 4.16; update the library distribution without inventing a 4.16.13 SQL upgrade.