1

I am trying to use oracle' NVL function in nativeQuery of Spring Data Repository.

While i am passing null value in programId parameter then it is throwing exception (ORA-00932: inconsistent datatypes: expected NUMBER got BINARY) and if i am passing a valid value in "programId" then it is working properly.

public interface ProgramRulesRepository
{
  public static final String FIND_PROGRAM_RULES_BY_PARTICIPANT_ID_AND_ROLE_OR_PROGRAM = " SELECT DISTINCT pr.id , pr.program_id , prgm.display_name , pr.group_id , pr.type , pr.cmmo_key FROM  program prgm , program_rule pr , program_audience pa , participant_audience paa WHERE prgm.id = pa.program_id AND pr.program_id = pa.program_id AND pa.audience_id = paa.audience_id AND pr.type = :roleType  AND paa.participant_id = :participantId "
      + " AND pr.program_id = NVL ( :programId ,pr.program_id )";

  @Query( value = FIND_PROGRAM_RULES_BY_PARTICIPANT_ID_AND_ROLE_OR_PROGRAM, nativeQuery = true )
List<Object[]> findByParticipantIdAndRoleTypeOrProgramId( @Param( "participantId" ) Long participantId, @Param( "roleType" ) String roleType, @Param( "programId" ) Long programId );


}

Exception :

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
Zico
  • 2,349
  • 2
  • 22
  • 25
Prabhu Patel
  • 69
  • 1
  • 2
  • 4
  • Possible duplicate of [Spring Data - ignore parameter if it has a null value](https://stackoverflow.com/questions/43780226/spring-data-ignore-parameter-if-it-has-a-null-value) – araknoid Oct 17 '17 at 12:06

3 Answers3

2

Avoid NVL and COALESCE when using Hibernate. COALESCE function needs to have all parameters of the same type. NVL is using implicit casting which doesn't work well when there is BINARY or VARBINARY. And where this BINARY came from? Well, Hibernate is setting NULL value as type of BINARY and ignores the real datatype backed by Java. When you set logging level to trace you can see in output:

binding parameter [1] as [VARBINARY] - [null]

So when the other type of in COALESCE or NVL function is for example NUMBER, you will get that error ORA-00932.

Shaolin
  • 154
  • 10
2

A good solution for this problem is this:

" AND (:programId IS NULL OR pr.program_id = :programId)"

Doing this way, if your param is null this sentence will result TRUE and won't discard the register, and if it is not null will be compared with the value stored in its field.

juldeh
  • 129
  • 13
0

I have faced this problem with MongoDB. But I could solve this problem by using mongoTemplate as like,

 Query query = new Query();
 Criteria criteria = new Criteria();
 List<Criteria> orCriterias = new ArrayList<>();
 if( dto.getId() != null) {    
   orCriterias.add(Criteria.where("id").is(Integer.parseInt(dto.getId())));
 }
 ... so on for other fields
criteria.orOperator(orCriterias.toArray(new Criteria[orCriterias.size()]));
query.addCriteria(criteria);
List<StudentDTO> recordsList = mongoTemplate.find(query, StudentDTO.class, 
"student_collection");
WGSSAMINTHA
  • 180
  • 1
  • 11
  • I Ignored null parameters by applying condition and add Criteria to Criteria list. Then applied operators like "orOperators", " andOperators" as document https://docs.spring.io/spring-data/mongodb/docs/2.0.8.RELEASE/reference/html/#mongo.aggregation.supported-aggregation-operations. Then add that Criteria list to Query. – WGSSAMINTHA Sep 06 '18 at 07:30
  • This thing is also discussing in spring jira "https://jira.spring.io/browse/DATAJPA-209". Please refer that too to get idea. – WGSSAMINTHA Sep 07 '18 at 03:06