Java sql invalid number

stmt.executeUpdate() returns java.sql.SQLException: ORA-01722: invalid number

I have FP_BIOMETRIC_DATA table in oracle whose primary key are CUST_NO and SERIAL_NO . The Blob column is FINGER_DATA . So I want to insert a record in this table . The code is as following :

public void saveFingerToDatabase(String username , String key , byte[] data) < try < connection = DBConnectionHandler.getConnection(); PreparedStatement stmt = connection.prepareStatement("insert into FP_BIOMETRIC_DATA (CUST_NO,SERIAL_NO,FINGER_DATA,KEY_VALUE) values (. )"); stmt.setInt(1, Integer.parseInt(username)); stmt.setInt(2, 1); stmt.setBytes(3, data); stmt.setString(4, key); stmt.executeUpdate(); connection.commit(); >catch (SQLException ex) < Logger.getLogger(SaveDataToDatabase.class.getName()).log(Level.SEVERE, null, ex); >try < connection.close(); >catch (SQLException ex) < Logger.getLogger(SaveDataToDatabase.class.getName()).log(Level.SEVERE, null, ex); >> 

java.sql.SQLException: ORA-01722: invalid number at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368) at com.Futronic.WorkedEx.SaveDataToDatabase.saveFingerToDatabase(SaveDataToDatabase.java:43) at com.Futronic.WorkedEx.DbRecord.Save(DbRecord.java:184) at com.Futronic.WorkedEx.MainForm.OnEnrollmentComplete(MainForm.java:159) at com.futronic.SDKHelper.FutronicEnrollment.run(FutronicEnrollment.java:234) at java.lang.Thread.run(Thread.java:745) 

Источник

I can’t understand the reason behind ORA-01722: invalid number

I have an issue that is generated randomly (one time between thousandth of calls). The error ORA-01722: invalid number is generated in a random way while executing sql update in a prepared statement Oracle database. The case details are as below:

try < connection = getConnection(); statement = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) < if (params[i] instanceof Date) < statement.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime())); >else if (params[i] instanceof java.util.Date) < statement.setTimestamp(i + 1, new Timestamp(((java.util.Date) params[i]).getTime())); >else < statement.setObject(i + 1, params[i]); >paramsBuilder.append(": " + params[i]); > if (logger.isInfoEnabled()) < logger.info("Query String [" + sql + "] [" + paramsBuilder + "]"); logger.info("Query Parameters [" + paramsBuilder + "]"); >result = statement.executeUpdate(); if (logger.isInfoEnabled()) < logger.info(result + " rows affected"); >> catch (SQLException e) < if (logger.isInfoEnabled()) < String message = "Failed to execute SQL statment [" + sql + "] with parameters [" + paramsBuilder + "]"; logger.error(message, e); >throw new DAOException(e); > 

