CODEWITHSUNDEEP
hibernatespring-bootjpaspring-data-jpahql
Happy Coder
Happy Coder

Reputation: 1423

JPA @Query select on oracle throws ORA-01722: invalid number on string

I have the following query that works perfectly with ms-SQL but failing with oracle

there is sample query, the original is way complicated but this one reproduces the error 

@Query("SELECT new Map((s.name + ' - ' + s.name) as name , s.idStandard as idStandard) FROM Standard s ")

jpa model

public class Standard
{


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    private int idStandard;

    @Column(name = "Name")
    @NonNull
    private String name;

    @Column(name = "Description")
    private String description;

    @Column(name = "UpdateUser")
    private String updateUser;


    @Column(name = "UpdateDT")
        private Date updateDt;
}

why oracle thinks this is (s.name + ' - ' + s.name) is a number ?

Upvotes: 1

Views: 570

Answers (1)

Happy Coder
Happy Coder

Reputation: 1423

I think the world deserves to know :) using concatenate operator || solves the problem!! concatenate operator allows you to concatenate 2 or more strings together.

see the doc for more info https://www.techonthenet.com/oracle/functions/concat2.php

updating the query runs on ms SQL and oracle 

@Query("SELECT new Map((s.name || ' - ' || s.name) as name , s.idStandard as idStandard) FROM Standard s ")

Upvotes: 1

Related Questions