Oracle Error 1722 Java.sql.sqlsyntaxerrorexception
17, 2012 - 9:21 am UTC Category: � Version: Whilst you are here, check out some content from the AskTom team: EXCHANGE PARTITION revisited Latest Followup You Asked What is the error ORA-01722 and we said... ORA-1722 is Invalid number. We've attempted to either explicity or implicity convert a character string to a number and it is failing. This can happen for a number of reasons. It generally happens in SQL only (during a query) not in plsql (plsql throws a different exception for this error). You can see this error easily by: ops$tkyte@8i> select to_number('abc') from dual; select to_number('abc') from dual * ERROR at line 1: ORA-01722: invalid number This error seems to creep into queries in the strangest ways. A change in the order of a predicate can make it come and go -- depending on the order of evaluation in the predicate. Consider this example: ops$tkyte@8i> create table t ( x int, y varchar2(25) ); Table created. ops$tkyte@8i> ops$tkyte@8i> insert into t values ( 1, 'abc' ); 1 row created. ops$tkyte@8i> insert into t values ( 2, '123' ); 1 row created. ops$tkyte@8i> ops$tkyte@8i> ops$tkyte@8i> select * from t where y > 100 and x = 2; X Y ---------- ------------------------- 2 123 ops$tkyte@8i> select * from t where x = 2 and y > 100; select * from t where x = 2 and y > 100 * ERROR at line 1: ORA-01722: invalid number The first query worked since we *tend* to evaluate queries from the bottom up. We evaluated the x=2 part first and never tried to do 'abc' > 100. In the second query, the y>100 was evaluated first. Y was promoted to a number and then compared to 100. 'abc' could not be converted so ORA-1722. The only general purpose solution is to always compare like types to like types. You should either convert the column Y entirely to numbers (clean the data) or use a character string comparision (which changes the meaning of the predicate -- y > 100 is very different from y > '100' ) Reviews Write a Review ORa-01722 March 27, 2001 - 2:30 pm UTC Reviewer: Tom Petrella from Melville, NY I was getting this error and it was driving me nuts because I know everything was syntactically correct and there were no invalid numbers. Be rearranging the order of the where clause i got it to work. Thanks ! Is there a surefire way to avoid this? July 11, 2002 - 10:35 am UTC Reviewer: Adrian from Exeter England Apart from the obvious method, (i.e. Always compare like data-types), is there a sure-fire way to avoid this sort of problem happening? If I have a domain table cg_ref_codes with fields domain, low_value, high_value, abbreviation,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45012348053 minute: Sign up I can't understand the reason behind ORA-01722: invalid number up vote 4 down vote favorite 1 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 { http://stackoverflow.com/questions/10636114/i-cant-understand-the-reason-behind-ora-01722-invalid-number 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); } and the value in log is like that : 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 id=?] 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 f
This Site Careers Other all forums Forum: JDBC and Relational Databases java.sql.SQLException: ORA-01722: invalid number karthik swamy Ranch Hand Posts: https://coderanch.com/t/557878/JDBC/databases/java-sql-SQLException-ORA-invalid 45 posted 4 years ago hello everyone, Although i think my code is correct but then also it si showing error,So please tell me where i http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm am wrong. here is my code i am getting strOrgId as 1',2',3' String strOrgId=(String)session.getAttribute("strOrgId"); String strQry1="SELECT (TO_CHAR(SYSDATE,'yddd')||MAX(SUBSTR(DE095,5)))+1 FROM APP_MAST_CBOUT WHERE org_id in (?)"; PreparedStatement pst1=null; pst1=dbcon.getConnection().prepareStatement(strQry1); oracle error pst1.setString(1,strOrgId); obj.runQuery(dbcon.getConnection(),pst1); then also showing me this error java.sql.SQLException: ORA-01722: invalid number Regards Karthik Swamy Sudheer Bhat Ranch Hand Posts: 75 posted 4 years ago Your code is not right. You are setting a string literal to the query in list where as DB expects the in list to be numbers. The oracle error 1722 query which gets exected in your case will look like SELECT ...... FROM table where something in ('1,2,3'); Make sure your query gets constructed as SELECT ...... FROM table where something in (1,2,3); Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB). karthik swamy Ranch Hand Posts: 45 posted 4 years ago Sudheer Bhat wrote:Your code is not right. You are setting a string literal to the query in list where as DB expects the in list to be numbers. The query which gets exected in your case will look like SELECT ...... FROM table where something in ('1,2,3'); Make sure your query gets constructed as SELECT ...... FROM table where something in (1,2,3); Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB). But Sir i am getting that id as 1','2','3 and after that i am ju
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-01722: invalid number tips Oracle Error Tips by Burleson Consulting Oracle docs offer this information regarding Oracle ORA-01722: ORA-01722 invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. Jonathan Gennick provides information regarding Oracle ORA-01722 in conjunction with subqueries and Oracle Optimizer. To exhibit how Oracle ORA-01722 is often thrown, this query is given as an example: SELECT * FROM ( SELECT FLAG, TO_NUMBER ( NUM ) NUM FROM SUBTEST WHERE FLAG = 'N' ) WHERE NUM > 0 ; Here, from in the FROM clause of a query, the user is attempting to have a subquery of the original query, which is causing Oracle ORA-01722 to be thrown. The Oracle ORA-01722 error is thrown with the failure because of the outer query. This is because it is trying to test the NUM > 0 condition first because it is assumed it might be more useful. Gennick goes on to show that Oracle ORA-01722 is thrown because the Oracle optimizer has re-written the query as: SELECT FLAG, TO_NUMBER ( NUM ) NUM FROM SUBTEST WHERE TO_NUMBER ( NUM ) > 0 AND FLAG = 'N' ; This throws Oracle ORA-01722 because the re-written query causes the system to convert a non-numeric NUM value of the WHERE clause into numbers. OraFaq also has notes on Oracle ORA-01722. Here, it i