We're updating the issue view to help you get more done. 

Allow propagation of NULL for stored-procedure argument parameters to database

Description

This is allowed at 2 levels...

First, this can be controlled at a "global" level (via the new hibernate.proc.param_null_passing setting, see org.hibernate.cfg.AvailableSettings#PROCEDURE_NULL_PARAM_PASSING) when building the SessionFactory.

Additionally this can be adjusted per-parameter, per-execution. In the native Hibernate API this is done through org.hibernate.procedure.ParameterRegistration#enablePassingNulls. The default for this per-parameter is defined by the "global" setting. In JPA, this is done via hints; in the initial implementation this is only supported through named queries.


Original Description

I've this Exception when pass a null as an In Paramter to a storedprocedure:

1 2 3 4 5 6 7 8 Caused by: java.sql.SQLException: Falta el parámetro IN o OUT en el índice:: 11 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2244) [ojdbc7.jar:12.1.0.2.0] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4790) [ojdbc7.jar:12.1.0.2.0] at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) [ojdbc7.jar:12.1.0.2.0] at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631) [ojdbc7.jar:12.1.0.2.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) [ojdbc7.jar:12.1.0.2.0] at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404) at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:69) [hibernate-core-4.3.7.Final.jar:4.3.7.Final]

I see this is this file

1 org.hibernate.procedure.internal.AbstraAbstractParameterRegistrationImpl::prepare (línea 276-293 hibernate-core-4.3.7.Final)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 if ( mode == ParameterMode.INOUT || mode == ParameterMode.IN ) { if ( bind == null || bind.getValue() == null ) { // the user did not bind a value to the parameter being processed. That might be ok *if* the // procedure as defined in the database defines a default value for that parameter. // Unfortunately there is not a way to reliably know through JDBC metadata whether a procedure // parameter defines a default value. So we simply allow the procedure execution to happen // assuming that the database will complain appropriately if not setting the given parameter // bind value is an error. log.debugf( "Stored procedure [%s] IN/INOUT parameter [%s] not bound; assuming procedure defines default value", procedureCall.getProcedureName(), this ); } else { typeToUse.nullSafeSet( statement, bind.getValue(), startIndex, session() ); } }

I've try to set a default value but not luck. I think that It can be remove the if and use

1 typeToUse.nullSafeSet( statement, bind.getValue(), startIndex, session() );

Because in

1 org.hibernate.type.descriptor.sql.BasicBinder::bind (línea 66 hibernate-core-4.3.7.Final)

it has this null protection:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 @Override public final void bind(PreparedStatement st, J value, int index, WrapperOptions options) throws SQLException { final boolean traceEnabled = log.isTraceEnabled(); if ( value == null ) { if ( traceEnabled ) { log.trace( String.format( NULL_BIND_MSG_TEMPLATE, index, JdbcTypeNameMapper.getTypeName( getSqlDescriptor().getSqlType() ) ) ); } st.setNull( index, sqlDescriptor.getSqlType() ); } else { if ( traceEnabled ) { log.trace( String.format( BIND_MSG_TEMPLATE, index, JdbcTypeNameMapper.getTypeName( sqlDescriptor.getSqlType() ), getJavaDescriptor().extractLoggableRepresentation( value ) ) ); } doBind( st, value, index, options ); } }

Environment

None

Status

Assignee

Steve Ebersole

Reporter

Marcial Atiénzar Navarro

Labels

Worked in

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Community Help Wanted

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Components

Fix versions

Priority

Major