Pro*c Job to Spring Batch Job

Anand Nerurkar
2 hours ago
6 min read

Example1:

📌 Background

Pro*C job → Written in C with embedded SQL, often used for batch ETL-like jobs in Oracle.
Spring Batch job → Java-based batch framework supporting chunk-oriented processing, scheduling, retry/restart, transaction management, and scalability.

📌 Sample Use Case

Existing Pro*C Job Functionality:

Read loan application records from LOAN_APPLICATIONS table where STATUS = 'NEW'.
Validate credit score by calling an external service (via stored procedure / API).
Update the STATUS column to either ‘APPROVED’ or ‘REJECTED’ in the same table.
Generate a daily log file of processed applications.

📌 Step 1 – Analyze the Pro*C Job

Sample Pro*C Pseudocode:

EXEC SQL DECLARE app_cursor CURSOR FOR
    SELECT ID, CUSTOMER_ID, AMOUNT FROM LOAN_APPLICATIONS WHERE STATUS = 'NEW';

while (FETCH app_cursor INTO :id, :cust_id, :amt) {
   credit_score = call_credit_bureau_api(cust_id);
   if (credit_score > 700) {
      EXEC SQL UPDATE LOAN_APPLICATIONS SET STATUS = 'APPROVED' WHERE ID = :id;
   } else {
      EXEC SQL UPDATE LOAN_APPLICATIONS SET STATUS = 'REJECTED' WHERE ID = :id;
   }
   write_to_log_file(id, cust_id, amt, credit_score);
}
EXEC SQL COMMIT;

📌 Step 2 – Spring Batch Job Design

We design it in Reader → Processor → Writer style.

ItemReader → Reads loan applications with status NEW from DB.
ItemProcessor → Calls credit score API & decides APPROVED/REJECTED.
ItemWriter → Updates DB with new status + writes logs.

📌 Step 3 – Spring Batch Job Implementation

(a) Entity & DTO

@Data
@AllArgsConstructor
@NoArgsConstructor
public class LoanApplication {
    private Long id;
    private String customerId;
    private Double amount;
    private String status;
}

(b) ItemReader (JDBC Reader)

@Bean
public JdbcCursorItemReader<LoanApplication> loanReader(DataSource dataSource) {
    return new JdbcCursorItemReaderBuilder<LoanApplication>()
            .dataSource(dataSource)
            .name("loanReader")
            .sql("SELECT ID, CUSTOMER_ID, AMOUNT, STATUS FROM LOAN_APPLICATIONS WHERE STATUS='NEW'")
            .rowMapper((rs, rowNum) -> new LoanApplication(
                    rs.getLong("ID"),
                    rs.getString("CUSTOMER_ID"),
                    rs.getDouble("AMOUNT"),
                    rs.getString("STATUS")
            ))
            .build();
}

(c) ItemProcessor

@Component
public class LoanProcessor implements ItemProcessor<LoanApplication, LoanApplication> {

    @Override
    public LoanApplication process(LoanApplication loan) throws Exception {
        int creditScore = callCreditBureau(loan.getCustomerId()); // external API
        if (creditScore > 700) {
            loan.setStatus("APPROVED");
        } else {
            loan.setStatus("REJECTED");
        }
        return loan;
    }

    private int callCreditBureau(String customerId) {
        // Simulated API call
        return (int) (Math.random() * 900);
    }
}

(d) ItemWriter (Update DB + Log File)

@Bean
public ItemWriter<LoanApplication> loanWriter(DataSource dataSource) {
    return items -> {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        for (LoanApplication loan : items) {
            jdbcTemplate.update("UPDATE LOAN_APPLICATIONS SET STATUS=? WHERE ID=?",
                    loan.getStatus(), loan.getId());

            // log file
            Files.write(Paths.get("loan-processing.log"),
                (loan.toString() + "\n").getBytes(),
                StandardOpenOption.CREATE, StandardOpenOption.APPEND);
        }
    };
}

(e) Job Configuration

@Bean
public Job loanJob(JobBuilderFactory jobBuilderFactory, StepBuilderFactory stepBuilderFactory,
                   ItemReader<LoanApplication> reader,
                   ItemProcessor<LoanApplication, LoanApplication> processor,
                   ItemWriter<LoanApplication> writer) {

    Step step = stepBuilderFactory.get("loanStep")
            .<LoanApplication, LoanApplication>chunk(10)
            .reader(reader)
            .processor(processor)
            .writer(writer)
            .build();

    return jobBuilderFactory.get("loanJob")
            .incrementer(new RunIdIncrementer())
            .flow(step)
            .end()
            .build();
}

📌 Step 4 – Migration Steps Summary

Analyze Pro*C job logic – SQLs, loops, validations, file writes.
Model equivalent batch job in Spring Batch – Reader, Processor, Writer.
Replace embedded SQL with JDBC/ORM (Spring Data / JPA / JdbcTemplate).
Replace stored procedure calls with REST API clients or JDBC calls.
Replace file writes with Spring Batch writers (FlatFileItemWriter / custom writer).
Wrap in Job configuration for scheduling (Spring Scheduler / Quartz / Cron / Control-M).
Test for functional equivalence + performance tuning (chunk size, commit interval).
Deploy as Spring Boot microservice on cloud or on-prem batch infra.

Example 2:

📌 Scenario

Existing Pro*C Job:
- Reads customer loan data from Oracle DB.
- For each loan record, performs some business logic (e.g., calculate EMI).
- Writes results back into another loan_summary table.
- Scheduled to run daily at midnight.

