Oracle Compatibility: What to Expect for In-House Applications

HeliosDB Full’s Oracle compatibility module implements a large part of Oracle 23ai’s SQL and PL/SQL feature surface, at varying maturity (see the compatibility matrix). A feature list says little about a given application, though: applications use a small, uneven part of that list, and what matters is whether the statements they send run correctly through the Oracle listener. This page describes what runs today for typical in-house (line-of-business) applications, area by area, explains how that was assessed, and shows how to assess your own application.

Summary

Application profile	What to expect over the Oracle listener today
SQL/ORM-centric (little or no PL/SQL)	The best fit. Table definitions, INSERT/UPDATE/DELETE, joins and basic aggregates run as written; Oracle-specific syntax and functions (DUAL, ROWNUM, NVL, DECODE, FETCH FIRST) need the documented rewrites.
Typical in-house application	Core data access runs unchanged. Plan to rewrite Oracle-specific syntax, most built-in functions, sequences and any PL/SQL before relying on the listener.
PL/SQL-heavy (business logic in packages)	The listener does not execute PL/SQL yet, so the business logic has to move to the application or be converted in a conventional migration.

Assessed in internal tests with python-oracledb 4.0.1 in thin mode against a heliosdb-full development build. “Runs unchanged” means the statement is accepted and returns the same result as Oracle; a statement that reports success but does nothing counts as not working.

Usage that needs rewriting has to be changed before an application can rely on it over the Oracle listener. The usual alternative is a conventional migration: convert the schema and PL/SQL with a PostgreSQL-targeted tool and move the application to a PostgreSQL driver (see the Oracle migration guide).

Results by usage area

Usage area	What runs through the listener today
Connectivity, binds, transactions	Logon, positional (:1) and named (:name) binds, executemany, DATE and BLOB values work. Driver commit()/rollback() calls are not supported yet: every statement commits as it runs.
DDL and data types	Oracle column types in CREATE TABLE (NUMBER(p,s), VARCHAR2, DATE, CLOB, BLOB), constraints, indexes and global temporary tables work. Identity columns need rewriting.
Basic DML (INSERT/UPDATE/DELETE)	Works as written, including INSERT ... SELECT.
Core queries (joins, subqueries, GROUP BY, CTE)	Inner joins, IN (subquery), UNION, simple CASE, NULLS LAST, basic aggregates, GROUP BY and SELECT ... FOR UPDATE work. Derived tables need rewriting; give each computed column its own alias.
Oracle-specific syntax (DUAL, ROWNUM, (+), MINUS, NULL rules)	Mostly needs rewriting; each form has a documented replacement.
Row limiting (FETCH FIRST, OFFSET)	Needs rewriting (LIMIT n).
Built-in functions (NVL, DECODE, TO_CHAR, dates, strings, regex)	TO_CHAR(date), TO_NUMBER, EXTRACT, LENGTH/UPPER/LOWER, COALESCE and || work. NVL, DECODE and most string, numeric and date functions need rewriting.
Sequences, identity, synonyms	Need rewriting: generate keys in the application or over the PostgreSQL listener.
Analytic functions and advanced aggregates	Need rewriting.
Advanced SQL (MERGE, INSERT ALL, CONNECT BY, PIVOT, GTT, dictionary)	Global temporary tables work. MERGE, INSERT ALL, CONNECT BY, PIVOT, RETURNING INTO and the data dictionary views need rewriting.
PL/SQL (blocks, procedures, functions, triggers, packages)	Not executed through the listener yet; PL/SQL execution through the listener is on the roadmap. Move the logic to the application, or port it to PL/pgSQL functions run through the PostgreSQL interface.

Several of these report success without the expected effect (for example MERGE, PL/SQL blocks and statements against a missing table), so test by reading results back. The Oracle Quick Start lists a replacement for each form.

How this was assessed

1. Feature probes. 115 Oracle-dialect features in 11 areas were sent through the Oracle listener. Each probe checks the returned values, and statements that change data are verified by reading the data back. Functions are applied to table columns, as applications do, and DUAL is assessed separately. A correct value returned with a non-Oracle type (a NUMBER as text) is recorded as partly working.
2. Usage profiles. The areas were then read against how three kinds of application use the database: SQL/ORM-centric, typical in-house and PL/SQL-heavy. The profiles are a model informed by the published sources below, not a measurement of any one application.

Why usage matters more than feature counts:

• Plain SQL is plentiful in application code. A study of 357 industrial projects extracted 74,416 INSERT, 51,652 SELECT, 10,454 UPDATE, 18,907 CREATE TABLE and 7,152 CREATE INDEX statements embedded in application code (Sharma et al., 2018). The study searched only for these five statement types, so it shows that ordinary DDL and DML are common, not how they compare with PL/SQL or other statements.
• Procedural code is where migrations get hard. In an AWS Schema Conversion Tool walkthrough on a sample schema, the tool converted all database storage objects but only some of the code objects (one worked example, not a general rate); Google reports an Oracle estate of 14 databases, most of which could be converted automatically. How much PL/SQL an application uses is therefore what separates the profiles.
• Oracle-specific constructs are frequent but individually small. Ora2Pg’s migration-cost model scores ROWNUM at 1, TO_CHAR at 0.1, outer-join (+) at 2, CONNECT BY, MERGE and DBMS_ calls at 3 and BULK COLLECT at 5. These are effort weights per occurrence, not frequencies: Ora2Pg rates hierarchical, bulk and package features as costlier to convert than individual Oracle functions and syntax.
• Feature-count scores depend on what is counted. EDB’s migration portal scores compatible DDL against total DDL, and EDB notes that a schema with a higher score is not necessarily easier to migrate than one with a lower score. This page therefore looks at usage rather than counting features.

Caveats

• Your application is not the average. Applications that keep business logic in PL/SQL packages, use DBMS_ packages, database links, Advanced Queuing, Spatial or Oracle Forms/APEX belong with the PL/SQL-heavy profile; ORM-generated workloads are closest to the SQL/ORM-centric profile.
• The profiles are a model. They are set from the sources above, not measured on customer applications. Read the area results against your application’s actual mix.
• The results describe one build. They will change as the Oracle listener improves; check the release notes of your version.

Assess your own application

1. Capture the SQL the application sends: driver or JDBC logs, V$SQL/AWR, or the application’s repository.
2. Classify the statements by the usage areas above and count how often each runs.
3. Run them against a HeliosDB Full test server through the Oracle listener, and check results, not only success.
4. Rank what needs rewriting by how often it runs: a frequent statement matters more than a rare one, and PL/SQL usually decides the effort.
5. For a planned migration, contact sales@heliosdb.com for an assessment.

Sources

• T. Sharma, M. Fragkoulis, S. Rizou, M. Bruntink, D. Spinellis, “Smelly Relations: Measuring and Understanding Database Schema Quality”, ICSE-SEIP 2018, Table 1. Preprint
• AWS Database Blog, “Migrate your procedural SQL code with the AWS Schema Conversion Tool”. Link
• Google Cloud Blog, “Introducing the open-source Database Migration Assessment tool” (2023-07-13). Link
• EDB, “How EDB Migration Portal Calculates Compatibility Percentage”. Link
• Ora2Pg, migration cost assessment (%UNCOVERED_SCORE in lib/Ora2Pg/PLSQL.pm). Repository