Hibernate invoking Stored Procedure returns not a valid function or procedure name error

Question

This is my procedure I am trying to invoke in Hibernate.

PROCEDURE CSR_GET_BUSINS_UNIT_EXP(
                      v_start_year VARCHAR2,
                      v_end_year VARCHAR2,
                      c_bus_total OUT cursor_output,
                      c_bus_month_total OUT cursor_output,
                      c_bus_unit_info OUT cursor_output,
                      c_bus_exp_info OUT cursor_output
                      ) AS
  BEGIN
        OPEN c_bus_total FOR
            SELECT NVL(SUM(amount),0)
            FROM TIPS_BUS_UNIT
            WHERE ACCT_PER <= TO_DATE(v_start_year,'dd-mon-yyyy')
            AND ACCT_PER >= TO_DATE(v_end_year,'dd-mon-yyyy');
        OPEN c_bus_month_total FOR
           SELECT TO_CHAR(acct_per,'MON-YYYY'), NVL(SUM(amount),0)
              FROM TIPS_BUS_UNIT
              WHERE ACCT_PER <= TO_DATE(v_start_year,'dd-mon-yyyy')
              AND ACCT_PER >= TO_DATE(v_end_year,'dd-mon-yyyy')
              GROUP BY acct_per
              ORDER BY acct_per DESC;
       OPEN c_bus_unit_info FOR
           SELECT TIPS_BUNIT, NVL(SUM(amount),0)
              FROM TIPS_BUS_UNIT
              WHERE ACCT_PER <= TO_DATE(v_start_year,'dd-mon-yyyy')
              AND ACCT_PER >= TO_DATE(v_end_year,'dd-mon-yyyy')
              GROUP BY TIPS_BUNIT
              ORDER BY TIPS_BUNIT DESC;
       OPEN c_bus_exp_info FOR
              SELECT TIPS_BUNIT, TO_CHAR(acct_per,'MON-YYYY'), amount
           FROM TIPS_BUS_UNIT
           WHERE ACCT_PER <= TO_DATE(v_start_year,'dd-mon-yyyy')
           AND ACCT_PER >= TO_DATE(v_end_year,'dd-mon-yyyy')
           ORDER BY TIPS_BUNIT DESC, ACCT_PER DESC;
   END;

And below is my POJO where i have defined my procedure in.setters and getters added.

@NamedNativeQueries({`@NamedNativeQuery(
        name = "callBusinessUnitProcedure",`enter code here`
        query = "CALL CSR_GET_BUSINS_UNIT_EXP(:v_start_year,:v_end_year)",
        resultClass = Company.class`)
    })
    @Entity
    @Table(name="TIPS_BUS_UNIT",schema ="CSR")
    public class Company implements Serializable{
    @Column(name = "TIPS_BUNIT")
        @Id
        @JsonProperty(value="businessUnit")
        private String businessUnit;

        @Column(name = "BUS_UNIT_DESC")
        @JsonProperty(value="description")
        private String description;

        @Column(name = "ACCT_PER")
        @JsonProperty(value="date")
        private String date;

        @Column(name = "AMOUNT")
        @JsonProperty(value="amount")
        private String amount;

The below is my IMPL class where I am invoking the stored procedure to hit the data base.

 @SuppressWarnings("unchecked")
        @Override
        public  List<Company> getWebReports()
        {

            List<Company> webReport = new ArrayList<Company>();


            webReport =  entityManager
                    .createNamedQuery("callBusinessUnitProcedure").setParameter("v_start_year","07-OCT-2007")
                    .setParameter("v_end_year", "06-JAN-2006").getResultList();
             return webReport;

And when I run this I am getting the error below in my console. Please help if anyone else is facing this kind of problem...

2016-08-30 21:59:13.516  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 56 ms
Hibernate: CALL CSR_GET_BUSINS_UNIT_EXP(?,?)
2016-08-30 21:59:15.199  WARN 11884 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 6576, SQLState: 65000
2016-08-30 21:59:15.199 ERROR 11884 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-06576: not a valid function or procedure name

2016-08-30 21:59:15.269 ERROR 11884 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[`dispatcherServlet`]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract Result Set; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract Result Set] with root cause

java.sql.SQLException: ORA-06576: not a valid function or procedure name

This sounds like an awfully complicated stored procedure to begin with. Why don't you use Java code to convert strings to SQL compatible time points to begin with, instead of letting Oracle do this? Also, you only ever open cursors in the stored procedure but never exploit them?? — fge, Aug 31 '16 at 15:32

score 0 · Answer 1 · edited Jan 30 '23 at 21:05

0

Change your namednative query to have {} on it

@NamedNativeQueries({`@NamedNativeQuery(
    name = "callBusinessUnitProcedure",`enter code here`
    query = "{CALL CSR_GET_BUSINS_UNIT_EXP(:v_start_year,:v_end_year)}",
    resultClass = Company.class`)
})

Check this https://stackoverflow.com/a/26381638/258741

edited Jan 30 '23 at 21:05

Sven Eberth

3,057
12
24
29

answered Aug 31 '16 at 16:24

Zeus

6,386
6
54
89

Hibernate invoking Stored Procedure returns not a valid function or procedure name error

1 Answers1