---
title: "Oracle Compatibility: What to Expect for In-House Applications"
description: "What in-house Oracle applications can run unchanged over the HeliosDB Full Oracle listener, by usage area, and how to assess your own application."
---

# 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](/docs/full/reference/protocols/oracle/compatibility/)). 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](/docs/full/migration/oracle-to-heliosdb/)).

## 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](/docs/full/quick-starts/protocols/oracle_quick_start/#what-runs-over-the-oracle-listener)
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](mailto: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](https://www.tusharma.in/preprints/dbSchemaQuality_Preprint_ICSE2018.pdf)
- AWS Database Blog, "Migrate your procedural SQL code with the AWS Schema Conversion
  Tool". [Link](https://aws.amazon.com/blogs/database/migrate-your-procedural-sql-code-with-the-aws-schema-conversion-tool/)
- Google Cloud Blog, "Introducing the open-source Database Migration Assessment tool"
  (2023-07-13). [Link](https://cloud.google.com/blog/products/databases/introducing-open-source-database-migration-assessment-tool/)
- EDB, "How EDB Migration Portal Calculates Compatibility Percentage".
  [Link](https://www.enterprisedb.com/blog/how-edb-migration-portal-calculates-compatibility-percentage)
- Ora2Pg, migration cost assessment (`%UNCOVERED_SCORE` in `lib/Ora2Pg/PLSQL.pm`).
  [Repository](https://github.com/darold/ora2pg)