Skip to content

Oracle Compatibility Gap Analysis #4

Open
Open
Oracle Compatibility Gap Analysis#4
@rophy

Description

@rophy
rophy
opened on Nov 25, 2025

Overview

Comprehensive Oracle PL/SQL compatibility analysis based on direct feature testing.

Test Images:

  • Upstream: ivorysql/ivorysql:5.0-bookworm
  • Fork: rophy/ivorysql:20251204-ab4128a

Compatibility Matrix

Syntax Description PostgreSQL IvorySQL 5.0 rophy/IvorySQL Upstream PR Status
FOR var IN (SELECT ...) Implicit RECORD declaration for FOR loop cursor variables ❌ ❌ ✅ #983 Open
EXCEPTION type User-defined exception types with RAISE/WHEN support ❌ ❌ ✅ #972 Merged
ROWNUM Oracle row numbering pseudocolumn ❌ ❌ ✅ #1000 Open
ROWID (automatic) Physical row address on all tables ❌ ❌ ❌ N/A
ROWID (opt-in) Physical row address with explicit option ❌ ⚠️ ⚠️ Implemented
DBMS_OUTPUT.PUT_LINE() Output text with newline to buffer ❌ ❌ ✅ #998 Open
DBMS_OUTPUT.PUT() Output text without newline to buffer ❌ ❌ ⚠️ #998 Open
DBMS_OUTPUT.ENABLE() Enable output buffer ❌ ❌ ⚠️ #998 Open
DBMS_OUTPUT.NEW_LINE() Output newline character ❌ ❌ ⚠️ #998 Open
DBMS_OUTPUT.DISABLE() Disable output buffer ❌ ❌ ⚠️ #998 Open
DBMS_UTILITY.FORMAT_CALL_STACK() Returns current call stack ❌ ❌ ✅ #1001 Open
DBMS_UTILITY.FORMAT_ERROR_STACK() Returns error message ❌ ❌ ✅ #1001 Open
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() Returns call stack where exception was raised ❌ ❌ ✅ #1001 Open
PACKAGE / PACKAGE BODY Package specification and body structure ❌ ✅ ✅ Implemented
PRAGMA AUTONOMOUS_TRANSACTION Independent transactions that commit separately ❌ ❌ ✅ #986 Open
SYS_CONTEXT('USERENV', 'CURRENT_USER') Current database username ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'SESSION_USER') Session username ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'IP_ADDRESS') Client IP address ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'SID') Session identifier ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'SESSIONID') Session sequence number ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'DB_NAME') Database name ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') Current schema name ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'LANG') Language setting ❌ ✅ ✅ Implemented
SYS_CONTEXT('USERENV', 'ISDBA') User is superuser ❌ ✅ ✅ Implemented
SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') User has DBA role ❌ ✅ ✅ Implemented
SYS_CONTEXT('SYS_SESSION_ROLES', 'LOGIN') User has login privilege ❌ ✅ ✅ Implemented
SYS_CONTEXT('SYS_SESSION_ROLES', 'CREATEROLE') User can create roles ❌ ✅ ✅ Implemented
SYS_CONTEXT('SYS_SESSION_ROLES', 'CREATEDB') User can create databases ❌ ✅ ✅ Implemented
DECODE(expr, search, result, ...) Inline conditional logic ❌ ✅ ✅ Implemented
ADD_MONTHS(date, n) Add months to date ❌ ✅ ✅ Implemented
LAST_DAY(date) Last day of month ❌ ✅ ✅ Implemented
NEXT_DAY(date, weekday) Next specified weekday ❌ ✅ ✅ Implemented
%TYPE Variable type inheritance from table columns ✅ ✅ ✅ Implemented
VARCHAR2 type Oracle variable-length string type ❌ ✅ ✅ Implemented
NUMBER type Oracle numeric type ❌ ✅ ✅ Implemented
DATE type Oracle date/time type ⚠️ ✅ ✅ Implemented
v$mystat / v$statname Oracle session statistics views ❌ ❌ #1003 ❌ Not started
IDENTIFIER# Oracle # character in unquoted identifiers ❌ ❌ #1002 ❌ Not started
CONNECT BY Hierarchical queries with LEVEL pseudocolumn ❌ ❌ ❌ Not started

Legend:

  • ✅ = Fully supported
  • ⚠️ = Partial support or different semantics
  • ❌ = Not supported