Failed to execute SQL statment [update CUSTOMER_CASE set no_of_ptp=?, no_of_unreached=?,collector_name=? , last_case_status_history_id=?, current_handler=?, handling_start_time=?,due_total_open_amount=?, payment_due_invoice_id =? where with parameters [: 0: 0: auto: 5470508: null: null: 0.0: 23410984: 2476739] java.sql.SQLException: ORA-01722: invalid number 

by tracing the query parameters at DB all parameters are transferred correctly through JDBC driver except for the parameter 23410984 it was replaced by the value «^X* U» (note this value contains carriage return before char ‘u’ !). I don’t know why

What is the type of the object that you’re trying to insert? The toString() returns 23410984, but is it an Integer, Long or BigDecimal?

I faced the same issue with a delete with a IN() clause. Doing a subquery and avoiding putting string values in the query avoid this problem

Usually this happens when we are using IN clause and passing the values as IN (‘1, 2, 3, 4, . ‘) the values should be passed in following way IN (‘1′,’2′,’3′,’4’. )

6 Answers 6

The key reason is about java.sql.SQLException: ORA-01722: invalid number .
May be the field last_case_status_history_id type is number, but your parameter is null

Some times it happens when we are using IN clause and passing the values as IN (‘1, 2, 3, 4’) instead of passing it in following way IN (‘1′,’2′,’3′,’4’)

We had a similar issue. Our hibernate based java code issued a prepared statement to fill in user info and a ‘reason for change’ for all save operations by using aspectJ.

In 3 separate database environments (Oracle 10G) this worked without any issues, but on the production database this sometimes failed with the ORA-01722 error. This only happens when the cpu load on the database server is close to 100%.

On another forum I found a suggestion about not passing along Long objects but doing the unboxing explicitly to a long primitive. It seems the jdbc driver or the database itself has issues when doing this unboxing on high load (even if that sounds pretty crazy). Various jdbc drivers were tested, such as the 1.4 and 1.6 versions.

The code that sometimes failed was:

private void execute(final Long userId, final String rfc) < Object[] args = new Object[]< userId, rfc >; getJdbcTemplate().update("call schema.package.setUserAndRFC(. )", args); > 

Now we changed the code to make an explicit preparedStatement like this:

private void execute(final Long userId, final String rfc) < getJdbcTemplate().update(prepareStatement(userId.longValue(), rfc)); >private PreparedStatementCreator prepareStatement(final long userId, final String rfc) < return new PreparedStatementCreator() < @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException < PreparedStatement statement = con.prepareStatement("call schema.package.setUserAndRFC(. ) "); statement.setLong(1, userId); statement.setString(2, rfc); return statement; >>; > 

This issue has not happened since, even while the environment stayed the same with the same software and high load on the database.

I’ve heard from a colleague that one of the DBA’s could see in the logging that the prepared statement was accepted by the database, but no cpu was assigned to it. (Makes sense since all cpu’s would be busy on such a high load.) It might be that simply the wrong error is thrown, a ‘database overload’ error of some sorts should be thrown or something. Even better would be to never create such an error anyway.

I guess it’s primarily the database load though, it’s not smart at all to let the cpu load reach 100% all the time.

Источник

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

I’m developing an Application , it includes a Registration page and i’m using Oracle database.. I’m connecting back end and front end(html,js and jsp) using jdbc in jsp programs.. Here is my code:

String fname=request.getParameter("f1"); String uid=request.getParameter("f2"); String email=request.getParameter("f3"); Long phnno=Long.parseLong(request.getParameter("f4")); String pwd=request.getParameter("f5"); String face=request.getParameter("f7"); String finger=request.getParameter("f9"); out.println(uid); try < Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","Sindhu","project"); PreparedStatement pst=con.prepareStatement("select * from Shield where uid=?",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); pst.setString(1,uid); ResultSet rs=pst.executeQuery(); boolean f=rs.next(); if(f) < // some html code >else < PreparedStatement pst1=con.prepareStatement("insert into shield values(. )"); pst1.setString(1,fname); pst1.setString(2,uid); pst1.setString(3,email); pst1.setLong(4,phnno); pst1.setString(5,pwd); pst1.setString(6,face); pst1.setString(7,finger); pst1.executeUpdate(); pst1.close(); String TABL = "create table "+uid+" ( " + " site VARCHAR2(30) PRIMARY KEY, u_name VARCHAR2(30) NOT NULL, pass VARCHAR2(30) NOT NULL, " + " url VARCHAR2(30) NOT NULL" + ")"; Statement stmt = con.createStatement(); stmt.executeUpdate(TABL); stmt.close(); >rs.close(); pst.close(); con.close(); > catch(Exception e)

I have to check whether User Id already exists in Db and then if not, have to insert the details . Along with that I have to create a table with the name same as user Id.. Here is the table creation syntax in Oracle:

CREATE TABLE Shield ( f_name varchar2(40), user_id varchar2(40) PRIMARY KEY, email varchar2(40), phone Number(11), pass_id varchar2(40), face varchar2(40), finger varchar2(40) ); 

I’m using TOMCAT and I’m getting an exception: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number I’m getting same exception even when i changed the phone field type to varchar2 . What ever values I try to insert in oracle workspace directly, they are getting inserted and if i try the same in jsp, they are not. thanks in advance, sindhu

Источник

[Solved] Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number in Java and Oracle

Hello guys, if you are getting below error in your Java program and wondering how to solve this or just stuck then you have come to the right place. In this article, I will explain to you what causes the » java.sql.SQLSyntaxErrorException: ORA-01722: invalid number» error when you connect to an Oracle database from a Java program and how you can solve it.

Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number 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 .executeForDescribe(T4CPreparedStatement.java:884) at oracle.jdbc.driver.OracleStatement .executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement .doExecuteWithTimeout(OracleStatement.java:1289) at oracle.jdbc.driver.OraclePreparedStatement .executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement .executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper .executeQuery(OraclePreparedStatementWrapper.java:1493)

I encountered this exception today and the cause of it was very obvious eventually, I learned a very good practice out of it

Below is the query I was trying to execute

select * from Alerts where Id= 60

Surprisingly this query was showing me some results in the PL\ SQL developer tool initially but when I tried to fetch the complete result set it gave me this «ORA-01722: invalid number» exception.

Cause of java.sql.SQLSyntaxErrorException: ORA-01722: invalid number Error?

As you can see the name of the field «Id» gave me a feeling that it is a number field in oracle that is why I had the numeric comparison in the query, but Id was actually declared as a varchar field in the database, and in some records, it had values which could have been converted to number the other records had no numeric values.

When oracle was trying to fetch the data according to a condition in the query, because of comparison with a number oracle was trying to convert values in Id to number.

For few records, it worked but the records having string values of course oracle couldn’t convert those to the number and gave me the «ORA-01722: invalid number» exception.

Solution:

After understanding the problem solution was very simple, I changed the query to have a string comparison like below.

select * from Alerts where Id = '60'

So now oracle will always do the string comparison and will not convert values to numbers.

This is a simple trick but can really help you if you are getting this error.

like to_number(id), or when you use order by clause where it needs to compare column numerically. Like when you try to convert null or empty. Generally, it comes when data is not correct. Precisely it’s not what you expected.

select to_number('') from DUAL; // null - no exception select to_number(null) from DUAL; // null - no exception select to_number('abcd') from DUAL; 

ORA-01722: invalid number
01722. 00000 — «invalid number»

Since this issue comes because of incorrect data, you may see your query working sometimes, but not working all time. This is the classical case of SQL queries working on the test environment but failing in the production environment because of the variety of data.

Anyway, relying on the format of data is not a good thing, it results in fragile code, which can break anytime.

Let’s say if upstream changes the format of data or any other system is allowed to insert data in a database that doesn’t follow that format.

Remember, the data format is something that the database cannot check, your data may be string, not null but what if it’s not in the format you are expecting. So bug will not be caught at the time of data insertion, neither when running queries on other environments, it will be caught most likely on production.

• How to fix java.lang.ClassNotFoundException: org.postgresql.Driver error in Java? [solution]
• java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver [solution]
• How to connect to SQL Server database using Eclipse [solution]
• How to connect to MySQL database from Java Program [steps]
• General Guide to solve java.lang.ClassNotFoundException in Java [guide]
• java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory? [solution]
• How to solve java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver in Java? [solution]
• The java.sql.BatchUpdateException: String or binary data would be truncated [solution]
• Java guide to connect Oracle 10g database using JDBC thin driver (guide)
• Solving java.lang.classnotfoundexception sun.jdbc.odbc.jdbcodbcdriver [solution]
• How to solve java.lang.ClassNotFoundException:org.Springframework.Web.Context.ContextLoaderListener [solution]
• Fixing java.lang.ClassNotFoundException: org.postgresql.Driver [solution]
• Solving java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver [solution]
• Dealing with java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [fix]
• How to solve java.lang.ClassNotFoundException: com.mysql.jdbc.Driver in Java MySQL? [solution]

Источник