Home // Cloud Infrastructure // Odoo Development // AI & Automation // Odoo + AI Agents Case Studies About Blog Free Assessment
// CLOUD MIGRATION · 11 MIN READ

Babelfish for Aurora Is Not an Oracle Migration Target

Every few months a team shows up with the idea of using Babelfish to migrate off Oracle. The confusion is understandable — both involve moving away from a commercial database to Aurora PostgreSQL. But Babelfish translates T-SQL, not PL/SQL. It solves a completely different problem.

// PUBLISHED 2023-11-14 · LANIAKEA TEAM

What Babelfish Actually Does

Babelfish for Aurora PostgreSQL is a compatibility layer that allows applications written for Microsoft SQL Server to run against Aurora PostgreSQL with minimal code changes. It implements the TDS wire protocol (the network protocol SQL Server uses) and translates T-SQL statements into PostgreSQL-compatible operations at the protocol level.

The value proposition is for SQL Server migrations: an application that connects to SQL Server via JDBC or ODBC can point at a Babelfish-enabled Aurora cluster and, for the subset of T-SQL that Babelfish supports, run without modification. This can meaningfully reduce the rewriting effort for SQL Server migrations where the application code is the bottleneck.

What Babelfish does not do:

Babelfish is T-SQL-to-PostgreSQL translation. Oracle uses PL/SQL, not T-SQL. These are entirely separate procedural SQL dialects with different syntax, semantics, and runtime behavior. Babelfish's parser does not recognize PL/SQL keywords.

Why the Confusion Exists

The confusion stems from the fact that the end destination is the same: Aurora PostgreSQL. Teams hear "Babelfish helps you migrate to Aurora without rewriting code" and reason that if they are also migrating to Aurora, maybe Babelfish is relevant.

It is not. The compatibility problem Babelfish solves — enabling SQL Server applications to run on Aurora — is categorically different from the Oracle-to-PostgreSQL migration problem. The Oracle migration problem requires:

None of these have any relationship to T-SQL or the TDS protocol. Babelfish is simply the wrong tool for the job.

The Actual Oracle Migration Toolchain

For Oracle-to-Aurora PostgreSQL migrations, the relevant tools are:

ora2pg

The most widely used open-source Oracle-to-PostgreSQL migration tool. ora2pg introspects an Oracle schema via JDBC, extracts DDL and stored procedures, and generates PostgreSQL-compatible equivalents. Its conversion quality is reasonable for straightforward schemas and degrades significantly for complex PL/SQL, Oracle-specific syntax, and implicit type conversions.

# Basic ora2pg schema export
ora2pg -t TABLE -o tables.sql -d your_db \
  -s your_schema \
  -u your_user \
  -w your_password

# Export stored procedures and functions
ora2pg -t PROCEDURE -o procedures.sql -d your_db \
  -s your_schema

# Run migration assessment to get conversion complexity score
ora2pg --estimate_cost -d your_db -s your_schema

The --estimate_cost output gives you a migration unit (MU) score per object. Objects scoring above 5 MU require manual review. In practice, most PL/SQL packages with exception handling, cursor management, and Oracle-specific built-ins score 8-20 MU and require substantial manual rewriting.

AWS Schema Conversion Tool (SCT)

AWS SCT is the commercial alternative for Oracle-to-RDS/Aurora migrations. It handles schema conversion, stored procedure assessment, and generates an action item report showing what it can convert automatically and what requires manual work. SCT generally handles simple Oracle SQL constructs well and struggles with the same edge cases as ora2pg: complex PL/SQL bodies, Oracle package state, UTL_FILE, DBMS_SCHEDULER, and dynamic SQL.

# SCT is a GUI tool, but its assessment can be run via CLI
# for CI/CD integration
java -jar aws-schema-conversion-tool.jar \
  --config-file sct-project.sct \
  --output-folder ./sct-output \
  --assessment-report-file assessment.pdf

AWS DMS for Data Migration

Once the schema is converted, AWS DMS handles the initial data load and CDC (change data capture) for cutover. DMS has well-documented limitations with Oracle LOB columns, compressed tables, and certain data type mappings — but these are separate from the schema conversion problem.

The PL/SQL Problem: What Actually Takes Time

The schema conversion tooling handles DDL (CREATE TABLE, indexes, constraints) reasonably well. The hard part is always the procedural code. Oracle PL/SQL has constructs with no direct PostgreSQL equivalent:

Oracle Packages

