Trekport

April 15, 2026 · Trekport Engineering

The BULK COLLECT problem: how AWS SCT silently breaks Oracle migrations

AWS Schema Conversion Tool wraps untranslated BULK COLLECT and FORALL constructs in PL/pgSQL shells that compile but do nothing. By AWS's own admission. Here is how to detect it, why it happens, and what to do instead.

The BULK COLLECT problem: how AWS SCT silently breaks Oracle migrations

This post is about a specific failure mode that costs Oracle to PostgreSQL migration projects months. It is not a vendor pile-on. It is a documented, AWS-acknowledged behavior of AWS Schema Conversion Tool that buyers should understand before they commit to a migration plan.

If you are evaluating Oracle migration tools, read this in full. If you are mid-migration with AWS SCT, search your converted PL/pgSQL output for the patterns below and verify nothing important is silently broken.

The setup

AWS SCT publicly claims it converts “up to 90 percent of complex database objects.” That is a real and useful number for most constructs. The question is what happens to the remaining 10 percent.

For most untranslatable Oracle constructs, AWS SCT leaves an action item in its conversion report. You see it, you handle it, you move on. The behavior is honest.

For one specific class of constructs (BULK COLLECT and FORALL), the tool does something different. It wraps the untranslated logic in PL/pgSQL that compiles cleanly. Your function deploys. Your tests that check “does the function exist” pass. Your tests that check “does the function compile” pass. Your tests that exercise the actual logic against real data fail in production.

This is documented by AWS themselves on the AWS Database Blog. Quoted text below.

The exact behavior

From the AWS Database Blog post on migrating Oracle bulk binds to Aurora PostgreSQL:

AWS SCT generates the necessary objects in the target database. However, it can't convert some Oracle features. For example, AWS SCT doesn't convert the BULK COLLECT clause or FORALL statement. When AWS SCT encounters these constructs, it generates the equivalent PostgreSQL function but doesn't convert the logic inside.

In practice the output looks like this. Take a typical Oracle procedure that uses bulk binds:

-- Original Oracle PL/SQL procedure (works correctly)
CREATE OR REPLACE PROCEDURE update_employee_status(
  p_company_id NUMBER
) IS
  TYPE id_list IS TABLE OF NUMBER;
  TYPE name_list IS TABLE OF VARCHAR2(100);
  emp_ids id_list;
  emp_names name_list;
BEGIN
  SELECT id, name
    BULK COLLECT INTO emp_ids, emp_names
    FROM employees
   WHERE company_id = p_company_id
     AND status = 'PENDING';

  FORALL i IN 1..emp_ids.COUNT
    UPDATE employees
       SET status = 'ACTIVE',
           activated_at = SYSDATE
     WHERE id = emp_ids(i);

  COMMIT;
END;

After AWS SCT conversion, the PostgreSQL function compiles. It deploys into your target Aurora PostgreSQL or RDS for PostgreSQL database without errors. But the body looks like this:

-- AWS SCT output (compiles, deploys, does nothing useful)
CREATE OR REPLACE FUNCTION update_employee_status(
  p_company_id INTEGER
) RETURNS VOID AS $$
DECLARE
  -- Note: AWS SCT could not convert BULK COLLECT.
  -- Manual conversion required.
BEGIN
  -- Note: AWS SCT could not convert FORALL.
  -- Manual conversion required.
  NULL;
END;
$$ LANGUAGE plpgsql;

The SQL is valid. The function exists in the catalog. Calling it returns without error. It performs no UPDATE. The employee statuses are never changed.

This is the silent-failure pattern. Compile success is not behavioral correctness. The standard test pyramid (unit tests on the function exists, integration tests on the function returns, end-to-end tests on the application behavior) catches the issue only at the end-to-end layer, by which point engineers have spent weeks debugging whyemployee statuses are not updating after the migration.

Why this matters

Bulk binds are not a niche Oracle feature. They are the standard PL/SQL idiom for any operation that needs to process more than a few rows efficiently. In typical enterprise estates, BULK COLLECT and FORALL appear in:

  • Batch ETL jobs (most uses)
  • Audit log processing
  • Data archival routines
  • Bulk customer updates (subscription renewals, status flips)
  • Periodic reconciliation procedures
  • Any nightly job that touches more than a few hundred rows

A 30,000-object Oracle estate typically has 200 to 2,000 procedures that use bulk binds. Every one of them is a candidate for silent failure if converted with AWS SCT and not manually reviewed.

The reviews are the manual labor that AWS SCT's “up to 90 percent” figure does not count. They are also the work that finishes a migration. Skipping them means you have a database that deploys cleanly and behaves wrong.

How to detect it after the fact

If you are mid-migration with AWS SCT and want to audit your converted PL/pgSQL for this pattern, search for these markers in your output:

