Stack Overflow
Stack Overflow

Questions tagged [ora-01722]

ORA-01722: invalid number in Oracle. 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.

ORA-01722: invalid number

Cause:

The attempted conversion of a character string to a number in Oracle 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.

Reference:

103 questions
71
votes
9 answers

"Safe" TO_NUMBER()

SELECT TO_NUMBER('*') FROM DUAL This obviously gives me an exception: ORA-01722: invalid number Is there a way to "skip" it and get 0 or NULL instead? The whole issue: I have NVARCHAR2 field, which contains numbers and not almost ;-) (like *) and…
zerkms
  • 249,484
  • 69
  • 436
  • 539
40
votes
3 answers

C# parameterized queries for Oracle - serious & dangerous bug!

This is an absolute howler. I cannot believe my own eyes, and I cannot believe nobody before me would have discovered this if it was a genuine bug in C#, so I'm putting it out for the rest of the developer community to tell me what I am doing…
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
20
votes
3 answers

oracle jdbc driver version madness

Why the heck does Oracle offer a different(!) version of the JDBC driver, e.g. ojdbc14.jar, for every(!) database version? The files all have different sizes and thus probably different content. background: We get a random and seemingly…
Stroboskop
  • 4,327
  • 5
  • 35
  • 52
17
votes
2 answers

Create a Sequence with START WITH from Query

How can I create a Sequence where my START WITH value comes from a query? I'm trying this way: CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table"); But, I get the ORA-01722 error
Victor
  • 8,309
  • 14
  • 80
  • 129
15
votes
1 answer

Oracle number and varchar join

I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of…
broschb
  • 4,976
  • 4
  • 35
  • 52
14
votes
1 answer

Joining tables with LIKE (SQL)

First of all I am using Oracle: Table One Name = tableone Table Two Name = tabletwo tableone has a column named pizzaone, tabletwo has a column named pizzatwo. I want to join tableone to tabletwo where pizzaone is somewhere in the pizzatwo's…
Jacob Nelson
  • 2,946
  • 5
  • 33
  • 41
10
votes
3 answers

Why am I getting an ORA-01722 (invalid number)?

I've been using a parameterized query to insert values into an Oracle table, like so: var q = "insert into MyTable(Field1, Field2...) values(:Field1, :Field2...)"; var cmd = new OracleCommand(q, conn); // conn is a pre-existing…
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
7
votes
1 answer

.nextval JDBC insert problem

I try to insert into table with sequence .nextval as primary key, the sql in Java is sql = "INSERT INTO USER (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) VALUES (?,?,?,?,?)"; ps = conn.prepareStatement(sql); …
jasonfungsing
  • 1,625
  • 8
  • 22
  • 34
6
votes
5 answers

PL SQL : NVL first parameter type conversion issue

I have a table Application which has a column BORROWINGTERM NUMBER(10,0) Nullable why this script throw an error (ORA-01722 invalid number) select nvl(borrowingterm, 'no term') from Application while this one works select…
nandin
  • 2,549
  • 5
  • 23
  • 27
6
votes
6 answers

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…
user1017344
  • 181
  • 1
  • 1
  • 11
5
votes
3 answers

problem using Oracle parameters in SELECT IN

I have a problem when inserting a string of numbers into sql query SELECT * FROM tablename a WHERE a.flokkurid IN (3857,3858,3863,3285) ORDER BY sjodategund, rodun ...or: SELECT * FROM tablename a WHERE a.flokkurid IN…
aghaux
  • 729
  • 4
  • 14
  • 38
5
votes
5 answers

Function or Procedure for an IN clause

I want to write a funcion or procedure that can be used in the IN clause of another procedure. The function or procedure would return ID numbers. The main procedure would say something like SELECT * FROM EMPLOYEES WHERE OFFICE_ID IN…
user39653
  • 1,275
  • 3
  • 15
  • 17
5
votes
3 answers

Can I pass a number for varchar2 in Oracle?

I have an Oracle table and a column (col1) has type varchar2(12 byte). It has one row and value of col1 is 1234 When I say select * from table where col1 = 1234 Oracle says invalid number. Why is that? Why I cannot pass a number when it is…
Victor
  • 16,609
  • 71
  • 229
  • 409
4
votes
2 answers

Another issue with to_number(). I simply do not understand it

I have a master table (hereafter called SURVEY) and a detail table (hereafter called ANSWERS.) Unsurprisingly, ANSWERS has answers to SURVEY questions. ANSWERS has a VARCHAR2 column named TEXT. Some ANSWERS.TEXT values are truly text but some are…
Tony Ennis
  • 12,000
  • 7
  • 52
  • 73
4
votes
4 answers

Getting weird issue with TO_NUMBER function in Oracle

I have been getting an intermittent issue when executing to_number function in the where clause on a varchar2 column if number of records exceed a certain number n. I used n as there is no exact number of records on which it happens. On one DB it…
Fazal
  • 2,991
  • 7
  • 29
  • 37
1
2 3 4 5 6 7