Call a Stored Procedure from a Oracle Package in SpringBoot Application

 

To create a Spring Boot application that calls an Oracle stored procedure `STORED_PRC_NAME` in a package `PACKAGE_NAME`, follow these steps:

 

1. Set Up Your Spring Boot Project: Use Spring Initializr or your IDE to create a new Spring Boot project with the necessary dependencies.

 

 Step 1: Create the Project

 

1. Initialize a Spring Boot project: Include the following dependencies:

   - Spring Web

   - Spring Data JPA

   - Oracle JDBC

   - Spring Boot Starter JDBC

 

2. Project Structure: Ensure your project has the following structure:

 

   src/main/java/com/example/oracleprocedure

   ├── OracleProcedureApplication.java

   ├── config

   │   └── DataSourceConfig.java

   ├── repository

   │   └── OracleRepository.java

   └── service

       └── OracleService.java

 

 

 Step 2: Configure Oracle DataSource

 

Create a configuration class `DataSourceConfig.java` to set up the Oracle DataSource.

 

JAVA CODE:

package com.example.oracleprocedure.config;

 

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 

import javax.sql.DataSource;

import java.sql.SQLException;

 

@Configuration

public class DataSourceConfig {

 

    @Bean

    public DataSource dataSource() throws SQLException {

        OracleDataSource dataSource = new OracleDataSource();

        dataSource.setURL("jdbc:oracle:thin:@//your_db_url:1521/your_service_name");

        dataSource.setUser("your_username");

        dataSource.setPassword("your_password");

        return dataSource;

    }

 

    @Bean

    public JdbcTemplate jdbcTemplate(DataSource dataSource) {

        return new JdbcTemplate(dataSource);

    }

 

    @Bean

    public DataSourceTransactionManager transactionManager(DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

}

```

 

 Step 3: Create a Repository to Call the Stored Procedure

 

Create a repository class `OracleRepository.java` to handle the stored procedure call.

 

JAVA CODE:

package com.example.oracleprocedure.repository;

 

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

 

import javax.annotation.PostConstruct;

import javax.sql.DataSource;

import java.sql.Types;

import org.springframework.jdbc.core.SqlOutParameter;

import org.springframework.jdbc.core.SqlParameter;

import org.springframework.jdbc.core.simple.SimpleJdbcCall;

 

@Repository

public class OracleRepository {

 

    private final JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCall;

 

    public OracleRepository(JdbcTemplate jdbcTemplate) {

        this.jdbcTemplate = jdbcTemplate;

    }

 

    @PostConstruct

    public void init() {

        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)

            .withCatalogName("PACKAGE_NAME") // Package name

            .withProcedureName("STORED_PRC_NAME")    // Procedure name

            .declareParameters(

                new SqlParameter("input_param1", Types.VARCHAR),

                new SqlParameter("input_param2", Types.INTEGER),

                new SqlOutParameter("output_param", Types.VARCHAR)

            );

    }

 

    public String callStoredProcedure(String param1, int param2) {

        Map<String, Object> inParams = new HashMap<>();

        inParams.put("input_param1", param1);

        inParams.put("input_param2", param2);

 

        Map<String, Object> outParams = simpleJdbcCall.execute(inParams);

        return (String) outParams.get("output_param");

    }

}

```

 

 Step 4: Create a Service to Use the Repository

 

Create a service class `OracleService.java` to use the repository.

 

JAVA CODE:

package com.example.oracleprocedure.service;

 

import com.example.oracleprocedure.repository.OracleRepository;

import org.springframework.stereotype.Service;

 

@Service

public class OracleService {

 

    private final OracleRepository oracleRepository;

 

    public OracleService(OracleRepository oracleRepository) {

        this.oracleRepository = oracleRepository;

    }

 

    public String callStoredProcedure(String param1, int param2) {

        return oracleRepository.callStoredProcedure(param1, param2);

    }

}

 

 

 Step 5: Main Application Class

 

Create the main application class `OracleProcedureApplication.java`.

 

JAVA CODE:

package com.example.oracleprocedure;

 

import com.example.oracleprocedure.service.OracleService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

 

@SpringBootApplication

public class OracleProcedureApplication implements CommandLineRunner {

 

    @Autowired

    private OracleService oracleService;

 

    public static void main(String[] args) {

        SpringApplication.run(OracleProcedureApplication.class, args);

    }

 

    @Override

    public void run(String... args) throws Exception {

        String result = oracleService.callStoredProcedure("testParam1", 123);

        System.out.println("Stored Procedure Output: " + result);

    }

}

 

 

 Step 6: Add Dependencies in `pom.xml`

 

Here is the complete `pom.xml` for the project:

 

XML Code

<project xmlns="http://maven.apache.org/POM/4.0.0"

         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

 

    <groupId>com.example</groupId>

    <artifactId>oracleprocedure</artifactId>

    <version>0.0.1-SNAPSHOT</version>

    <packaging>jar</packaging>

 

    <name>oracle-procedure</name>

    <description>Spring Boot project to call an Oracle stored procedure</description>

 

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>3.0.0</version>

        <relativePath/> <!-- lookup parent from repository -->

    </parent>

 

    <properties>

        <java.version>17</java.version>

    </properties>

 

    <dependencies>

        <!-- Spring Boot Starter Web -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

 

        <!-- Spring Boot Starter Data JPA -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-jpa</artifactId>

        </dependency>

 

        <!-- Spring Boot Starter JDBC -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-jdbc</artifactId>

        </dependency>

 

        <!-- Oracle JDBC -->

        <dependency>

            <groupId>com.oracle.database.jdbc</groupId>

            <artifactId>ojdbc8</artifactId>

            <version>19.8.0.0</version>

        </dependency>

 

        <!-- Spring Boot Starter Test -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

    </dependencies>

 

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

        </plugins>

    </build>

 

</project>

```

 

 Step 7: Run Your Application

 

Run your Spring Boot application. It should call the Oracle stored procedure `STORED_PRC_NAME` and print the result.

 

Make sure your Oracle database is properly set up and accessible from your application. Also, adjust the stored procedure call parameters according to your actual stored procedure's input and output parameters.