How to call oracle stored procedure from specifically spring boot using jdbc

Viewed 780

I've a spring boot application which is supposed to call an oracle stored procedure but when I send a request it returns 200 Ok with no payload returned. here is my code on how I called the oracle stored procedure.

    #application.properties file
    
    server.port=3000
    spring.datasource.url=jdbc:oracle:thin:@xxxxxxxxx
    #thin is popular oracle driver, localhost is the host of the database, 1521 is the port of the database, and xe is the database name
    spring.datasource.username=XXXXXX
    spring.datasource.password=XXXXXX
    spring.datasource.driver-class-name= oracle.jdbc.OracleDriver
    spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
    spring.jpa.show-sql=true
    spring.jpa.hibernate.ddl-auto=none
    spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    spring.jpa.hibernate.naming.implicit-strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
    spring.jpa.properties.hibernate.proc.param_null_passing=true

#my repo class to call the stored procedure
package com.amsadmacc.amsadmaccadapter.model;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Data
@Entity
@NoArgsConstructor
@Builder
@AllArgsConstructor
@NamedStoredProcedureQuery(
        name = "test_stored_proc_sp",
        procedureName = "Test_stored_proc"        
)
public class PathwaysJourney implements Serializable  {
        @Id
        private long id;
        private Integer pidm;
        private String firstName;
        private String lastName;
        private Integer termCode;
        private String termDescription;
        private Integer applicationNumber;
        private String applicationStatusCode;
        private String applicationStatusDescription;
        private String applicationProgram;
        private String majorCode;
        private String majorDescription;
        private Date applicationDate;
        private Integer daysFromApplication;
        private String email;
        private String mobileNumber;
}

#my controller

   @PostMapping("/pathwaysjourney1")
   @ResponseBody
   public List getAllPathways1() {
       spridenRepo.serverOut();
       StoredProcedureQuery proc = this.em.createNamedStoredProcedureQuery("Test_stored_proc");
       System.out.println("===>>> start exec");
       //String output=serverOut();
       //log.info("Output {}",output);
       proc.execute();
       System.out.println("===>>> end exec");

       return proc.getResultList();
   }

The above end point in the controller returns an empty string like [] in the response body, I've tested the stored procedure in oracle sql developer it returns data.

Any Idea what the problem is? ,some say it is the " set serveroutput on" command, it should be turned on every time a call is made from spring boot, if so, how do we run that command from spring boot whenever the call is made?

0 Answers
Related