Modernising Oracle Stored Procedures to Java Services in Banking and Insurance (with GitLab Duo)

Banking and insurance systems often sit on top of battle-tested Oracle databases. Over the years, a huge amount of business logic has migrated into PL/SQL stored procedures: customer onboarding, KYC checks, fund transfers, premium calculations, claim adjudication… you name it.

This architecture has worked for decades. But it comes with a cost:

• Business rules are trapped in the database
• Changes are risky and slow
• Automated testing is hard
• Scaling is constrained by the database tier

Modernisation usually means moving this logic into Java services (e.g., Spring Boot), while retaining Oracle as a reliable system of record. In this journey, tools like GitLab Duo act as copilots: helping you understand legacy PL/SQL, sketch new service layers, and generate tests and pipelines.

In this blog, we’ll walk through:

• What a stored-procedure–centric Oracle application looks like in BFSI
• A target Java-based architecture
• A practical migration strategy
• A concrete example: turning an Oracle fund transfer stored procedure into a Java service
• How GitLab Duo helps along the way
• Before/after comparison and lessons learned

1. What “Stored-Procedure–Centric” Looks Like in BFSI

In many banks and insurers, the core systems resemble this:

Legacy Architecture:
[Channel (Web/Mobile/Branch)] → [App Server / ESB] → [Oracle DB] → [PL/SQL Packages & Procedures]

Examples from banking:

• SP_OPEN_SAVINGS_ACCOUNT
• SP_TRANSFER_FUNDS
• PKG_LOAN_DISBURSAL

Examples from insurance:

• PKG_POLICY_ISSUANCE
• SP_CALCULATE_PREMIUM
• SP_PROCESS_CLAIM

Common traits:

• Business logic lives in PL/SQL: validations, calculations, eligibility checks, fee/interest computations.
• The Java (or .NET) app often becomes a thin wrapper around stored procedures.
• Integrations call PL/SQL via JDBC CallableStatements, passing IN/OUT parameters.
• Testing is mostly manual or done via end-to-end UI tests.
• Deployments are split: DB changes vs app changes, with tricky coordination.

Why modernise?

• Need for microservices and API-driven architectures (open banking, embedded insurance).
• Stronger automated testing and CI/CD.
• More flexible scaling, especially for read-heavy and compute-heavy workloads.
• Easier talent hiring (Java developers are easier to find than PL/SQL experts).
• Regulatory & audit: better traceability, decoupling, and observability.

2. Target Java Architecture for Banking & Insurance

A modernised architecture typically looks like:

Target Architecture:
[Channels (Mobile, Web, Partner APIs)]
→ [API Gateway]
→ [Java Microservices (Spring Boot)]
→ [Oracle / Other Data Stores]

Key characteristics:

• Business logic moves into Java services
• Banking: Customer Service, Account Service, Payments Service
• Insurance: Policy Service, Pricing Service, Claims Service
• Oracle remains as a transactional system of record
• Data access via JDBC or JPA (Hibernate), not via stored procedures (or heavily reduced)
• Clear separation of:
• Controller layer (REST APIs)
• Service layer (business rules)
• Repository/DAO layer (DB access)
• CI/CD pipeline: build, test, static analysis, deploy

GitLab & GitLab Duo in this target state:

• GitLab: single platform for repos, issues, CI/CD, environments
• GitLab Duo:
• Explains complex legacy code (PL/SQL and Java)
• Suggests Java implementation based on comments or PL/SQL snippets
• Generates or improves unit/integration tests
• Assists with .gitlab-ci.yml pipelines and MR reviews

3. Migration Strategy: Incremental, Risk-Aware, AI-Assisted

A sensible approach for regulated BFSI environments:

3.1 Strangler-Fig Pattern vs Big-Bang Rewrite

• Big-bang rewrite: migrate all PL/SQL logic to Java at once
• High risk, long duration, large “blackout” window
• Strangler-fig pattern: gradually route new/selected functionalities to new services
• Start with well-bounded use cases (e.g., fund transfer, premium calculation)
• Coexistence: some logic in PL/SQL, some in Java
• Gradually “strangle” the old monolith and procedures

In BFSI, strangler-fig is usually preferred due to risk, audit, and stability concerns.

3.2 Prioritising Stored Procedures to Migrate

Typical ranking criteria:

