At a Glance

Industry	Higher education and academic medicine
Organization	A leading academic medical center and research university (client details generalized to protect confidentiality)
Migration	Oracle PL/SQL procedures and mappings → Informatica PowerCenter
Challenge	Procedural, database-bound logic across roughly 1,500 tables, with no one-to-one equivalent in a declarative ETL dataflow
Scope	Approximately 1,500 tables across the Finance, HR, faculty, and student and staff domains
Approach	Automated conversion with Python-based validation, delivered entirely inside the client's own environment

A LEADING ACADEMIC MEDICAL CENTER AND RESEARCH UNIVERSITY

Migrating PL/SQL to Informatica PowerCenter for a Leading Academic Medical Center

Customer Background

A Research University Running Its Data Integration on PL/SQL

A leading academic medical center and research university ran its enterprise data integration on Oracle PL/SQL. Procedures moved and transformed data across the Finance, HR, faculty, and student and staff domains, spanning about 1,500 tables.

The organization standardized Informatica PowerCenter as its enterprise ETL platform. That meant moving years of accumulated procedural logic out of the database and into governed, visual mappings, without changing what any of it produced.

Customer Storeis

The Challenge

Procedural Logic with No Direct ETL Equivalent

PL/SQL and Informatica work on fundamentally different models. PL/SQL is procedural and row-by-row; PowerCenter is declarative and set-based. Converting between them is a redesign, not a translation. The key challenges:

No one-to-one mapping for procedural logic

A cursor loop with conditional branching has no single transformation equivalent. It has to be redesigned as a set-based dataflow using Router, Filter, Expression, and Update Strategy transformations.

Nested package dependencies

Packages calling packages calling functions meant full dependency tracing was required before any conversion work could safely begin.

Exception handling and transaction control

PL/SQL EXCEPTION blocks and explicit COMMIT and ROLLBACK statements have no direct counterpart, so we had to re-express them as commit intervals, reject handling, and session-level error strategies.

Order-dependent logic

Procedural code depends on execution order, and dataflows don't guarantee order unless explicitly designed for it, so we had to handle sequencing deliberately.

Scale and consistency

With roughly 1,500 tables, a manual table-by-table rebuild would have been slow and produced inconsistent results across developers.

Our Approach

Automated Conversion, Validated Programmatically

DataTerrain replaced the manual, table-by-table approach with an automated conversion process applied consistently across the estate, then validated every converted object with scripted checks rather than visual review.

1

Automated Conversion at Scale

DataTerrain replaced the manual table-by-table method with an automated process, applied consistently so results did not vary from developer to developer.

2

Python-Based Validation on Every Object

Validation scripts ran A-minus-B and B-minus-A comparisons, row counts, and additional checks against every converted object, proving equivalence programmatically rather than accepting output that merely looked right.

3

Performance Tuning as Part of Delivery

DataTerrain gathered statistics and tuned mappings so the converted pipelines performed in production, not just produced correct results.

4

Delivered Inside the Client's Own Environment

All conversion work ran inside the client's own systems rather than an external environment, and DataTerrain handed over reference documentation for the converted objects.

Business Impact

Outcomes That Mattered to the Business

Runtime Cut from Seven Hours to Two

A process that took seven hours to complete on the legacy PL/SQL system now finishes in two, a reduction of more than 70%.

Like-for-Like Conversion

Output was validated as equivalent to the original PL/SQL, not approximated.

Manual Replaced by Automated

Automated conversion replaced a table-by-table manual process across the estate.

Programmatic Validation

Ran A-minus-B, B-minus-A, row-count, and additional scripted checks on every converted object.

Tuned for Production

Gathered statistics and tuned mappings so performance held up under real workloads.

Documented for Handover:

Provided reference documentation for the converted objects.

Bottom Line

Why Choose DataTerrain

An estate this size is where manual conversion breaks down, not because any single object is impossible, but because you can't keep consistency and verification up by hand. DataTerrain automated the conversion so results stayed consistent across roughly 1,500 tables, then proved equivalence with scripted validation on every object rather than relying on spot checks. All of it ran in the client's environment, with documentation handed over for the converted objects, and the relationship has continued for years with ongoing support.

legacy reports conversion connect with us

"DataTerrain converted our PL/SQL data integration to Informatica PowerCenter across roughly 1,500 tables without changing what any of it produced. We validated every converted object with scripted checks; a process that took seven hours now finishes in two, and all the work ran in our environment with documentation handed over. They have supported us for years since." Director
Leading Academic Medical Center and Research University