# In your converted PL/pgSQL files
grep -lE "Could not convert (BULK COLLECT|FORALL|bulk collect|forall)" \
  ./converted/*.sql
grep -lE "Manual conversion required" ./converted/*.sql

Every matching file is a candidate. Open each, find the original Oracle procedure, decide what the correct PostgreSQL translation should be, and rewrite by hand.

For a typical estate, this is hundreds of procedures, each requiring 15 to 60 minutes of work. Budget a person-month per 200 affected procedures.

What the correct translation looks like

PostgreSQL does not have a direct BULK COLLECT or FORALL syntax. The correct translation is set-based and almost always faster than the original Oracle.

-- Correct PostgreSQL translation (set-based, faster than original)
CREATE OR REPLACE FUNCTION update_employee_status(
  p_company_id INTEGER
) RETURNS VOID AS $$
BEGIN
  UPDATE employees
     SET status = 'ACTIVE',
         activated_at = NOW()
   WHERE company_id = p_company_id
     AND status = 'PENDING';
END;
$$ LANGUAGE plpgsql;

In this case the BULK COLLECT pattern collapses entirely: the SELECT and UPDATE can be fused into a single set-based statement. Performance is 5x to 20x better than the Oracle original because there is no per-row context switch between the SQL engine and the PL/SQL engine.

Some patterns are harder. If the bulk-bound array is used between the SELECT and the UPDATE for non-trivial logic (filtering, joining, external calls), the translation needs an array-bound intermediate:

-- For cases where you actually need the intermediate array
CREATE OR REPLACE FUNCTION process_pending(
  p_company_id INTEGER
) RETURNS VOID AS $$
DECLARE
  emp_ids INTEGER[];
BEGIN
  SELECT ARRAY_AGG(id) INTO emp_ids
    FROM employees
   WHERE company_id = p_company_id AND status = 'PENDING';

  -- Do non-trivial work with emp_ids here

  UPDATE employees
     SET status = 'ACTIVE',
         activated_at = NOW()
   WHERE id = ANY(emp_ids);
END;
$$ LANGUAGE plpgsql;

The point is that a correct translation exists for every bulk-bind pattern. The work is not technically hard. The work is identifying every case (which the AWS SCT report does not surface as a hard failure) and writing the correct translation by hand.

How Trekport handles this

This is the kind of construct Trekport was built to handle. The conversion engine parses the original Oracle PL/SQL into an abstract syntax tree, recognizes the BULK COLLECT and FORALL patterns explicitly, and emits the correct PostgreSQL set-based translation in the converter output. The same procedure above, run through Trekport:

-- Trekport output (set-based, deploys, runs correctly)
CREATE OR REPLACE FUNCTION update_employee_status(
  p_company_id INTEGER
) RETURNS VOID AS $$
BEGIN
  UPDATE employees
     SET status = 'ACTIVE',
         activated_at = NOW()
   WHERE company_id = p_company_id
     AND status = 'PENDING';
END;
$$ LANGUAGE plpgsql;

No manual review. No silent failure. The conversion report tells you the procedure was translated, what pattern it matched, and what the new PostgreSQL semantics are. If you want to audit the choice, the AST and the transformation log are inspectable.

This is what we mean by “100 percent deployment mandate.” Every object is fully equivalent PostgreSQL or backed by a verified extension. Nothing ships as a compiling-but-empty shell.

What to do if you are mid-migration

If you have already started a migration with AWS SCT and are reading this in panic, three concrete steps:

  1. Audit the converted output with the grep pattern above. Count the affected procedures. This is your manual-conversion debt.
  2. For each affected procedure, choose between manual translation (the patterns above are usually sufficient) or running it through Trekport. The Trekport free trial covers up to 10 tables and includes PL/SQL conversion; that is enough to validate the approach on your most-used procedures.
  3. Add a behavioral test for every translated procedure that exercises it against representative input data and compares to the Oracle baseline. The compile-time tests are not enough.

If you have not started, evaluate Trekport against AWS SCT on the same source schema and compare the conversion output line by line. The differences are concrete and easy to verify.

The broader pattern

The BULK COLLECT case is the most well-documented instance of a broader pattern: tools that quote a high automated-conversion percentage by counting compile-success, not behavioral correctness. The 90 percent figure is real. It is also measuring the wrong thing for a migration that needs to ship.

The right metric is the percentage of objects that deploy cleanly and behave correctly against representative input data, the first time, with no manual SQL editing. By that metric, the industry standard is closer to 60 to 70 percent for AWS SCT and Ora2Pg, and 100 percent is achievable with disciplined AST-based conversion.

This is the standard Trekport ships against. It is what the marketing copy on the home page means. And it is the standard we think buyers should hold every Oracle to PostgreSQL migration tool to.

Try Trekport

Download Trekport Studio. Run the free trial against any Oracle schema you have lying around (10-table limit on trial, full conversion engine). Look at the converted PL/pgSQL for any procedure that uses BULK COLLECT or FORALL. Compare to AWS SCT's output on the same procedure.

If you want a guided side-by-side comparison on your real estate, talk to us. We have done this exercise enough times to walk through it efficiently.

Source

AWS Database Blog: Migrate Oracle bulk binds to Amazon Aurora PostgreSQL-compatible edition or Amazon RDS for PostgreSQL.