1. Business criticality (e.g., payments, FNOL for insurance)
2. Change frequency (logic that changes often benefits more from Java)
3. Complexity (start with medium, not the most complex)
4. Dependencies (how many other modules call it)
5. Performance profile (procedures causing DB hotspots)

GitLab Duo helps here by:

• Summarising large PL/SQL packages: “Explain what PKG_FUNDS_TRANSFER does”
• Mapping procedure dependencies: “List procedures in this package and who calls them”
• Identifying candidate boundaries: “Suggest logical services from this package”

3.3 Coexistence Phase

During migration:

• Some flows still call PL/SQL.
• Some flows call new Java services that work directly with tables.
• For safety, you may:
• Keep PL/SQL contracts for external systems while re-implementing logic in Java behind them.
• Use feature flags to toggle between old/new paths.
• Run shadow traffic to Java services to validate outputs vs legacy.

4. End-to-End Example: Fund Transfer from PL/SQL to Java Service

Let’s walk through a concrete example common in banks:

Use Case: Internal fund transfer between two accounts in the same bank.

4.1 The Legacy Oracle Stored Procedure

Simplified PL/SQL:

CREATE OR REPLACE PROCEDURE SP_TRANSFER_FUNDS (
    p_from_account   IN  VARCHAR2,
    p_to_account     IN  VARCHAR2,
    p_amount         IN  NUMBER,
    p_txn_id         OUT VARCHAR2,
    p_status_code    OUT VARCHAR2,
    p_status_message OUT VARCHAR2
) AS
    v_from_balance   NUMBER;
    v_to_balance     NUMBER;
    v_new_txn_id     VARCHAR2(50);
BEGIN
    -- 1. Check source account balance
    SELECT balance
      INTO v_from_balance
      FROM accounts
     WHERE account_no = p_from_account
       FOR UPDATE;

    IF v_from_balance < p_amount THEN
        p_status_code    := 'INSUFFICIENT_FUNDS';
        p_status_message := 'Insufficient balance in source account';
        RETURN;
    END IF;    -- 2. Lock destination account and get balance
    SELECT balance
      INTO v_to_balance
      FROM accounts
     WHERE account_no = p_to_account
       FOR UPDATE;    -- 3. Debit source account
    UPDATE accounts
       SET balance = balance - p_amount
     WHERE account_no = p_from_account;    -- 4. Credit destination account
    UPDATE accounts
       SET balance = balance + p_amount
     WHERE account_no = p_to_account;    -- 5. Insert into transactions table
    v_new_txn_id := 'TXN_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '_' || p_from_account;    INSERT INTO account_transactions (
        txn_id, from_account, to_account, amount,
        txn_date, status
    ) VALUES (
        v_new_txn_id, p_from_account, p_to_account, p_amount,
        SYSDATE, 'SUCCESS'
    );    -- 6. Commit done by caller or AUTOCOMMIT (depending on design)
    p_txn_id         := v_new_txn_id;
    p_status_code    := 'SUCCESS';
    p_status_message := 'Funds transferred successfully';EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_status_code    := 'ACCOUNT_NOT_FOUND';
        p_status_message := 'Source or destination account not found';    WHEN OTHERS THEN
        p_status_code    := 'SYSTEM_ERROR';
        p_status_message := SQLERRM;
END SP_TRANSFER_FUNDS;
/

What this does:

1. Locks and checks source account balance.
2. Locks destination account.
3. Debits and credits the accounts.
4. Records a transaction.
5. Returns a transaction ID and status.

In many banks, dozens of channels and downstream systems rely on this procedure.

4.2 Using GitLab Duo to Understand the Legacy Logic

A developer could:

• Open the PL/SQL file in the IDE integrated with GitLab / GitLab Duo.
• Ask: “Explain what SP_TRANSFER_FUNDS does, step by step, including error handling and concurrency considerations.”

Duo can:

• Summarise the procedure in plain language.
• Highlight potential issues (e.g., what if p_amount <= 0? what about currency?).
• Help you document the procedure — valuable in regulated environments.

5. Designing the Java Service

Implement a Spring Boot service:

/api/v1/payments/transfer

Layers:

• DTOs: request/response objects
• Entities: Account, AccountTransaction
• Repository interfaces (Spring Data JPA)
• Service: business logic
• Controller: REST API

5.1 Domain Entities

// Account.java
package com.bank.payments.domain;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Version;
import java.math.BigDecimal;@Entity
public class Account {    @Id
    private String accountNo;    private BigDecimal balance;    @Version
    private Long version; // optimistic locking    // getters and setters omitted for brevity
}

