AWS SCT: unconverted objects when migrating Oracle to PostgreSQL

This runbook covers troubleshooting schema conversion errors in AWS SCT during a heterogeneous Oracle to PostgreSQL migration. For the full migration guide, see Oracle to PostgreSQL Migration on AWS. Once the schema is ready, you move the data with a DMS task - see the AWS DMS guide and the runbook on DMS data validation after cutover. For migration planning, book a consulting session.

Symptoms

In AWS SCT the Assessment Report and the conversion tree show objects that couldn’t be translated automatically. Typical signals:

# Assessment Report / Action Items in SCT:
#   "Packages: 42 objects - 18 can be converted automatically, 24 require manual work"
#   "PL/SQL package APP_PKG.CALC_TOTALS cannot be converted automatically"
#   "Sequence NEXTVAL used in SELECT - no direct PostgreSQL equivalent"
#   "Trigger uses :OLD/:NEW referencing - manual conversion required"
#   "Data type NUMBER without precision mapped to double precision (review)"
#   "Function requires AWS SCT extension pack (aws_oracle_ext) - not installed on target"

# Action item complexity categories (SCT assigns each an estimate in hours):
#   Simple / Medium / Complex / Significant

The object tree shows items flagged red (unconverted) or yellow (converted with warnings). The Assessment Report tallies the estimated effort of manual conversion.

Cause

SCT conversion errors for Oracle → PostgreSQL usually have one of four causes:

• PL/SQL packages and procedures with no direct equivalent: PostgreSQL has no packages in the Oracle sense. PACKAGE/PACKAGE BODY logic, package variables, %ROWTYPE, ref cursors, or AUTONOMOUS_TRANSACTION require manual translation into PL/pgSQL functions/schemas. These are the most common and most labour-intensive action items.
• Oracle-specific constructs in SQL: sequence.NEXTVAL in SELECT, CONNECT BY, ROWNUM, DECODE, (+) in joins, SYSDATE, hierarchical queries. SCT rewrites some automatically and leaves others as action items for manual correction.
• Missing extension pack (aws_oracle_ext) on the target: SCT emulates many Oracle functions through an extensions schema installed on the target database. If the extension pack wasn’t applied, the converted objects reference non-existent aws_oracle_ext.* functions and don’t compile on PostgreSQL.
• Data types with no unambiguous mapping: NUMBER without precision, DATE with a time component, RAW, LONG, CLOB/BLOB, TIMESTAMP WITH LOCAL TIME ZONE. SCT proposes a mapping but needs a review - the wrong type choice pushes the problem down to the DMS data-validation stage.

Fix

A) Organize the action items by category and effort:

# In SCT: View -> Assessment Report View. Sort the action items by:
#   - object category (Packages, Procedures, Triggers, Sequences, Views)
#   - complexity rating (Simple -> Significant)
# Start with the bulk, repeatable patterns (e.g. NEXTVAL, SYSDATE, DECODE)
# that can be resolved with a single rule, rather than object by object.
# Export the list: Assessment Report -> Save to CSV/PDF (to track progress).

B) Install the extension pack (aws_oracle_ext) on the target database:

-- In SCT: right-click the target database -> Apply Extension Pack.
-- Creates the aws_oracle_ext schema with an emulation of Oracle functions on PostgreSQL.

-- Verify on the target (PostgreSQL/Aurora) that the schema and functions exist:
SELECT n.nspname AS schema, count(*) AS funcs
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'aws_oracle_ext'
GROUP BY n.nspname;
-- Expected: schema aws_oracle_ext with a non-zero function count.
-- Without it, converted objects referencing aws_oracle_ext.* won't compile.

C) Converted PL/SQL packages/procedures - translate and apply to the target:

-- Pattern for converting an Oracle package to PostgreSQL:
--   PACKAGE + PACKAGE BODY  ->  a separate schema + PL/pgSQL functions
--   package variables       ->  a table/temp or GUC (set_config/current_setting)
--   package functions        ->  functions in a schema named after the package

-- Example: APP_PKG.CALC_TOTALS -> schema app_pkg, function calc_totals
CREATE SCHEMA IF NOT EXISTS app_pkg;
CREATE OR REPLACE FUNCTION app_pkg.calc_totals(p_order_id bigint)
RETURNS numeric LANGUAGE plpgsql AS $$
DECLARE v_total numeric;
BEGIN
  SELECT COALESCE(SUM(qty * unit_price), 0) INTO v_total
  FROM app_schema.order_lines WHERE order_id = p_order_id;
  RETURN v_total;
END $$;
-- Simple objects (views, sequences) SCT applies automatically: Apply to Database.
-- Move complex ones by hand and version the DDL in the migration repo.

D) Oracle constructs in SQL - replace with PostgreSQL equivalents:

-- Most common replacements (apply via a rule/script wherever SCT left an action item):
--   seq.NEXTVAL           -> nextval('seq')
--   SYSDATE               -> now()  (or clock_timestamp())
--   DECODE(a, x, y, z)     -> CASE WHEN a = x THEN y ELSE z END
--   NVL(a, b)             -> COALESCE(a, b)
--   a (+) = b (outer join) -> LEFT JOIN ... ON a = b
--   CONNECT BY            -> WITH RECURSIVE ...
--   ROWNUM <= n           -> LIMIT n

-- After replacing, check the object compiles on the target (no references to missing functions).

Prevention: run the Assessment Report at the very start of the project - it’s what gives you the real migration estimate and the list of risks before any data moves. Resolve repeatable patterns with a rule, not object by object. Version the target DDL and manual conversions in a repository so they can be reproduced on the test and production environments. Close out the schema conversion before starting the DMS task - otherwise DMS loads data into incomplete structures and the problem comes back as a validation mismatch.

Validation

-- 1. Extension pack installed and visible on the target
SELECT count(*) AS ext_funcs FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'aws_oracle_ext';
-- Expected: > 0

-- 2. All functions/procedures compile (no objects in an error state)
--    Quick way: try loading the DDL onto a clean test environment and check for errors.
--    Object-count check source vs target by type:
SELECT 'functions' AS obj, count(*) FROM information_schema.routines
WHERE specific_schema IN ('app_schema','app_pkg');
-- Expected: counts consistent with the conversion plan (accounting for packages -> functions)

# 3. In SCT: a re-run Assessment Report shows no unresolved action items
#    (or only consciously accepted, documented exceptions).
# 4. A test call of the converted logic on the target returns the same results as Oracle
#    for a representative data set (compare results, not just "it compiles").

If the extension pack is installed, all objects compile on the target, the Assessment Report reports no unresolved items, and test calls of the converted logic return results matching Oracle, the target schema is ready to start the data migration with a DMS task.

SCT reporting dozens of action items?

Book a free 30-minute call. We'll review the assessment report, set the strategy for converting PL/SQL packages, and close out the schema before the data migration.

Book a call Send us a message