We need to migrate this Pro*C job into Spring Batch.

🔹 Step 1: Analyze the Pro*C Job

Pro*C job typically has:

C code + Embedded SQL (EXEC SQL).
CURSOR definitions to fetch rows.
Loops to process data.
DML inserts/updates.

Example Pro*C snippet:

EXEC SQL DECLARE loan_cursor CURSOR FOR
  SELECT loan_id, principal, tenure, interest_rate FROM loan;

EXEC SQL OPEN loan_cursor;

while (sqlca.sqlcode == 0) {
   EXEC SQL FETCH loan_cursor INTO :loan_id, :principal, :tenure, :interest_rate;

   emi = (principal * interest_rate / 100) / tenure;

   EXEC SQL INSERT INTO loan_summary (loan_id, emi) VALUES (:loan_id, :emi);
}
EXEC SQL CLOSE loan_cursor;

🔹 Step 2: Define Spring Batch Job Structure

Spring Batch works on Reader → Processor → Writer.

Reader → Reads loan records from DB (similar to CURSOR).
Processor → Applies EMI calculation.
Writer → Inserts results into loan_summary table.

🔹 Step 3: Implement Spring Batch Components

1️⃣ Reader – JdbcCursorItemReader

@Bean
public JdbcCursorItemReader<Loan> loanItemReader(DataSource dataSource) {
    return new JdbcCursorItemReaderBuilder<Loan>()
            .dataSource(dataSource)
            .name("loanItemReader")
            .sql("SELECT loan_id, principal, tenure, interest_rate FROM loan")
            .rowMapper(new LoanRowMapper())
            .build();
}

public class LoanRowMapper implements RowMapper<Loan> {
    @Override
    public Loan mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Loan(
            rs.getLong("loan_id"),
            rs.getDouble("principal"),
            rs.getInt("tenure"),
            rs.getDouble("interest_rate")
        );
    }
}

2️⃣ Processor – EMI Calculation

@Component
public class LoanProcessor implements ItemProcessor<Loan, LoanSummary> {
    @Override
    public LoanSummary process(Loan loan) {
        double emi = (loan.getPrincipal() * loan.getInterestRate() / 100) / loan.getTenure();
        return new LoanSummary(loan.getLoanId(), emi);
    }
}

3️⃣ Writer – JdbcBatchItemWriter

@Bean
public JdbcBatchItemWriter<LoanSummary> loanItemWriter(DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder<LoanSummary>()
            .dataSource(dataSource)
            .sql("INSERT INTO loan_summary (loan_id, emi) VALUES (:loanId, :emi)")
            .beanMapped()
            .build();
}

4️⃣ Job & Step Configuration

@Bean
public Step loanStep(JobRepository jobRepository, 
                     PlatformTransactionManager transactionManager,
                     JdbcCursorItemReader<Loan> reader,
                     LoanProcessor processor,
                     JdbcBatchItemWriter<LoanSummary> writer) {

    return new StepBuilder("loanStep", jobRepository)
            .<Loan, LoanSummary>chunk(100, transactionManager)
            .reader(reader)
            .processor(processor)
            .writer(writer)
            .build();
}

@Bean
public Job loanJob(JobRepository jobRepository, Step loanStep) {
    return new JobBuilder("loanJob", jobRepository)
            .start(loanStep)
            .build();
}

🔹 Step 4: Scheduling (Replace Cron/Control-M/Unix Script)

Earlier Pro*C job was scheduled via Unix cron.
In Spring Boot:
- Use Spring Scheduler (@Scheduled(cron="0 0 0 *")) OR
- Integrate with Quartz OR
- External job scheduler (Control-M, Autosys, Airflow).

🔹 Step 5: Testing & Validation

Unit test Processor logic.
Run job with sample dataset.
Validate DB inserts in loan_summary.
Benchmark performance against old Pro*C job.

✅ End Result:The Pro*C job is successfully migrated to Spring Batch, improving maintainability, testability, and enabling integration with cloud-native environments.

Example 3:

✅ Mapping Summary

SQLCODE == 0 → normal Spring Batch flow (reader → processor → writer).
SQLCODE < 0 → SQLException triggers RetryPolicy / SkipPolicy / fail step.
SQLCODE > 0 → mapped to LoanWarningException → logged/skipped but job continues.

🔹 Pro*C Reference

SQLCODE == 0 → success, continue.
SQLCODE < 0 → error, stop or handle exception.
SQLCODE > 0 → warning (like “no rows”), continue but log.

🔹 Spring Batch Equivalent

Success → just return processed item (equivalent to SQLCODE == 0).
Error (SQLCODE < 0) → handled via SkipPolicy / RetryPolicy / Job step failure.
Warning (SQLCODE > 0) → Spring Batch doesn’t give warnings directly, but we can simulate with SQLWarning on JDBC connection or custom validation exceptions.

proc*c

===

/* Pro*C pseudo-code */

EXEC SQL DECLARE loan_cursor CURSOR FOR

SELECT APPLICATION_ID, CREDIT_SCORE

FROM LOAN_APPLICATIONS;

EXEC SQL OPEN loan_cursor;

while (SQLCODE == 0) {

EXEC SQL FETCH loan_cursor INTO :appId, :creditScore;

if (SQLCODE != 0) break; /* exit loop */

if (creditScore > 700) {

EXEC SQL UPDATE LOAN_APPLICATIONS

SET STATUS = 'APPROVED'

WHERE APPLICATION_ID = :appId;

} else {