// AccountTransaction.java
package com.bank.payments.domain;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.math.BigDecimal;
import java.time.OffsetDateTime;@Entity
public class AccountTransaction {    @Id
    private String txnId;    private String fromAccount;
    private String toAccount;
    private BigDecimal amount;
    private OffsetDateTime txnDate;
    private String status;    // getters and setters omitted
}

5.2 Repositories

// AccountRepository.java
package com.bank.payments.repository;

import com.bank.payments.domain.Account;
import org.springframework.data.jpa.repository.JpaRepository;public interface AccountRepository extends JpaRepository<Account, String> {
}

// AccountTransactionRepository.java
package com.bank.payments.repository;

import com.bank.payments.domain.AccountTransaction;
import org.springframework.data.jpa.repository.JpaRepository;public interface AccountTransactionRepository extends JpaRepository<AccountTransaction, String> {
}

You can ask GitLab Duo to:

• Generate repository interfaces.
• Suggest query methods if you need custom lookups.
• Highlight missing indexes or performance considerations based on queries.

5.3 DTOs and Exceptions

// TransferRequest.java
package com.bank.payments.api.dto;

import java.math.BigDecimal;public class TransferRequest {
    private String fromAccount;
    private String toAccount;
    private BigDecimal amount;    // getters and setters
}

// TransferResponse.java
package com.bank.payments.api.dto;

public class TransferResponse {
    private String txnId;
    private String statusCode;
    private String statusMessage;    // constructors, getters, setters
}

// BusinessException.java
package com.bank.payments.service;

public class BusinessException extends RuntimeException {    private final String statusCode;    public BusinessException(String statusCode, String message) {
        super(message);
        this.statusCode = statusCode;
    }    public String getStatusCode() {
        return statusCode;
    }
}

5.4 Service Layer with Transactions

// FundsTransferService.java
package com.bank.payments.service;

import com.bank.payments.api.dto.TransferRequest;
import com.bank.payments.api.dto.TransferResponse;
import com.bank.payments.domain.Account;
import com.bank.payments.domain.AccountTransaction;
import com.bank.payments.repository.AccountRepository;
import com.bank.payments.repository.AccountTransactionRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import java.math.BigDecimal;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.UUID;@Service
public class FundsTransferService {    private final AccountRepository accountRepository;
    private final AccountTransactionRepository txnRepository;    public FundsTransferService(AccountRepository accountRepository,
                                AccountTransactionRepository txnRepository) {
        this.accountRepository = accountRepository;
        this.txnRepository = txnRepository;
    }    @Transactional
    public TransferResponse transfer(TransferRequest request) {        validateRequest(request);        Account from = accountRepository.findById(request.getFromAccount())
                .orElseThrow(() -> new BusinessException("ACCOUNT_NOT_FOUND",
                        "Source account not found"));        Account to = accountRepository.findById(request.getToAccount())
                .orElseThrow(() -> new BusinessException("ACCOUNT_NOT_FOUND",
                        "Destination account not found"));        if (from.getBalance().compareTo(request.getAmount()) < 0) {
            throw new BusinessException("INSUFFICIENT_FUNDS",
                    "Insufficient balance in source account");
        }        // Debit source
        from.setBalance(from.getBalance().subtract(request.getAmount()));
        // Credit destination
        to.setBalance(to.getBalance().add(request.getAmount()));        accountRepository.save(from);
        accountRepository.save(to);        String txnId = "TXN_" + UUID.randomUUID();
        AccountTransaction txn = new AccountTransaction();
        txn.setTxnId(txnId);
        txn.setFromAccount(from.getAccountNo());
        txn.setToAccount(to.getAccountNo());
        txn.setAmount(request.getAmount());
        txn.setTxnDate(OffsetDateTime.now(ZoneOffset.UTC));
        txn.setStatus("SUCCESS");        txnRepository.save(txn);        return new TransferResponse(txnId, "SUCCESS", "Funds transferred successfully");
    }    private void validateRequest(TransferRequest request) {
        if (request.getAmount() == null || request.getAmount().compareTo(BigDecimal.ZERO) <= 0) {
            throw new BusinessException("INVALID_AMOUNT", "Amount must be greater than zero");
        }
        if (request.getFromAccount().equals(request.getToAccount())) {
            throw new BusinessException("INVALID_ACCOUNTS", "Source and destination must differ");
        }
    }
}

