Modernizing PL/SQL & Pro*C to Java Microservices

🔹 Legacy Situation

• In the lending platform, a large portion of business logic was embedded in Oracle PL/SQL stored procedures, triggers, and Pro*C batch jobs.

• Example: A loan eligibility check was implemented in a 25K+ line PL/SQL package, tightly coupled with Oracle DB. Similarly, Pro*C programs were handling daily settlement batch processing.

• Challenges: Hard to scale, difficult to test, vendor lock-in with Oracle, limited agility for cloud adoption.

🔹 Modernization Approach

1. Decomposition

   • We analyzed stored procedures and Pro*C code using CAST & VFunction for dependency mapping and complexity analysis.

   • Broke down monolithic packages into domain-driven bounded contexts (e.g., LoanEligibility, CustomerKYC, RepaymentSchedule).

2. Microservices Implementation

   • Rewrote PL/SQL business logic into Spring Boot microservices with Java APIs.

   • Example: LoanEligibilityService now exposes a REST API (/loan/eligibility) instead of running inside PL/SQL.

   • Implemented rules with Drools Rule Engine and Java classes, replacing stored procedure logic.

3. Database Refactoring

   • Moved from Oracle to Postgres.

   • Instead of triggers/stored procedures, used event-driven architecture with Kafka.

   • Example: Instead of an “AFTER INSERT trigger” for new loan applications, now a Kafka LoanInitiated event is published, consumed by the Eligibility microservice.

4. Batch to Streaming

   • Pro*C batch jobs were re-engineered into Kafka streaming jobs + Spring Batch for residual batch processes.

   • Example: Settlement calculation batch → Converted into a streaming pipeline with Kafka + Spark Structured Streaming.

5. Testing & Validation

   • Used automated regression suites to validate that new microservices produced the same results as old PL/SQL/Pro*C jobs.

   • Parallel run in UAT to ensure correctness.

🔹 Outcome

• Reduced database dependency by 70%.

• Improved time-to-market for new features from 6 weeks → 2 weeks.

• Reduced Oracle licensing cost by migrating to Postgres.

• Scalable, API-driven architecture ready for Azure cloud-native deployment.