0

I've spring boot application that calls oracle stored procedures when I run it it gives me a null output then I found out that first set serveroutput on command should be run for oracle to display the result for the stored procedure.

How can i run set serveroutput on in spring boot before executing the stored procedures.

here is my code to executer the stored procedure.

package com.dmacc.amsaadapterdmacc.model;

import lombok.Data;

import javax.persistence.\*;
import java.util.Date;

@Data
@Entity
@NamedStoredProcedureQuery(name = "pathwaysjourney_sp", procedureName = "AMSA_Marketing_Cloud.pathways_journey")
public class PathwaysJourney {

    @Id
    private long id;
    private Integer pidm;
    private String bannerID;
    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;

`}`

`package com.dmacc.amsaadapterdmacc.dao;`

`import com.dmacc.amsaadapterdmacc.model.PathwaysJourney;`
`import org.springframework.beans.factory.annotation.Autowired;`
`import org.springframework.data.jpa.repository.Query;`
`import org.springframework.stereotype.Repository;`

`import javax.persistence.EntityManager;`
`import java.util.List;`

`@Repository`
`public class PathwaysJourneyDao {`

    @Autowired
    private EntityManager em;
    
    @SuppressWarnings("unchecked")

`public List<PathwaysJourney> getPathwayJourney(){`
`return em.createNamedStoredProcedureQuery("pathwaysjourney_sp").getResultList();`
`}`
`}`

I couldn't find any clue how to solve this

Befekadu Asmare
  • 11
  • 3
  • Try looking at questions about getting DBMS_OUTPUT lines into Java programs. [Here's one](https://stackoverflow.com/questions/47830370/getting-output-from-dbms-output-get-lines-using-jdbc/47831073#47831073) using dbms_output.get_lines, and [here's a Spring/Hibernate](https://stackoverflow.com/questions/47138041/print-dbms-output-put-line-with-hibernate-or-spring) solution. – kfinity Apr 01 '22 at 13:33
  • You may be better off with some kind of logging procedure that logs messages to a db table instead of relying on dbms_output. – Martin Schapendonk Apr 01 '22 at 14:04
  • What led you to believe that `set serveroutput on` was required? That's a sqlplus/sqlcl command, not a SQL command. DBMS_OUTPUT only writes messages using its `PUT_LINE` function that must then be read with DBMS_OUTPUT.GET_LINE or dumped to the console (if serveroutput is "on"), otherwise the messages generated are ignored. This feature is usually only used for debugging; literally no one designs procedures to return actual data this way. – pmdba Apr 01 '22 at 18:41

0 Answers0