1

I'll try to explain this best I can.

I have materialized view with varchar2 column.

This is the code example:

SELECT field1 * 1000
FROM my_mv 
WHERE id = 15

When I execute this from TOAD, it works just fine (I do it on numbers only ofc). But when I try to do same thing from ASP

sql = "SELECT field1 * 1000 FROM my_mv WHERE id = 15"
set rs = conntemp.Execute(sql)

I get an error

[Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number

Tried to do to_number on the column, but still the same

Maybe I wasn't clear enough. This is not an Oracle error (Oracle can preform this query with no problem because column value can be converted to number). Problem is when this is called from classic ASP, then error is thrown.

And as already said, I also tried to do 

SELECT to_number(field1) * 1000

but I still get the same result

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BeRightBack
  • 186
  • 3
  • 15
  • 2
    Possible duplicate of [Can I pass a number for varchar2 in Oracle?](http://stackoverflow.com/questions/10420376/can-i-pass-a-number-for-varchar2-in-oracle) – user692942 Mar 21 '16 at 14:51
  • The error comes from Oracle `ODBC driver for Oracle`. Error [ORA-01722](http://www.orafaq.com/wiki/ORA-01722). – user692942 Mar 21 '16 at 15:03
  • Stating this is a Classic ASP error is nonsense, I guarantee that you will get the same result using the provider with any other language. – user692942 Mar 21 '16 at 15:10
  • Sorry, I'm not familiar with ASP(.NET?), but is this reproduceable in vb.net? – Recoil Mar 21 '16 at 17:16
  • This might be an artifact of an oversimplified example, but can't you do the multiplying by 1000 in the VBScript, *after* you've already done the filtering that makes sure your results will always be numeric? – Martha Mar 21 '16 at 19:23

1 Answers1

0

This will be happening because Oracle attempts to implicitly cast field1 to integer but fails because that column has other non-numeric values in it.

See @jason-quinones answer to Can I pass a number for varchar2 in Oracle?

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • No, that is not the case. It is numeric value and oracle can convert it when i call it from toad. I only get error when calling from classic asp, called directly from toad works fine – BeRightBack Mar 21 '16 at 15:01
  • @BeRightBack The value might be "numeric" *(`VARCHAR2` value implicitly cast to numeric)* but is the rest of the table column numeric? Trying to do an implicit cast like this is asking for trouble regardless of whether it works in TOAD or not. – user692942 Mar 21 '16 at 15:05
  • 1
    You might find that [TOAD is handling the implicit casting](http://www.toadworld.com/platforms/oracle/w/wiki/2007.datatypes-datatype-conversion) for you but the Oracle ODBC Provider isn't. – user692942 Mar 21 '16 at 15:08
  • Yes, all values in that field are numeric. And yes, know that it is not the best way, but it's my only way :) Yes, i know that toad is doing implicit casting, what i tried to ask is is there a way to make ODCB to do it? And thanks for your answers Before you say it, can't change view or even create new to make that field number instead of varchar – BeRightBack Mar 21 '16 at 15:12
  • NLS variables were first thing i checked and when selected from ASP and shown on screen, they seem correct NLS_NUMERIC_CHARACTERS - ., But now actually tried to do replace(field1, ',','.') * 1000 and error is gone. Thank you, don't know why do i get wrong values when checking nls varibles but that seems to be a problem. – BeRightBack Mar 21 '16 at 20:31
  • Oh, i see now i was looking wrong. Thanks, this solved my problem – BeRightBack Mar 21 '16 at 20:47