GitLab Duo can help here by:

• Generating the initial skeleton of FundsTransferService from comments or a description.
• Suggesting validations you may have missed (e.g., null checks, limits).
• Refactoring large methods into smaller ones (validateRequest, createTransaction, etc.).

5.5 Controller Layer

// FundsTransferController.java
package com.bank.payments.api;

import com.bank.payments.api.dto.TransferRequest;
import com.bank.payments.api.dto.TransferResponse;
import com.bank.payments.service.BusinessException;
import com.bank.payments.service.FundsTransferService;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;@RestController
@RequestMapping("/api/v1/payments")
public class FundsTransferController {    private final FundsTransferService transferService;    public FundsTransferController(FundsTransferService transferService) {
        this.transferService = transferService;
    }    @PostMapping("/transfer")
    public ResponseEntity<TransferResponse> transfer(@RequestBody TransferRequest request) {
        TransferResponse response = transferService.transfer(request);
        return ResponseEntity.ok(response);
    }    @ExceptionHandler(BusinessException.class)
    public ResponseEntity<TransferResponse> handleBusinessException(BusinessException ex) {
        TransferResponse response = new TransferResponse(null, ex.getStatusCode(), ex.getMessage());
        HttpStatus status = "INSUFFICIENT_FUNDS".equals(ex.getStatusCode())
                ? HttpStatus.BAD_REQUEST
                : HttpStatus.UNPROCESSABLE_ENTITY;        return new ResponseEntity<>(response, status);
    }    @ExceptionHandler(Exception.class)
    public ResponseEntity<TransferResponse> handleGenericException(Exception ex) {
        TransferResponse response = new TransferResponse(null, "SYSTEM_ERROR", "Internal server error");
        return new ResponseEntity<>(response, HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

Again, GitLab Duo can:

• Suggest controller boilerplate.
• Propose consistent error-response structures.
• Help extract common exception handling into a @ControllerAdvice.

6. Testing Strategy: A Big Win When Moving to Java

In BFSI, testing and auditability are critical.

With business logic in Java, you can:

• Write unit tests for the service layer without hitting the database.
• Write integration tests using an in-memory DB or test schema.
• Automate tests in GitLab CI.

6.1 Example Unit Test

// FundsTransferServiceTest.java
package com.bank.payments.service;

import com.bank.payments.api.dto.TransferRequest;
import com.bank.payments.domain.Account;
import com.bank.payments.repository.AccountRepository;
import com.bank.payments.repository.AccountTransactionRepository;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;import java.math.BigDecimal;
import java.util.Optional;import static org.junit.jupiter.api.Assertions.*;
import static org.mockito.Mockito.*;class FundsTransferServiceTest {    private AccountRepository accountRepository;
    private AccountTransactionRepository txnRepository;
    private FundsTransferService service;    @BeforeEach
    void setUp() {
        accountRepository = mock(AccountRepository.class);
        txnRepository = mock(AccountTransactionRepository.class);
        service = new FundsTransferService(accountRepository, txnRepository);
    }    @Test
    void transfer_successful() {
        Account from = new Account();
        from.setAccountNo("A1");
        from.setBalance(new BigDecimal("1000.00"));        Account to = new Account();
        to.setAccountNo("A2");
        to.setBalance(new BigDecimal("500.00"));        when(accountRepository.findById("A1")).thenReturn(Optional.of(from));
        when(accountRepository.findById("A2")).thenReturn(Optional.of(to));        TransferRequest request = new TransferRequest();
        request.setFromAccount("A1");
        request.setToAccount("A2");
        request.setAmount(new BigDecimal("200.00"));        var response = service.transfer(request);        assertEquals("SUCCESS", response.getStatusCode());
        assertEquals(new BigDecimal("800.00"), from.getBalance());
        assertEquals(new BigDecimal("700.00"), to.getBalance());        verify(txnRepository, times(1)).save(any());
    }    @Test
    void transfer_insufficientFunds() {
        Account from = new Account();
        from.setAccountNo("A1");
        from.setBalance(new BigDecimal("100.00"));        Account to = new Account();
        to.setAccountNo("A2");
        to.setBalance(new BigDecimal("500.00"));        when(accountRepository.findById("A1")).thenReturn(Optional.of(from));
        when(accountRepository.findById("A2")).thenReturn(Optional.of(to));        TransferRequest request = new TransferRequest();
        request.setFromAccount("A1");
        request.setToAccount("A2");
        request.setAmount(new BigDecimal("200.00"));        BusinessException ex = assertThrows(BusinessException.class,
                () -> service.transfer(request));        assertEquals("INSUFFICIENT_FUNDS", ex.getStatusCode());
    }
}

How GitLab Duo helps with tests:

• Generate initial test methods based on FundsTransferService logic.
• Suggest additional edge cases (zero amount, same account, missing account).
• Help mock dependencies and make assertions consistent.

6.2 CI Pipeline with GitLab

A simple .gitlab-ci.yml snippet:

stages:
  - build
  - test

maven-build:
  stage: build
  script:
    - mvn -B clean compilemaven-test:
  stage: test
  script:
    - mvn -B test
  artifacts:
    when: always
    reports:
      junit: target/surefire-reports/*.xml

You can ask GitLab Duo:

“Update .gitlab-ci.yml to add code coverage reports and run integration tests with profile it.”

It can propose changes, and then you adjust for your organisation’s standards.

7. Key Technical Topics in BFSI Context

7.1 Transactions

• PL/SQL often relies on implicit transactions or DB-level control.
• In Java, you use Spring’s @Transactional to:
• Define transaction boundaries clearly.
• Integrate with distributed transactions (if needed).
• For BFSI, ensure:
• Idempotence for critical operations (retries).
• Proper isolation levels for concurrent transfers.

GitLab Duo can help:

• Spot missing @Transactional annotations.
• Suggest transaction boundaries based on methods that perform multiple updates.

7.2 Validation & Business Rules

• In PL/SQL, validations are often spread across IFs and nested blocks.
• In Java, you can:
• Use bean validation (@NotNull, @Positive) for input.
• Encapsulate rules in dedicated services or domain objects.

For insurance:

• Premium or claim calculations can be extracted into dedicated pricing or claims services.

GitLab Duo can:

• Extract repeated validation logic into shared helper methods.
• Suggest where to introduce enum types or value objects for clarity (e.g., TransactionStatus, Currency).

7.3 Error Handling & Logging

• PL/SQL uses EXCEPTION blocks and error codes.
• Java uses exceptions, error codes, and structured logs.

Recommendations:

• Map PL/SQL status codes into domain-specific error codes in Java.
• Standardize error responses for APIs (HTTP 4xx/5xx with JSON bodies).
• Log with correlation IDs for audit trails (critical in BFSI).

GitLab Duo can:

• Help you design a consistent error model.
• Suggest improvements to logging statements and their context.

7.4 Performance & Concurrency

When moving logic out of the DB:

• Watch for extra round trips to the database.
• Use batch operations where appropriate.
• Consider caching for read-heavy operations (e.g., static interest rate tables).

On the DB side:

• Consider optimistic locking (as shown with @Version) or pessimistic locks when needed.
• Ensure proper indexing and query optimization remain in place.

GitLab Duo can:

• Flag potentially N+1 query patterns.
• Suggest index hints or query improvements (though DBAs should still validate).

8. Before vs After: Modernisation Snapshot

Press enter or click to view image in full size

9. Conclusion and Lessons Learned

Modernising Oracle stored-procedure–based applications into Java services is not just a technology upgrade. In BFSI, it’s about:

• Making core banking and insurance logic more transparent, testable, and auditable
• Enabling faster change while preserving safety and compliance
• Preparing for open banking, embedded insurance, and cloud-native deployments

GitLab Duo doesn’t replace architects and developers — it amplifies them:

• Helps understand complex PL/SQL logic and legacy Java code
• Accelerates scaffolding of new services, DTOs, and tests
• Assists in evolving CI/CD pipelines and reviewing merge requests

Practical Tips for BFSI Teams

1. Start with a well-bounded use case
   Pick something like fund transfer, premium calculation, or claim status — not the entire core system.
2. Use GitLab Duo early, for analysis
   Upload PL/SQL packages and ask Duo to explain, summarise, and propose candidate service boundaries.
3. Mirror existing rules first, then improve
   Ensure the Java service behaves identically to the stored procedure (same error codes, edge cases) before refactoring.
4. Bake tests into the plan
   Use GitLab Duo to generate initial tests, then beef them up. In BFSI, tests are your shield against regressions.
5. Document & standardize patterns
   Once you have one “golden path” (like the fund transfer service), make it a reusable pattern: code templates, CI jobs, and guidelines that everyone follows.