Oracle packages bundle related procedures, functions, types, and package-level variables into a single compilable unit. Package-level variables persist for the lifetime of a session — a pattern that has no direct equivalent in PostgreSQL. PL/pgSQL has no package construct.

-- Oracle: package with session-level state
CREATE OR REPLACE PACKAGE session_context AS
  g_user_id  NUMBER;
  g_org_id   NUMBER;
  PROCEDURE set_context(p_user_id NUMBER, p_org_id NUMBER);
  FUNCTION  get_user_id RETURN NUMBER;
END session_context;

-- PostgreSQL equivalent options:
-- 1. Use a schema as a namespace (no session state)
-- 2. Use SET/current_setting() for session parameters
-- 3. Use a temporary table for session state
-- 4. Move session context to the application layer

-- Option 2: session parameter approach
SET myapp.user_id = '12345';
SELECT current_setting('myapp.user_id')::integer;

Autonomous Transactions

Oracle's PRAGMA AUTONOMOUS_TRANSACTION allows a procedure to commit independently of its calling transaction. This is commonly used for audit logging — you want to record that an operation was attempted even if the operation itself rolls back. PostgreSQL has no native autonomous transaction support.

-- Oracle: autonomous transaction for audit logging
CREATE OR REPLACE PROCEDURE log_audit_event(
  p_event_type VARCHAR2,
  p_details    VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log(event_type, details, event_time)
  VALUES (p_event_type, p_details, SYSDATE);
  COMMIT;  -- commits independently of caller's transaction
END;

-- PostgreSQL approach: use a separate dblink connection
-- or move audit writes to the application layer
-- or use a background worker queue
CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE FUNCTION log_audit_event(
  p_event_type text,
  p_details    text
) RETURNS void AS $$
BEGIN
  PERFORM dblink_exec(
    'dbname=' || current_database(),
    format(
      'INSERT INTO audit_log(event_type, details, event_time) VALUES (%L, %L, now())',
      p_event_type, p_details
    )
  );
END;
$$ LANGUAGE plpgsql;

CONNECT BY Hierarchical Queries

Oracle's CONNECT BY syntax for hierarchical/tree queries is one of the most commonly-used Oracle-specific SQL extensions. PostgreSQL uses recursive CTEs instead. The logic is equivalent; the syntax is completely different.

-- Oracle: CONNECT BY for org hierarchy
SELECT employee_id, manager_id, name,
       LEVEL,
       SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- PostgreSQL: recursive CTE equivalent
WITH RECURSIVE org_tree AS (
  -- anchor: root nodes
  SELECT employee_id, manager_id, name,
         1 AS level,
         '/' || name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive: children
  SELECT e.employee_id, e.manager_id, e.name,
         ot.level + 1,
         ot.path || '/' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree;

MERGE Statement Differences

Oracle's MERGE syntax differs from PostgreSQL's INSERT ... ON CONFLICT. For complex MERGE statements with multiple WHEN clauses and conditional updates, the rewrite is straightforward but not automatic:

-- Oracle MERGE
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.value = s.value, t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (id, value, created_at) VALUES (s.id, s.value, SYSDATE);

-- PostgreSQL equivalent
INSERT INTO target_table (id, value, created_at)
SELECT id, value, now() FROM source_table
ON CONFLICT (id) DO UPDATE
  SET value = EXCLUDED.value,
      updated_at = now();

What a Realistic Oracle Migration Actually Looks Like

On a mid-size Oracle database with 200-500 stored procedures and packages, a realistic migration timeline breaks down roughly as follows:

Babelfish is irrelevant to every step of this process. The migration path is ora2pg or SCT for assessment, manual PL/SQL rewriting, application SQL updates, and DMS for data movement.

The Right Use Case for Babelfish

To be clear: Babelfish is a legitimate and useful product for its intended purpose. If you have SQL Server applications — particularly older ones with heavy T-SQL stored procedure logic — and you want to migrate to Aurora PostgreSQL without rewriting all the T-SQL first, Babelfish can accelerate that effort significantly for the supported T-SQL subset.

SQL Server migration with Babelfish and Oracle migration to Aurora PostgreSQL are two distinct migration paths that happen to share a destination. They share no tooling, no methodology, and no applicability to each other's problems.

Planning an Oracle-to-Aurora migration?

We scope Oracle migrations honestly — what converts automatically, what requires manual rewriting, and what the real timeline looks like. Free assessment, no obligation.

Get a Free Assessment → More Articles