Modernizing a custom Ellucian reporting layer from Oracle to PostgreSQL in Ellucian Insights

Replacing compatibility-layer calls with PostgreSQL-native behavior

AWS SCT could represent Oracle datatypes and built-in functions through its Oracle Extension Pack, a target-side compatibility layer that emulates source-database functionality needed by converted objects. The migration reduced those dependencies by converting applicable references to native PostgreSQL datatypes, functions and expressions.

Representative mappings included:

Datatype review was also necessary because the type generated by AWS SCT was not always the right PostgreSQL type.

Explicit casting was another recurring case. A converted function call or join could look compatible by column name yet still fail because PostgreSQL enforced the target types differently.

A sanitized version of the correction pattern looks like this:

-- Initial converted pattern

custom_function(person.person_uid, term.academic_period)



JOIN person

  ON term.person_uid = person.person_uid

-- Corrected PostgreSQL pattern

custom_function(person.person_uid::numeric, term.academic_period)



JOIN person

  ON term.person_uid = person.person_uid::numeric

The important change is not the identifier itself. It is the explicit preservation of the expected function and comparison types.

Rebuilding cursor functions

Oracle functions using cursors created another conversion problem. AWS SCT could split one source function into a main function plus a generated cursor helper function. In some cases, parts of the generated PostgreSQL code remained unresolved.

ABCloudz consolidated the generated helper pattern into a single PL/pgSQL function, using PostgreSQL’s procedural language for stored database logic, with the cursor defined and consumed within that function.

A simplified structure is:

CREATE OR REPLACE FUNCTION custom_lookup(

    lookup_type text,

    lookup_value text

)

RETURNS varchar

AS $$

DECLARE

    result_value varchar(255);

    result_cursor CURSOR FOR

        SELECT value_description

        FROM validation_table

        WHERE table_name = lookup_type

          AND value = lookup_value;

BEGIN

    OPEN result_cursor;

    FETCH result_cursor INTO result_value;

    CLOSE result_cursor;



    RETURN result_value;

END;

$$ LANGUAGE plpgsql;

This restored the intended one-function behavior instead of retaining conversion-generated helper functions as part of the application design.

Correcting package-function calls

Package-function references also required review. Automatic conversion could leave the source function name in place rather than use the converted PostgreSQL function, or omit required function parameters.

The remediation pattern was to resolve the correct target function and restore its arguments:

-- Converted pattern

substr(package_function, 1, 63)



-- Corrected pattern

substr(converted_package_function(person_uid, 'ID'), 1, 63)

Converting table-returning functions

Oracle pipelined functions did not map directly to the required PostgreSQL behavior.

The source pattern:

RETURN table_type PIPELINED

was reworked as a PostgreSQL function with an explicit table return definition:

RETURNS TABLE (

    column_a datatype,

    column_b datatype

)

AS $$

BEGIN

    RETURN QUERY

    SELECT ...

    FROM ...;

END;

$$ LANGUAGE plpgsql;

Column references inside the query also had to be qualified where return-column names could otherwise make a reference ambiguous.

Repairing function-based indexes

Function-based indexes exposed another semantic difference. PostgreSQL requires functions used in index expressions to meet immutability requirements.

For expressions that needed a wrapper, the implementation introduced an IMMUTABLE PostgreSQL function and indexed the wrapper result:

CREATE OR REPLACE FUNCTION normalized_index_value(...)

RETURNS varchar

AS $$

BEGIN

    RETURN ...;

END;

$$ LANGUAGE plpgsql

IMMUTABLE;

Custom-function indexes also had to be corrected when AWS SCT generated an internal expression rather than the intended converted custom function.

Together, these cases illustrate why automatic conversion served as a starting point rather than the definition of the final PostgreSQL implementation.

ROWID without creating a permanent surrogate

One option considered for dependencies on Oracle ROWID, a database-provided row locator rather than an application-level business identifier, was to create an additional row-identifier column, sequence and primary key in PostgreSQL.

For this project, the preferred pattern used PostgreSQL’s ctid, a system column that identifies the physical location of a row version, for the documented transaction-scoped ROWID usage rather than introducing an additional persistent column.

The mapping depended on object type:

This is intentionally a scoped pattern. ctid can change when a row is updated or moved, including by VACUUM FULL, so it should not be treated as a durable business identifier.

Resolving Oracle synonyms

Oracle synonyms hid the physical schema behind referenced objects. PostgreSQL required those relationships to become explicit.

For table-like objects, two patterns were available:

1. Create a view that exposes the underlying table under the expected name.
2. Replace the synonym reference with a fully qualified object name.

A simplified example:

-- Legacy logic

SELECT ...

FROM enrollment_source;

could become either:

CREATE VIEW custom_schema.enrollment_source AS

SELECT *

FROM delivered_schema.enrollment_source;

or the consuming view could reference the delivered object directly:

SELECT ...

FROM delivered_schema.enrollment_source;

Functions and package-style calls had a similar choice between wrappers and explicit references to the converted target function. The implementation used the pattern appropriate to the dependency rather than carrying Oracle synonym semantics into PostgreSQL.

Reworking database-link dependencies

Some legacy materialized-view logic referenced data through Oracle database links, database-level references that let one database access objects in another. Three technical patterns were evaluated for PostgreSQL:

• foreign servers and foreign tables;
• direct references when the required objects existed in the same database;
• an external data-movement mechanism such as Data Connect.

Where the source data could be referenced within the same PostgreSQL database, direct schema-qualified access removed the database-link dependency altogether.

The same analysis also helped simplify some legacy copied tables: when an object only duplicated a table already available in another schema, the future state could reference that delivered object directly rather than preserve another custom copy.

The deployment design separates the definition of one object from orchestration across the entire custom layer.

For a table, one sub-pipeline can contain:

A view sub-pipeline contains its Create View operation, including the SQL query that defines the view.

A function sub-pipeline contains a Create Function operation with its arguments, return type and PL/pgSQL body.

This keeps each database object independently deployable while letting the consolidated pipeline enforce dependency order.

A view may depend on another view or table, and a function may reference delivered or custom objects. The consolidated pipeline runs the object-level pipelines in the order those dependencies require.

Materialized views receive a different treatment. Because the final Data Connect deployment model does not create them as materialized views, those objects are deployed as tables. Their data population becomes a separate ETL concern.

Classify the existing population logic first

The final scope included 38 tables that required a data-population decision.

They fell into three broad groups:

Within the last group, several tables simply duplicated data available in another schema. Those copies could be replaced with direct, fully qualified references rather than reproduced as separate custom data-loading processes.

This classification prevented the migration from treating every legacy table as if it required the same type of ETL.

Map source, refresh behavior and schedule

The future-state design includes 26 Data Connect ETL pipelines:

• 19 scheduled daily;
• 3 scheduled hourly;
• 4 driven by CSV files and executed ad hoc.

Database-sourced pipelines use tables or views already present in the environment. Flat-file pipelines use files from the Data Lake.

Refresh behavior also varies by target. Some datasets use a full refresh. Others are scoped by an academic period or another business key carried by the legacy process.

A simplified mapping looks like this:

Plan for larger loads with configurable chunks

The future-state design also accounts for Data Connect execution limits on long-running jobs. For workloads at risk of exceeding those limits, the planned mitigation is to split source datasets into chunks, with the chunk size configured for the individual pipeline.

This keeps the operational concern inside the ETL design rather than forcing the database migration to reproduce legacy loading mechanics unchanged.