Oracle Compatibility Gap Analysis

[rophy]

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

[rophy]

Update: SYS_CONTEXT is Already Implemented! ✅

After testing directly in IvorySQL, I discovered that SYS_CONTEXT is fully implemented with comprehensive support.

Test Results

Tested 18 parameters across two namespaces:

USERENV Namespace (Working)

• ✅ CURRENT_USER → Returns current database username
• ✅ SESSION_USER → Returns session username
• ✅ IP_ADDRESS → Returns client IP (e.g., ::1/128)
• ✅ SID → Returns session identifier (process ID)
• ✅ SESSIONID → Returns session sequence number
• ✅ DB_NAME → Returns database name
• ✅ CURRENT_SCHEMA → Returns current schema
• ✅ LANG → Returns language setting (e.g., en_US)
• ✅ ISDBA → Returns TRUE if user is superuser
• ⚠️ HOST, SERVER_HOST, CLIENT_INFO, OS_USER, TERMINAL, MODULE, ACTION, CLIENT_IDENTIFIER → Return empty (supported but not set in test environment)

SYS_SESSION_ROLES Namespace (Working)

• ✅ DBA → TRUE (user has superuser role)
• ✅ LOGIN → TRUE (user has login privilege)
• ✅ CREATEROLE → TRUE (user can create roles)
• ✅ CREATEDB → TRUE (user can create databases)

Updated Compatibility Score

Previous assessment: ~55% compatibility
Corrected assessment: ~65% compatibility

With SYS_CONTEXT implemented, IvorySQL has 13+ working Oracle session context parameters that neither PostgreSQL+Orafce nor MariaDB support.

Revised Feature Summary

Implemented (22 features):

• ✅ PACKAGE/PACKAGE BODY
• ✅ Implicit FOR loop cursors
• ✅ User-defined EXCEPTION types
• ✅ DBMS_OUTPUT (PUT_LINE, PUT, ENABLE)
• ✅ SYS_CONTEXT (13+ parameters) ← Newly verified
• ✅ Basic types (VARCHAR2, NUMBER, DATE)

Still Missing (6 features):

• ❌ CONNECT BY
• ❌ PRAGMA AUTONOMOUS_TRANSACTION
• ❌ ROWID
• ❌ DECODE
• ❌ DBMS_OUTPUT.NEW_LINE/DISABLE
• ❌ DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Apology for Initial Error

I incorrectly reported SYS_CONTEXT as missing because:

1. Package testing failed on other errors before I could verify SYS_CONTEXT
2. I didn't test it in isolation with direct SQL queries
3. I made assumptions based on package-level failures

Thank you to the IvorySQL team for the correction in discussion #976!

Documentation Reference

Full SYS_CONTEXT documentation: https://docs.ivorysql.org/en/ivorysql-doc/master/6.4.1