Invalid Number Exception in SQL when trying to call select statement from Java

Question

I am trying to call a select query from my Java Program but i am getting Invalid Numbererror. I am trying to call the query something like this

code

 String[] l_arr = new String[]{"CUSTOMERCUMULATIVELIMIT"};

    l_rs = JDBCEngine.executeQuery(MessageFormat.format(
                        " SELECT  LIMITS,  P.PACKAGE_ID  FROM MSTGLOBALLIMITSPACKAGE P  ,MSTCUSTOMERPROFILE C  WHERE P.PACKAGE_ID = {0}   AND C.ID_CUST = ?    AND C.ID_ENTITY = ?  AND C.TYPEUSER = ?  AND C.ID_ENTITY = P.ID_ENTITY  AND P.PACKAGE_ID NOT LIKE ?",l_arr );

I am getting query something like this:

Result

SELECT LIMITS, P.PACKAGE_ID
  FROM MSTGLOBALLIMITSPACKAGE P, MSTCUSTOMERPROFILE C
 WHERE P.PACKAGE_ID = CUSTOMERCUMULATIVELIMIT
   AND C.ID_CUST = '00000144'
   AND C.ID_ENTITY = 'B001'
   AND C.TYPEUSER = 'ECU'
   AND C.ID_ENTITY = P.ID_ENTITY
   AND P.PACKAGE_ID NOT LIKE '*%'

I am getting the error Invalid number in the line P.PACKAGE_ID = {0}. I gone through and come to know that if i use to_char i can avoid this error. But I am not sure how to use to_char in P.PACKAGE_ID = {0}.

Also PACKAGE_ID is of type Varchar2 and CUSTOMERCUMULATIVELIMIT is a column in MSTCUSTOMERPROFILE of type Number.

Any help or suggestion will be appreciated. Thanks.

Is Package_ID a varchar or char type in your table schema? Similarly, how about other columns? — Namandeep_Kaur, Aug 24 '20 at 05:54
why do you use String.format instead of a parameter ? And which value you pass in it in place of {0}? — Massimo Petrus, Aug 24 '20 at 05:58
@Saurav, can you share the exact error? Not sure but you may want to consider this for the pattern matching at the end of your query: https://stackoverflow.com/a/189955/5199194 — Namandeep_Kaur, Aug 24 '20 at 06:16
Now its clear why you use message.format, it's because you want to make a dynaic join, right ? Are you sure the error is in the P.PACKAGE_ID = CUSTOMERCUMULATIVELIMIT clause ? Oracle should translate the numbers into strings automatically during query execution. See this fiddle http://sqlfiddle.com/#!4/53182/1. What are the data types of the other join fields ? Can you hare your ddl (or at least the relevant part of it ) ? — Massimo Petrus, Aug 24 '20 at 07:12
Why are you comparing strings with numbers? At least one of those two columns has the wrong datatype. — , Aug 24 '20 at 08:01
Why are you choosing to use commas in the `FROM` clause rather than proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Aug 24 '20 at 11:26

andy · Accepted Answer · 2020-08-24T08:02:33.410

1

just add to_char to your sql, change {0} to to_char({0})

l_rs = JDBCEngine.executeQuery(MessageFormat.format( " SELECT LIMITS, P.PACKAGE_ID FROM MSTGLOBALLIMITSPACKAGE P ,MSTCUSTOMERPROFILE C WHERE P.PACKAGE_ID = to_char({0}) AND C.ID_CUST = ? AND C.ID_ENTITY = ? AND C.TYPEUSER = ? AND C.ID_ENTITY = P.ID_ENTITY AND P.PACKAGE_ID NOT LIKE ?",l_arr );

edited Aug 24 '20 at 08:02

answered Aug 24 '20 at 07:57

andy

1,336
9
23

score 0 · Answer 2 · answered Aug 24 '20 at 06:53

0

You are working with this line :

WHERE P.PACKAGE_ID = CUSTOMERCUMULATIVELIMIT

As there are no single quotes, SQL consider CUSTOMERCUMULATIVELIMIT as a number, not a string.

Adding quotes should solve this.

l_rs = JDBCEngine.executeQuery(MessageFormat.format(
                        " SELECT  LIMITS,  P.PACKAGE_ID  FROM MSTGLOBALLIMITSPACKAGE P  ,MSTCUSTOMERPROFILE C  WHERE P.PACKAGE_ID = '{0}'   AND C.ID_CUST = ?    AND C.ID_ENTITY = ?  AND C.TYPEUSER = ?  AND C.ID_ENTITY = P.ID_ENTITY  AND P.PACKAGE_ID NOT LIKE ?",l_arr );

answered Aug 24 '20 at 06:53

BenjaminD

488
3
13

CUSTOMERCUMULATIVELIMIT is a column of another table, not a string. – andy Aug 24 '20 at 07:54
Right. But as said, PACKAGE_ID is varchar2 and CUSTOMERCUMULATIVELIMIT is number. You can't match on different types. So, problem. – BenjaminD Aug 24 '20 at 07:59

Invalid Number Exception in SQL when trying to call select statement from Java

2 Answers2