DATABASE COMPATIBILITY
Compatibility across protocol, language, and execution behavior.
Halo addresses the layers applications encounter: connection protocol, parsing and semantics, optimization, and execution. Oracle, MySQL, and native PostgreSQL modes can coexist while their feature sets and boundaries remain distinct.
The E5 compatibility model
Communication protocol
How applications and tools connect.
Parsing and semantics
How types, functions, syntax, and object behavior are interpreted.
Optimizer
How compatible statements enter planning.
Execution
How the selected behavior is carried out by the Halo kernel.
The E5 overview includes Oracle, MySQL, and PostgreSQL in its four-layer model. PostgreSQL is also documented as the native mode requiring no compatibility extension; this site preserves both statements without treating them as mutually exclusive.
Three modes can coexist without hiding their boundaries
| Mode | Compatibility focus | Scope to validate |
|---|---|---|
| Oracle | Protocol, Oracle-oriented types/functions, characteristic SQL, PL/oraSQL, Package, and dictionary views | Feature-level and client-level behavior |
| MySQL | Protocol, common MySQL 5.6–8.0 features, types, functions, operators, and characteristic DML | Settings that affect application-visible behavior |
| PostgreSQL | Native protocol, types, functions, objects, indexes, partitioning, and administration | Required core families and extensions; no invented target release |
Oracle compatibility, organized by dependency
Named functions and database objects are part of the product surface. They are shown here as capabilities, not as executable examples.
Data types and date behaviorOracle-oriented families
Documented families include NUMBER, BINARY_FLOAT, BINARY_DOUBLE, VARCHAR2, NVARCHAR2, RAW, BLOB, CLOB, BYTEA, LONG, BOOLEAN, and date/time types. Oracle-style DATE behavior is configurable and must be confirmed for the target workload.
- NUMBER
- VARCHAR2
- NVARCHAR2
- RAW
- BLOB
- CLOB
- BOOLEAN
SQL and built-in behaviorQueries and expressions
Coverage includes ROWNUM, MINUS, DECODE, MERGE, CONNECT BY, common table expressions, analytics, sequences, SYSDATE, DUAL, empty-string handling, Oracle outer joins, formatting, and selected output behavior.
- ROWNUM
- MINUS
- DECODE
- MERGE
- CONNECT BY
- SYSDATE
- DUAL
PL/oraSQL and PackageProcedural application logic
Anonymous blocks, procedures, functions, exceptions, dynamic SQL, cursors, and packages containing public interfaces, routines, types, cursors, variables, constants, and exceptions are documented.
- PACKAGE
- PACKAGE BODY
- TYPE BODY
- REF CURSOR
System packagesCompatibility services
DBMS_ALERT supports asynchronous notification; DBMS_PIPE supports session communication; DBMS_ASSERT validates inputs; DBMS_OUTPUT, DBMS_UTILITY, DBMS_RANDOM, and UTL_FILE cover their documented procedural roles. DBMS_RANDOM is not cryptographic, and UTL_FILE is constrained by registered directories and permissions.
- DBMS_ALERT
- DBMS_ASSERT
- DBMS_OUTPUT
- DBMS_PIPE
- DBMS_RANDOM
- DBMS_UTILITY
- UTL_FILE
Metadata and administrationDictionary visibility
Oracle-oriented views cover objects, tables, columns, constraints, indexes, procedures, source, views, segments, and product-version information.
- USER_OBJECTS
- USER_TABLES
- USER_TAB_COLUMNS
- USER_SOURCE
- DBA_SEGMENTS
DBLINK and remote accessOracle and PostgreSQL targets
DBLINK supports documented query and data-change operations against remote Oracle and PostgreSQL systems. It depends on aux_oracle and oracle_fdw in the local database, a schema matching the connection user, and a reachable remote database identity. Remote UPDATE and DELETE also carry the documented primary-key boundary.
Flashback and recycle-bin recoveryHistorical reads and dropped objects
Flashback Query provides historical reads of committed data within the available retention window. It requires commit-timestamp tracking at instance level, restart-level activation, and explicit session enablement. The recycle bin records eligible dropped objects for Flashback to Before Drop recovery and supports an explicit purge lifecycle.
Global temporary tablesSession- or transaction-scoped data
Global temporary tables provide isolated temporary data, indexes, and schema-visible definitions with either session or transaction lifetime. The capability must be enabled at database level and takes effect after a database service restart.
MySQL compatibility, with behavior controls exposed
Types and functions
Numeric, character, ENUM, SET, BLOB, DATE, DATETIME, TIMESTAMP, and four-digit YEAR types are documented, with date, string, numeric, hash, control-flow, and aggregate function families.
- ENUM
- SET
- AUTO_INCREMENT
- LAST_INSERT_ID
- GROUP_CONCAT
Operators and characteristic DML
Comparison, logical, and bitwise operators accompany REPLACE INTO, INSERT IGNORE, duplicate-key update behavior, INSERT SET, LIMIT, prepared statements, and session user variables.
- REPLACE INTO
- INSERT IGNORE
- ON DUPLICATE KEY UPDATE
- LIMIT
Schema and application semantics
MySQL mode documents schema/database equivalence, backtick identifiers, cross-database queries, FORCE INDEX, create-as/select and create-like patterns, multitable joins and updates, aliases, and generated stored columns where applicable.
Client protocol
MySQL mode exposes the documented native protocol path for MySQL-oriented command-line, JDBC, and ODBC clients. Compatibility still belongs at the driver, pool, authentication, and transaction-behavior level rather than being inferred from a successful connection.
Behavior settings
Packet size, timestamp defaults, error rollback, reported server version, identifier case, multitable data changes, AUTO_INCREMENT behavior, and encoding validation can affect what an application observes. The reported version string is configurable and is not the Halo product version.
Known differences
YEAR accepts four-digit values in the documented behavior. TIMESTAMP defaults to NULL and does not implicitly update without explicit behavior. These differences should remain in the migration test plan.
Native PostgreSQL capabilities remain available
Types and functions
Native numeric, character, date/time, Boolean, BYTEA, JSON, XML, aggregate, and window-function families are documented.
- JSON
- XML
- BYTEA
- RANK
- ROW_NUMBER
- LEAD
- LAG
Indexes and partitioning
Six core index methods, multicolumn, unique, expression, partial, and generated-column indexing accompany range, list, hash, multilevel partitioning, pruning, and partition lifecycle operations.
Views and relational objects
Ordinary views compute current results; materialized views persist results and require refresh. Sequences, constraints, roles, permissions, triggers, joins, comments, defaults, and stored generated columns round out the documented object behavior.
Native client path
PostgreSQL mode uses the native protocol for PostgreSQL-oriented clients such as psql and JDBC and does not require the Oracle or MySQL compatibility extensions. The manual does not define a target PostgreSQL release version, so the website names explicit capability families instead of claiming version-wide equivalence.
Boundaries to carry into a PoC
Oracle semantics that need a focused test
Oracle DATE behavior depends on configuration. LISTAGG is documented without WITHIN GROUP; regular expressions use POSIX Advanced Regular Expressions; BITAND works through integer conversion; NVL2 requires compatible result types; and a documented SUBTRACT form is limited.
Remote access and procedural clients
Remote UPDATE and DELETE through Oracle DBLINK require a primary key on the remote table. Some procedural script terminator behavior varies by client, so client tooling belongs in the compatibility test.
Structured error behavior
Native and Oracle endpoints use stable five-character SQLSTATE classes with separate object fields where available. Oracle mode adds private UE and HERR error families. The MySQL protocol endpoint uses MySQL-style numeric errors and SQLSTATE, so error handling is protocol-specific.
Why boundaries are published
They do not negate compatibility. They identify where representative tests provide more useful evidence than a blanket equivalence percentage.
Move from feature lists to workload evidence.
Inventory the protocol, schema objects, SQL semantics, procedural code, metadata views, error handling, and remote dependencies your application uses.