How to execute Storedproc along side JPA - Springboot 2.0

Define Data sources in application.yaml

app:  datasource:     oracle-local:        url: jdbc:oracle:thin:@localhost:10152/test
        username: wrkday_apps
        password: bread4all
        driver-class: oracle.jdbc.driver.OracleDriver
     postgres-local:        url: jdbc:postgresql://localhost:5432/
        username: postgres
        password: postgres
        driver-class: org.postgresql.Driver

crate a config file

@Configurationpublic class DatasourceConfig {

    @Bean    @Primary    @ConfigurationProperties("app.datasource.oracle-local")
    public DataSourceProperties oracleDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean    @Primary    @ConfigurationProperties("app.datasource.oracle-local")
    public DataSource oracleDataSource() {
        return oracleDataSourceProperties().initializeDataSourceBuilder().build();
    }


    @Bean(name = "oracleEntityManagerFactory")
    @Primary    public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(oracleDataSource())
                .packages(Audit.class, Audit1.class)
                .persistenceUnit("local1")
                .build();
    }


    @Configuration    @EnableJpaRepositories(basePackages= "com.jlabs.repo",
            entityManagerFactoryRef = "oracleEntityManagerFactory")
    public class oracleConfiguration {
    }




    @Bean    @ConfigurationProperties("app.datasource.postgres-local")
    public DataSourceProperties localDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "localDataSource")
    @ConfigurationProperties("app.datasource.postgres-local")
    public DataSource localDataSource() {
        return localDataSourceProperties().initializeDataSourceBuilder().build();
    }


    @Bean(name = "jdbcMaster")
    public SimpleJdbcCall masterJdbcTemplate() {
        return new SimpleJdbcCall( localDataSource()) .withSchemaName("public")
                .withFunctionName("test_pkg");
    }
}


to Execute the store proc

@Componentpublic class LocalTestDAO {

    private static Logger LOGGER = LoggerFactory.getLogger(LocalTestDAO.class);

    @Autowired    public SimpleJdbcCall jdbcMaster;

    @Transactional    public Integer savetoLocalTestPkg() {
        LOGGER.info("Saving to TestPkg {}" );
        try {


            final Map<String, Object> params = new HashMap<>();
            params.put("_sno", 8);
            params.put("_eid", 104);
            params.put("_ename", "jim");
            params.put("_sd", new Date());
            params.put("_ed", new Date());
            params.put("_sid", 150000);
            Map<String, Object> out = jdbcMaster.execute(params);


            return 1;
        } catch (Exception e) {
            LOGGER.error("Saving to EDW TestPkg failed {}", e);
            return -1;
        }
    }

}



Steps to create the stored proc

CREATE TABLE app_for_leave
(
  sno INTEGER NOT NULL,
  eid INTEGER,
  ename VARCHAR(20),
  sd DATE,
  ed DATE,
  sid INTEGER,
  status BOOLEAN DEFAULT FALSE,
  CONSTRAINT pk_snoa PRIMARY KEY (sno)
);


CREATE OR REPLACE FUNCTION insert_into_table(_sno INTEGER, _eid INTEGER, _ename VARCHAR(20), _sd DATE, _ed DATE, _sid INTEGER)
RETURNS void AS
$BODY$
BEGIN
 INSERT INTO app_for_leave(sno, eid, ename, sd, ed, sid)
  VALUES(_sno, _eid, _ename, _sd, _ed, _sid);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
 COST 100;


To manually execure the proc - 
SELECT * FROM insert_into_table(2,102,'Jimmy','2013-08-16','2013-08-17',12);