问题描述:

Given the following Oracle SQL stored procedure signature:

PROCEDURE Update_Desactivation_Date(

code_util IN NUMBER,

t_no_piste IN VARCHAR2,

date_depart IN DATE);

Called from a Java JPA2 Hibernate context using the following code:

entityManager.createNativeQuery("CALL Update_Desactivation_Date(

+ ":code_util, "

+ ":t_no_piste, "

+ ":date_depart"

+ ")")

.setParameter("code_util", 30320)

.setParameter("t_no_piste", "087046")

.setParameter("date_depart", null)

.executeUpdate();

The execution yields the following exception:

WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: 6553, SQLState: 65000

ERROR: org.hibernate.util.JDBCExceptionReporter - ORA-06553: PLS-306: wrong number or types of arguments in call to 'UPDATE_DESACTIVATION_DATE'

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)

at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1224)

at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:108)

at com.sw.chuv.badges.backend.dao.ExecuteUpdateInProcedure.main(ExecuteUpdateInProcedure.java:25)

Caused by: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)

at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)

at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:396)

at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:188)

at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)

... 1 more

Caused by: java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'UPDATE_DESACTIVATION_DATE'

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233)

at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)

... 5 more

How comes, since all parameters are set correctly ?

网友答案:

The null value for the date parameter is the culprit. You have to send an empty string:

entityManager.createNativeQuery("CALL Update_Desactivation_Date(
        + ":code_util, "
        + ":t_no_piste, "
        + ":date_depart"
        + ")")
    .setParameter("code_util", 30320)
    .setParameter("t_no_piste", "087046")
    .setParameter("date_depart", "")
    .executeUpdate();

This issue appears with all temporal types (DATE, TIME, TIMESTAMP)

网友答案:

An other possibility is to define a default parameter in your SQL procedure:

PROCEDURE Update_Desactivation_Date(
      code_util              IN NUMBER,
      t_no_piste             IN VARCHAR2,
      date_depart            IN DATE DEFAULT NULL);
相关阅读:
Top