Summary

  • PostgreSQL Features: 1 fully supported, 2 partial (%TYPE, DATE, ctid for ROWID)
  • IvorySQL 5.0 Upstream: 22 Oracle-compatible features
    • Core PL/SQL: PACKAGE structure, %TYPE
    • ROWID: opt-in only with WITH (rowid=on)
    • SYS_CONTEXT: 13 parameters (USERENV + SYS_SESSION_ROLES namespaces)
    • Functions: DECODE, ADD_MONTHS, LAST_DAY, NEXT_DAY
    • Types: VARCHAR2, NUMBER, DATE
    • NOT included: ROWNUM, implicit FOR loop, DBMS_OUTPUT, DBMS_UTILITY, PRAGMA AUTONOMOUS_TRANSACTION
  • rophy/IvorySQL Fork: 31 fully supported + 5 partial
    • Everything in upstream, plus:
    • Core PL/SQL: Implicit FOR loop cursors, ROWNUM pseudocolumn
    • EXCEPTION types (user-defined exceptions with RAISE/WHEN) - merged to upstream
    • DBMS_OUTPUT: PUT_LINE (full), PUT/ENABLE/DISABLE/NEW_LINE (partial)
    • DBMS_UTILITY: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, FORMAT_ERROR_BACKTRACE
    • PRAGMA AUTONOMOUS_TRANSACTION (via dblink)
  • Not Implemented Anywhere: 4 features
    • CONNECT BY - hierarchical queries
    • ROWID (automatic) - not available without opt-in
    • v$mystat/v$statname - session statistics views (#1003)
    • # in identifiers - column names like STATISTIC# (#1002)
  • Overall Compatibility:
    • PostgreSQL: ~8% (3/38)
    • IvorySQL 5.0: ~58% (22/38)
    • rophy/IvorySQL: ~92% (31/38 full + 5/38 partial = 34/38 usable)

Pending Upstream PRs

The following features from rophy/IvorySQL have open PRs pending review:

Feature PR Status
Implicit FOR loop cursors #983 Open
ROWNUM pseudocolumn #1000 Open
DBMS_OUTPUT package #998 Open
DBMS_UTILITY package #1001 Open
PRAGMA AUTONOMOUS_TRANSACTION #986 Open
User-defined EXCEPTION #972 Merged (2025-12-01)

Upstream vs Fork Comparison

Features added in rophy/IvorySQL fork (not in upstream ivorysql/ivorysql:5.0-bookworm):

Feature Upstream Error Fork Status
ROWNUM "rownum": invalid identifier ✅ Works
FOR cr IN (SELECT...) loop variable must be a record variable ✅ Works
DBMS_OUTPUT.PUT_LINE() schema "dbms_output" does not exist ✅ Works
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() schema "dbms_utility" does not exist ✅ Works
PRAGMA AUTONOMOUS_TRANSACTION type "autonomous_transaction" does not exist ✅ Works

ROWID Implementation Details

Both IvorySQL 5.0 and rophy/IvorySQL implement ROWID via PR #749, but it's opt-in per table:

-- ROWID is NOT automatic - must specify WITH (rowid=on)
CREATE TABLE my_table (id INT, name VARCHAR2(50)) WITH (rowid=on);

INSERT INTO my_table VALUES (1, 'Alice');
SELECT ROWID, id, name FROM my_table;
--    rowid   | id | name
-- -----------+----+-------
--  (16385,1) |  1 | Alice

Comparison with Oracle:

Behavior Oracle IvorySQL
Table creation CREATE TABLE t (...) CREATE TABLE t (...) WITH (rowid=on)
ROWID available ✅ Automatic ❌ Requires opt-in
ROWID format AAASwtAAAAAAclRAAA (Base64) (16385,1) (oid, seq)

Implementation Priority Recommendations

  1. Low effort: # in identifiers (scanner modification) - #1002
  2. Low effort: Default ROWID on all tables (add GUC default_with_rowid)
  3. Medium effort: v$mystat/v$statname views - #1003
  4. Complex but valuable: CONNECT BY (consider restoring v2.x implementation)

Test Environment

  • Upstream Image: ivorysql/ivorysql:5.0-bookworm
  • Fork Image: rophy/ivorysql:20251204-ab4128a
  • IvorySQL Version: 5.0
  • PostgreSQL Base: 17/18
  • Oracle mode (port 1521)
  • Extensions: ivorysql_ora 1.0, plisql 1.0

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions