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?