Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Richard .

Asked: May 02, 2000 - 1:20 pm UTC

Answered by: Tom Kyte - Last updated: July 17, 2012 - 9:21 am UTC

Category: - Version:

Viewed 100K+ times! This question is

Whilst you are here, check out some content from the AskTom team: I love people who think logically - except when they expose my illogic

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' )



and you rated our response

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

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, meaning (all varchar2). If further some of my domains contain purely numeric values. Is there a way that I can not have to worry about which way my predicates are evaulated. Or will I always have to wrap a to_char() around my numeric columns?

The reason I ask is becuase I have just had to trouble-shoot this problem for one of my developers, who is on two weeks leave. His package works fine on the development box (NT Oracle 8.1.6), but when run on the test/integration machine (VAX Oracle 8.1.7.2) this error was returned.

Tom Kyte

Followup  

July 12, 2002 - 7:40 am UTC

The only sure fire way to avoid this in pretty much every language is:

compare numbers to numbers,
strings to strings,
dates to dates,
etc to etc....

always avoid the implicit conversion -- don't store numbers in varchar2's (i know, i know "its a generic model", well, generic models have their limited advantages -- and they have their very serious drawbacks).

specific code = more reliable code.
generic code = "pretty cool, but will it work" sometimes....

with CBO your example works

December 10, 2002 - 3:23 pm UTC

Reviewer: A reader

Hi

if I analyze the table from your example then the query works. Is this limitation or behaviour shows only in RBO?

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select * from t where x = 2 and y > 100;

         X Y
---------- -------------------------
         2 123 

Tom Kyte

Followup  

December 10, 2002 - 8:42 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1299201885045 <code>

which is the converse of this example. there rbo "works" and cbo "fails"

but neither "fails" really - the query was wrong to begin with. assumptions were made that were not valid -- that there is a defined order of operation in SQL.

By definition -- there is no defined order!

ORA-1722 using V$PARAMETER

December 10, 2002 - 9:13 pm UTC

Reviewer: Pablo Rovedo from Argentina

I have an interesting example where CBO works but RBO doesn't.


sys@ROP816> ed
Wrote file afiedt.buf

1 select /*+ RULE */ value
2 from sys.v_$parameter
3 where name = 'log_checkpoint_timeout' and
4* value > 1000
sys@ROP816> /
value > 1000
*
ERROR at line 4:
ORA-01722: invalid number

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 MERGE JOIN
2 1 FIXED TABLE (FULL) OF 'X$KSPPCV'
3 1 FILTER
4 3 SORT (JOIN)
5 4 FIXED TABLE (FULL) OF 'X$KSPPI'


but if we use CBO the query works

sys@ROP816> ed
Wrote file afiedt.buf

1 select /*+ FIRST_ROWS */ value
2 from sys.v_$parameter
3 where name = 'log_checkpoint_timeout' and
4* value > 1000
sys@ROP816> /

VALUE
--------------------------------------------------------
1800


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=32 Card=1 Bytes=337)
1 0 NESTED LOOPS (Cost=32 Card=1 Bytes=337)
2 1 FIXED TABLE (FULL) OF 'X$KSPPI' (Cost=16 Card=1 Bytes=60)
3 1 FIXED TABLE (FIXED INDEX #2) OF 'X$KSPPCV' (Cost=16 Card=5 Bytes=1385)


The explanation to this is because of the first plan involves full table scan of x$ksppcv, computing to_number() function on non-numeric values.

Cheers
Pablo Rovedo

Tom Kyte

Followup  

December 10, 2002 - 9:23 pm UTC

My whole point here is simple: there is no defined order, period.

A simple change in plan will cause it to "fail"

I can show you 1,000 where RBO "works" CBO "fails"
I can show you another 1,000 where CBO "works" RBO "fails"

It is neither CBO or RBO here, it is the decidely undecided way in which queries are evaluated.

continuing the same topic.

February 24, 2003 - 4:53 am UTC

Reviewer: Yogesh Bhardwaj from Bangalore, India

hi tom!
in continuation of the disscussion i wanna know why i'm getting the output while i'm compare different datatypes.
t is the same table(x int,y varchar2(25));

SQL> select * from t where '123'=123;

         X Y
---------- -------------------------
         1 abc
         2 123
in the above query '123' is string and 123 is number. so it should give the error ora 1722. but it doesn't.

if i'm firing the query like
SQL> select * from t where y=123;
select * from t where y=123
                      *
ERROR at line 1:
ORA-01722: invalid number 

Tom Kyte

Followup  

February 24, 2003 - 7:39 am UTC

well, '123' = 123 is the same as 123 = 123 -- the string '123' can and is converted to a number, the comparision made and "true" is returned.

All rows come out.


But, if you code where 'abc' = 123, well, that'll fail since 'abc' CANNOT be converted to a number. So, that query flops over and dies.


convert the NUMBER to a string


select * from t where y = to_char(123);

will work dandy.

Excellent

February 24, 2003 - 8:47 pm UTC

Reviewer: Doug

That sort of thing could drive a DBA/Developer
to drink!

Gotta fly Happy Hour is on

Another Question Regarding Datatypes and Output

August 17, 2003 - 7:09 pm UTC

Reviewer: Deanna from SF

Hi Tom,

What would happen in this scenario...
The developers created the following table:
Table1
Field1 = datatype_name
Field2 = value_data

datatype_name = Numeric or Qualitative
value_data can be
123
+
-

The end user wants to output the data to excel from Oracle such that if the datatype_name = Numeric the string is converted to a number and if the datatype_name = Qualitative the string is left as a string.

I tried a decode statement

decode (datatype_name , 'Numeric', to_number(value_data), to_text (value_data))

Didnt work, Oracle errors...

Any suggestions on how to output both numeric and text in the same column, while avoiding having to convert everything to a string?

Thanks again!







Tom Kyte

Followup  

August 17, 2003 - 7:50 pm UTC

A column is EITHER number or string -- not both.

there is the sys.anydata type (search for it here). but -- will the client application be ready to handle it.



August 18, 2003 - 2:36 am UTC

Reviewer: Helena Marková from Bratislava, Slovakia


Superb!!!

August 18, 2003 - 6:04 am UTC

Reviewer: A reader


1722 using a view..

August 02, 2004 - 11:37 am UTC

Reviewer: dxl from uk

Tom

Can you explain what maybe happening in the following case:

The AGESEXNOTOTALS is a view :

CREATE OR REPLACE VIEW AgeSexNoTotals
AS
SELECT
GPCode,
AgeBand,
DispensingFemale,
DispensingMale,
Indeterminate,
LocalGPCode,
PrescribingFemale,
PrescribingMale,
PracticeCode,
Quarter
FROM TEMP_AGESEX_YEARLY_BANDS
WHERE ((Upper(AgeBand) <> 'TOTALS') AND (Upper(AgeBand) <> 'TO'))
/


Ageband is a varchar in the table and should only contain numbers in the view.


16:17:29 TEST@DEV>select distinct AgeBand,
16:18:03 2 TO_NUMBER(AgeBand)
16:18:03 3 from AGESEXNOTOTALS;

AGEBAND TO_NUMBER(AGEBAND)
---------- ------------------
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
31 31
32 32
33 33
34 34
35 35
36 36
37 37
38 38
39 39
40 40
41 41
42 42
43 43
44 44
45 45
46 46
47 47
48 48
49 49
50 50
51 51
52 52
53 53
54 54
55 55
56 56
57 57
58 58
59 59
60 60
61 61
62 62
63 63
64 64
65 65
66 66
67 67
68 68
69 69
70 70
71 71
72 72
73 73
74 74
75 75
76 76
77 77
78 78
79 79
80 80
81 81
82 82
83 83
84 84
85 85
86 86
87 87
88 88
89 89
90 90
91 91
92 92
93 93
94 94
95 95
96 96
97 97
98 98
99 99
100 100
101 101
102 102
103 103
104 104
105 105
106 106
107 107
108 108

109 rows selected.

Elapsed: 00:00:03.02
16:18:06 TEST@DEV>select distinct AgeBand,
16:18:20 2 TO_NUMBER(AgeBand)
16:18:20 3 from AGESEXNOTOTALS
16:18:20 4 where to_number(AgeBand) BeTWEEN 0 AND 4;
where to_number(AgeBand) BeTWEEN 0 AND 4
*
ERROR at line 4:
ORA-01722: invalid number


However rewritten like this :

Elapsed: 00:00:00.09
16:18:21 TEST@DEV>select AgeBand,
16:18:40 2 age_band_num
16:18:40 3 from (select distinct AgeBand Ageband,
16:18:40 4 TO_NUMBER(AgeBand) age_band_num
16:18:40 5 from AGESEXNOTOTALS)
16:18:40 6 where age_band_num BeTWEEN 0 AND 4;

AGEBAND AGE_BAND_NUM
---------- ------------
0 0
1 1
2 2
3 3
4 4

5 rows selected.

Elapsed: 00:00:00.07
16:18:41 TEST@DEV>

gives the right results.

So why do i get an ora 1722 for :

select distinct AgeBand,
TO_NUMBER(AgeBand)
from AGESEXNOTOTALS
where to_number(AgeBand) BeTWEEN 0 AND 4;


and not for :

select AgeBand,
age_band_num
from (select distinct AgeBand Ageband,
TO_NUMBER(AgeBand) age_band_num
from AGESEXNOTOTALS)
where age_band_num BeTWEEN 0 AND 4;

Also if i rewrite the query to select from the base table and not the view i get:

16:21:29 TEST@DEV>select distinct AgeBand,
16:21:56 2 TO_NUMBER(AgeBand)
16:21:56 3 from TEMP_AGESEX_YEARLY_BANDS
16:21:56 4 WHERE ((Upper(AgeBand) <> 'TOTALS') AND (Upper(AgeBand) <> 'TO'))
16:21:56 5 and to_number(AgeBand) BeTWEEN 0 AND 4;

AGEBAN TO_NUMBER(AGEBAND)
------ ------------------
0 0
1 1
2 2
3 3
4 4

5 rows selected.

Elapsed: 00:00:00.06
16:21:58 TEST@DEV>


Which is the correct behaviour i want.

Do you know why this is happening?
(we are on 8.1.7)

Tom Kyte

Followup  

August 02, 2004 - 12:43 pm UTC

you have zero control over when to_number will be evaluated here. the predicate is pushed into the view and merged with the view text.

i see this time and time and time and time and time (and lots more times) again over and over (history doomed to repeat itself) as people store numbers and dates in strings.


your:

select * from VIEW where to_number(c) ....

is exactly the same as:

select * from table where <view predicate> and <to_number(c) ....>


using inline views and distinct caused portions to be materialized and hence "worked by accident" (but are not assured to always work, a simple change in plan and bam-- back to ora-1722.




You can use

case when ageband not in ( 'TOTALS', 'TO' ) then to_number(ageBand) end



instead of just to_number.

the behaviour you call "correct" is accidently. the behaviour you see is predicable and expected. It'll happen every single time, EVERY SINGLE TIME, you put a number or a date into a string. ugh.

thanks

August 03, 2004 - 4:37 am UTC

Reviewer: dxl from uk

Thanks for the reply.
I tried using your suggestion but i still got

09:20:08 DBO@AIS>select distinct AgeBand,
09:20:09 2 TO_NUMBER(AgeBand)
09:20:09 3 from AGESEXNOTOTALS
09:20:09 4 where case when upper(ageband) not in ( 'TOTALS', 'TO' ) then
09:20:09 5 to_number(ageBand) end
09:20:09 6 BeTWEEN 0 AND 4;
where case when upper(ageband) not in ( 'TOTALS', 'TO' ) then
*
ERROR at line 4:
ORA-01722: invalid number


Elapsed: 00:00:00.07

Is that how you meant me to use it? or did you mean for me to change the view definition?


Seeing as i cannot redesign the database at this time (legacy system) then what should i do to ensure i do not hit this problem again?

If using an inline view, that forces it to materialize at that point, produces the right results for this particular case, are you saying that is NOT a good enough solution and this could still not guarantee 1722 free code? ie could the plan still change if we left the code alone?

Any advise here would be useful thanks.

Tom Kyte

Followup  

August 03, 2004 - 8:46 am UTC

all references to to_number(ageband) must be "protected". you have a to_number() that is not on line 2.


inline views *do not force*, it was the use of distinct there that made you get "lucky" in that case -- they definitely do not *force*. a simple change in plan would "break it again".




?

August 03, 2004 - 9:24 am UTC

Reviewer: A reader

Please can you explain in more detail what you mean by protected??
how would you rewrite the query using the CASE statement to ensure it runs correctly?



Tom Kyte

Followup  

August 03, 2004 - 9:34 am UTC

ops$tkyte@ORA9IR2> select       distinct AgeBand,
  2          case when upper(ageband) not in ('TOTALS', 'TO' )
  3               then to_number(ageband)
  4           end
  5    from     v
  6    where case when upper(ageband) not in ( 'TOTALS', 'TO' )
  7               then to_number(ageband)
  8           end between 0 and 4
  9  /



line 2 -- you had simply "to_number(ageband)"  -- it was not protected by the CASE statment. 

August 03, 2004 - 10:04 am UTC

Reviewer: dxl from uk

Yes thats what i thought you meant but when i do that i get:

14:56:19 test@dev>select distinct AgeBand,
14:56:19 2 case when upper(ageband) not in ('TOTALS', 'TO' )
14:56:19 3 then to_number(ageband)
14:56:19 4 end
14:56:19 5 from AGESEXNOTOTALS
14:56:19 6 where case when upper(ageband) not in ( 'TOTALS', 'TO' )
14:56:19 7 then to_number(ageband)
14:56:19 8 end between 0 and 4
14:56:19 9 /
where case when upper(ageband) not in ( 'TOTALS', 'TO' )
*
ERROR at line 6:
ORA-01722: invalid number



It seems to be complaining about the ageband reference in

" where case when upper(ageband) not in "

that bit, but why is it attempting a number conversion there? i did not ask for one, is it implicit?

Tom Kyte

Followup  

August 03, 2004 - 10:09 am UTC

you'll have to help me reproduce - give me a create table and inserts into and all that do that, I cannot reproduce that issue at all.

the solution

April 28, 2005 - 11:32 am UTC

Reviewer: Martin from Vienna, Austria

Thank you for this big insight. It's the definitive answer that nothing than protecting using case/decode/... is for 100% secure.

Protect TO_NUMBER with case

May 12, 2005 - 7:21 am UTC

Reviewer: Nils Winkler from Frankfurt, Germany

Thanks for the hint about "protecting" the TO_NUMBER call with a case statement, that solved a problem I've been working on.

I've had the displeasure of having to read data from a table that is populated by a third-party product, where one column contains mixed data - strings and numbers. For some of the queries I've been getting the most popular ORA-01722 error before finding your solution.

Thanks again!

ora-01722

February 14, 2006 - 3:22 pm UTC

Reviewer: A reader

I have a table source where a column lic has values in varchar2.
when i use select lic from source it gives result as
04369
65251
09652
11809
13088
11693
17173
17563
10548
116195
116532
116529
118478
132871
136607
137435
141068
170665
181648
182936
179909
185588
185684
129374
296504
298157
299311
299312
299313
310918
311456
NA
----------

and When I have to convert to target table and in a pl/sql block i use

select to_number(lic) from source.
it gives error ora-01722 invalid number.

what can be the reason as there is no '.' or 'e' or '-' values in the li column.

please advice.
Thanks


Tom Kyte

Followup  

February 14, 2006 - 3:29 pm UTC

well, there is that big old "NA" in there.

ops$tkyte@ORA10GR1> select to_number( 'na' ) from dual;
select to_number( 'na' ) from dual
                  *
ERROR at line 1:
ORA-01722: invalid number


that would tend to do it.

Happens every single, every single, every single time someone has the brilliant idea to "use a string to store a number!"

 

target has number

February 14, 2006 - 3:33 pm UTC

Reviewer: A reader

my target table has number column so i have to convert it into number.

I am using it in a cursor, so all the other number should be converted but they are not.



Tom Kyte

Followup  

February 14, 2006 - 3:39 pm UTC

it is a "result set"
^^^

You will

a) fetch a string
b) convert string into number in an exception block
c) insert stuff that is a number


Or you can write a function "my_to_number()" that returns NULL when the thing cannot convert and the number otherwise.


but like I said, every single - every every single time - you use a string to store a number, you will be faced with this.

thanks

February 14, 2006 - 3:59 pm UTC

Reviewer: A reader

I was
convert string into number in an exception block
anyways My problem is solved as it was error of not taking all the columns in Primary key.

Regards

Tom Kyte

Followup  

February 14, 2006 - 4:36 pm UTC

no you weren't, you selected to_number( string ) from table.

you did not select a string from the table and then convert to a number in an exception block.


for x in ( select * from t )
loop
begin
l_number := x.str;
...
exception
.....
end;
end loop;

ORA-1722 During Import

April 28, 2008 - 5:05 pm UTC

Reviewer: Doug Cartwright from USA

I've exported a table from a 9.2.0.8 database, and imported it into a 10.2.0.3 database. The import completes but I get the error ORA-1722. Have you ever seen this during an import? Is there any way to correct this behaviour?
Tom Kyte

Followup  

April 29, 2008 - 8:36 am UTC

not sure the order of events here - you get an error during the import, but the import completes - can you be more specific.

ORA-01722 obscures the true problem

May 29, 2008 - 7:58 pm UTC

Reviewer: John Sisson from Sacramento, CA

Our product uses Oracle 9.2 and has an 'address' table with a column for imported data defined as "LEGACY_ID NVARCHAR(30)". The new importer failed to write text to this column with error 01722. The Jdbc SQL exception carried no useful extra data; SqlPlus failed just as silently.

Finally we discovered a site-dba had added an index as follows:
index: IX_ADDRESS$TONUMBERLEGACY_ID
expression: TO_NUMBER("LEGACY_ID")
This appears to have effectively created a silent constraint.

The ORA-01722 event is so discreet about cause that you can't even identify the failing column from GUI, CommandLine or Jdbc. This ate up a lot of my day, but I suppose the reward is that I know one more weird thing to look for in future!

ORA-01722 after an update

October 13, 2008 - 5:01 pm UTC

Reviewer: Jarod from Oklahoma City, OK

Tom,

One of our developers has a job that will select certain fields in a table that has a varchar2 column.  All records in this column were a number until recently an update changed one record in this column to a number and alpha character.  Now all records that are selected by this job in this table will return an ORA-01722.  I replicated this issue to further examine this event:

XOTC/DTX1.L> create table xotc_imp_test_tbl (imp_key number(10), fileda varchar2(10));

Table created.

XOTC/DTX1.L> insert into xotc_imp_test_tbl values(1,1);

1 row created.

XOTC/DTX1.L> insert into xotc_imp_test_tbl values(2,2);

1 row created.

XOTC/DTX1.L> select * from xotc_imp_test_tbl;

IMP_KEY       FIELDA
----------    -----------
1             1
2             2

XOTC/DTX1.L> select * from xotc_imp_test_tbl where fielda=2;

IMP_KEY       FIELDA
----------    -----------
2             2

XOTC/DTX1.L> update xotc_imp_test_tbl set fielda='1A' where imp_key=1;

1 row updated.

XOTC/DTX1.L> select * from xotc_imp_test_tbl;

IMP_KEY       FIELDA
----------    -----------
1             1A
2             2

Now when I attempt the same query from above of the record that was not updated:

XOTC/DTX1.L> select * from xotc_imp_test_tbl where fielda=2;
select * from xotc_imp_test_tbl where fielda=2
                      *
ERROR at line 1:
ORA-01722: invalid number

If you would provide feedback on the reason for Oracle to respond in this way and what would be the best option for correcting this issue, I would appreciate it.

Thanks,
Jarod

Tom Kyte

Followup  

October 14, 2008 - 6:00 pm UTC

come on - really? this is confusing? even after reading this page???

You are comparing a string to a number.

You have strings
You do not have numbers in your column.

You convert a string to a number to compare to a number

and one of the strings is not convertable.

So, it logically FAILS.


use strings to store strings
use numbers to store numbers
use dates to store dates

and never compare a string to a number
never compare a string to a date
never compare a number to a date

and so on.


when you compare a string to a number, the string is converted to a number and then compared. consider this SCARY example:

ops$tkyte%ORA11GR1> create table t ( x varchar2(10) );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t values ( '2.0' );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( '+2' );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( '       2' );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( '2.000000' );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set autotrace on explain
ops$tkyte%ORA11GR1> select * from t where x = 2;

X
----------
2.0
+2
       2
2.000000


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    28 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     4 |    28 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=2)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA11GR1> set autotrace off



find the faulty row

February 11, 2009 - 3:29 pm UTC

Reviewer: Pinguman

Hi

I have a table with over 20 million of rows, one of column is varchar2 but it should have defined as number. Recently there is a data migration from some old legacy system to this system and from time to time users get ORA-01722 error, I think there are some data which contains ",". Something went wrong with some data conversions during the migration.

I know the easy way to find the problematic row is using PL/SQL and loop the row and to_number the column value and catch the exception. But I wonder if there are any other better way since this table is not small.

Thank you


Tom Kyte

Followup  

February 12, 2009 - 10:47 am UTC

well, if you are fairly sure it is a comma

where instr(column,',') > 0

would find it. Or if you expect "all of our numbers are just digits, no decimals, no nothing but numbers" then

where replace( translate( col, '0123456789','000000000'), '0', '' ) is not null

would find that (maybe add a ltrim(col) to remove leading spaces if that counts and so on...)

what version? another way might be dml error logging, insert that column into a scratch table - log errors to another table, all failed rows would appear over there (10g and above)

ora-01722 capture

February 14, 2009 - 10:01 am UTC

Reviewer: Pinguman

Hi Tom

It is 10.2.0.1 on Windows x64.

I think I will give a shot with translate() and replace()

Thank you

I have a problem

February 15, 2009 - 9:41 pm UTC

Reviewer: ashok from Dallas,TX

Hi Tom,
I'm getting invalid no error.. while fetching the result.
i'm comparing both fields datatype is varchar2(14) still i'm getting error.
Tom Kyte

Followup  

February 16, 2009 - 12:26 pm UTC

no idea what you are doing - you'll actually need to describe the issue you are encountering and what you are trying.

Why can't the error message be more specific...

February 18, 2009 - 1:08 pm UTC

Reviewer: Evan from Chantilly, VA USA

Hi Tom,
This question isn't specific to ORA-1722, but this is one place where it appears.

Something that's always bothered me about Oracle is that the error messages aren't always specific about where the problem occurred. For instance, okay, I understand that I did an implicit conversion where I shouldn't have. But why can't Oracle tell me WHICH of the fields it was trying to convert? Certainly, somewhere in the depths of the query engine, it knows, and it would be nice if it told me...

In an ideal world, it'd be obvious, but sometimes when you're dealing with someone else's code, and there's two dozen different fields in the SQL, a little more help would be nice...

I figure there's a good reason why Oracle doesn't tell you this, and I always wondered why....

Is it a Bug in Oracle or in The Query??

September 18, 2009 - 11:58 am UTC

Reviewer: Bhushan from Lagos,Nigeria

Dear Thomas,
Below is the query i run it runs perfect with the where clause commnented.The moment i put in the where clause it gives me invalid number error.

SELECT CAlculated_total,csv_value-CAlculated_total FROM (
SELECT inv_no,CSV_STRING,tran_code,defaultcode,
prd_group,
product_dtl,to_number(CAlculated_total) CAlculated_total,base_amount ,tran_amount ,base_price ,csv_value ,to_number(csv_value-CAlculated_total) act_total

FROM(
SELECT
inv_no,CSV_STRING,tran_code,defaultcode,
prd_group,
product_dtl,
nvl(substr(FIRST,2,instr(FIRST,'$',1,2)-instr(FIRST,'$',1,1)-1)* substr(FIRST,instr(FIRST,'$',1,2)+1,instr(FIRST,'$',1,3)-instr(FIRST,'$',1,2)-1),0)+
nvl(substr(SECOND,2,instr(SECOND,'$',1,2)-instr(SECOND,'$',1,1)-1)* substr(SECOND,instr(SECOND,'$',1,2)+1,instr(SECOND,'$',1,3)-instr(SECOND,'$',1,2)-1),0)+
nvl(substr(third ,2,instr(third ,'$',1,2)-instr(third,'$',1,1)-1)* substr(third,instr(third,'$',1,2)+1,instr(third,'$',1,3)-instr(third,'$',1,2)-1),0)+
nvl(substr(fourth ,2,instr(fourth,'$',1,2)-instr(fourth,'$',1,1)-1)* substr(fourth,instr(fourth,'$',1,2)+1,instr(fourth,'$',1,3)-instr(fourth,'$',1,2)-1),0)+
nvl(substr(fifth ,2,instr(fifth,'$',1,2)-instr(fifth,'$',1,1)-1)* substr(fifth,instr(fifth,'$',1,2)+1,instr(fifth,'$',1,3)-instr(fifth,'$',1,2)-1),0)+
nvl(substr(sixth ,2,instr(sixth,'$',1,2)-instr(sixth,'$',1,1)-1)* substr(sixth,instr(sixth,'$',1,2)+1,instr(sixth,'$',1,3)-instr(sixth,'$',1,2)-1),0)+
nvl(substr(seventh ,2,instr(seventh,'$',1,2)-instr(seventh,'$',1,1)-1)* substr(seventh,instr(seventh,'$',1,2)+1,instr(seventh,'$',1,3)-instr(seventh,'$',1,2)-1),0)+
nvl(substr(eighth ,2,instr(eighth,'$',1,2)-instr(eighth,'$',1,1)-1)* substr(eighth,instr(eighth,'$',1,2)+1,instr(eighth,'$',1,3)-instr(eighth,'$',1,2)-1),0)+
nvl(substr(nine ,2,instr(nine,'$',1,2)-instr(nine,'$',1,1)-1)* substr(nine,instr(nine,'$',1,2)+1,instr(nine,'$',1,3)-instr(nine,'$',1,2)-1),0)+
nvl(substr(ten ,2,instr(ten,'$',1,2)-instr(ten,'$',1,1)-1)* substr(ten,instr(ten,'$',1,2)+1,instr(ten,'$',1,3)-instr(ten,'$',1,2)-1),0)+
nvl(substr(eleven ,2,instr(eleven,'$',1,2)-instr(eleven,'$',1,1)-1)* substr(eleven,instr(eleven,'$',1,2)+1,instr(eleven,'$',1,3)-instr(eleven,'$',1,2)-1),0)+
nvl(substr(twelve ,2,instr(twelve,'$',1,2)-instr(twelve,'$',1,1)-1)* substr(twelve,instr(twelve,'$',1,2)+1,instr(twelve,'$',1,3)-instr(twelve,'$',1,2)-1),0)+
nvl(substr(thirteen ,2,instr(thirteen,'$',1,2)-instr(thirteen,'$',1,1)-1)* substr(thirteen,instr(thirteen,'$',1,2)+1,instr(thirteen,'$',1,3)-instr(thirteen,'$',1,2)-1),0)+
nvl(substr(fourteen ,2,instr(fourteen,'$',1,2)-instr(fourteen,'$',1,1)-1)* substr(fourteen,instr(fourteen,'$',1,2)+1,instr(fourteen,'$',1,3)-instr(fourteen,'$',1,2)-1),0)+
nvl(substr(fifteen ,2,instr(fifteen,'$',1,2)-instr(fifteen,'$',1,1)-1)* substr(fifteen,instr(fifteen,'$',1,2)+1,instr(fifteen,'$',1,3)-instr(fifteen,'$',1,2)-1),0)+
nvl(substr(sixteen ,2,instr(sixteen,'$',1,2)-instr(sixteen,'$',1,1)-1)* substr(sixteen,instr(sixteen,'$',1,2)+1,instr(sixteen,'$',1,3)-instr(sixteen,'$',1,2)-1),0)+
nvl(substr(seventeen ,2,instr(seventeen,'$',1,2)-instr(seventeen,'$',1,1)-1)* substr(seventeen,instr(seventeen,'$',1,2)+1,instr(seventeen,'$',1,3)-instr(seventeen,'$',1,2)-1),0)+
nvl(substr(eighteen ,2,instr(eighteen,'$',1,2)-instr(eighteen,'$',1,1)-1)* substr(eighteen,instr(eighteen,'$',1,2)+1,instr(eighteen,'$',1,3)-instr(eighteen,'$',1,2)-1),0)+
nvl(substr(nineteen ,2,instr(eighteen,'$',1,2)-instr(eighteen,'$',1,1)-1)* substr(eighteen,instr(eighteen,'$',1,2)+1,instr(eighteen,'$',1,3)-instr(eighteen,'$',1,2)-1),0)+
nvl(substr(twenty ,2,instr(twenty,'$',1,2)-instr(twenty,'$',1,1)-1)* substr(twenty,instr(twenty,'$',1,2)+1,instr(twenty,'$',1,3)-instr(twenty,'$',1,2)-1),0)+
nvl(substr(twentyone ,2,instr(twentyone,'$',1,2)-instr(twentyone,'$',1,1)-1)* substr(twentyone,instr(twentyone,'$',1,2)+1,instr(twentyone,'$',1,3)-instr(twentyone,'$',1,2)-1),0)+
nvl(substr(twentytwo ,2,instr(twentytwo,'$',1,2)-instr(twentytwo,'$',1,1)-1)* substr(twentytwo,instr(twentytwo,'$',1,2)+1,instr(twentytwo,'$',1,3)-instr(twentytwo,'$',1,2)-1),0)+
nvl(substr(twentythree ,2,instr(twentythree,'$',1,2)-instr(twentythree,'$',1,1)-1)* substr(twentythree,instr(twentythree,'$',1,2)+1,instr(twentythree,'$',1,3)-instr(twentythree,'$',1,2)),0) CAlculated_total,
base_amount ,tran_amount ,base_price ,csv_value

FROM (SELECT CSV_STRING,tran_code,defaultcode,
SUBSTR(CSV_STRING, 2, INSTR(CSV_STRING, '/', 2, 1) - 2) FIRST,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 1) + 1,
INSTR(CSV_STRING, '/', 2, 2) - INSTR(CSV_STRING, '/', 2, 1) - 1) SECOND,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 2) + 1,
INSTR(CSV_STRING, '/', 2, 3) - INSTR(CSV_STRING, '/', 2, 2) - 1) THIRD,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 3) + 1,
INSTR(CSV_STRING, '/', 2, 4) - INSTR(CSV_STRING, '/', 2, 3) - 1) FOURTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 4) + 1,
INSTR(CSV_STRING, '/', 2, 5) - INSTR(CSV_STRING, '/', 2, 4) - 1) FIFTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 5) + 1,
INSTR(CSV_STRING, '/', 2, 6) - INSTR(CSV_STRING, '/', 2, 5) - 1) SIXTH,

SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 6) + 1,
INSTR(CSV_STRING, '/', 2, 7) - INSTR(CSV_STRING, '/', 2, 6) - 1) SEVENTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 7) + 1,
INSTR(CSV_STRING, '/', 2, 8) - INSTR(CSV_STRING, '/', 2, 7) - 1) EIGHTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 8) + 1,
INSTR(CSV_STRING, '/', 2, 9) - INSTR(CSV_STRING, '/', 2, 8) - 1) NINE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 9) + 1,
INSTR(CSV_STRING, '/', 2, 10) - INSTR(CSV_STRING, '/', 2, 9) - 1) TEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 10) + 1,
INSTR(CSV_STRING, '/', 2, 11) - INSTR(CSV_STRING, '/', 2, 10) - 1) ELEVEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 11) + 1,
INSTR(CSV_STRING, '/', 2, 12) - INSTR(CSV_STRING, '/', 2, 11) - 1) TWELVE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 12) + 1,
INSTR(CSV_STRING, '/', 2, 13) - INSTR(CSV_STRING, '/', 2, 12) - 1) THIRTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 13) + 1,
INSTR(CSV_STRING, '/', 2, 14) - INSTR(CSV_STRING, '/', 2, 13) - 1) FOURTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 14) + 1,
INSTR(CSV_STRING, '/', 2, 15) - INSTR(CSV_STRING, '/', 2, 14) - 1) FIFTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 15) + 1,
INSTR(CSV_STRING, '/', 2, 16) - INSTR(CSV_STRING, '/', 2, 15) - 1) SIXTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 16) + 1,
INSTR(CSV_STRING, '/', 2, 17) - INSTR(CSV_STRING, '/', 2, 16) - 1) SEVENTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 17) + 1,
INSTR(CSV_STRING, '/', 2, 18) - INSTR(CSV_STRING, '/', 2, 17) - 1) EIGHTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 18) + 1,
INSTR(CSV_STRING, '/', 2, 29) - INSTR(CSV_STRING, '/', 2, 18) - 1) NINETEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 19) + 1,
INSTR(CSV_STRING, '/', 2, 20) - INSTR(CSV_STRING, '/', 2, 19) - 1) TWENTY,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 20) + 1,
INSTR(CSV_STRING, '/', 2, 21) - INSTR(CSV_STRING, '/', 2, 20) - 1) TWENTYONE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 21) + 1,
INSTR(CSV_STRING, '/', 2, 22) - INSTR(CSV_STRING, '/', 2, 21) - 1) TWENTYTWO,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 22) + 1,
INSTR(CSV_STRING, '/', 2, 23) - INSTR(CSV_STRING, '/', 2, 22) - 1) TWENTYTHREE,
INV_NO,
PRD_GROUP,
PRODUCT_DTL,
BASE_AMOUNT,
TRAN_AMOUNT,
BASE_PRICE,
CSV_VALUE
FROM BRS_TRAN_DTL WHERE company='SNL' AND prd_group='CHEM' AND defaultcode !='SLA' AND tran_effect IN ('R','S')
--AND defaultcode='SGM'
) ))
WHERE to_number(nvl(csv_value,0)-nvl(CAlculated_total,0)) > 0
I was very sure there are no characters so i created a table with 2 columns and data type as number and inserted from this SQL all perfect.Any clue why this query might be giving invalid number when run with WHERE (to_number(nvl(csv_value,0)-nvl(CAlculated_total,0)) > 0) clause. DB version is Connected to Oracle9i Enterprise Edition Release 9.0.1.1.1 .Connected through PL/SQL developer. One request..if you think there is noway you can answer having a look at the query, due to insufficient data please reply in a single word IGNORED.I will try to make up some data then. TIA Cheers!!!
Bhushan
Tom Kyte

Followup  

September 18, 2009 - 12:45 pm UTC

I see no where clause

but undoubtedly - it is not a bug, you are comparing a string to a number, we convert the string to a number and - guess what - it isn't a number

happens every time you store numbers or dates in strings

every
single
time


I can already tell I hate your data "model", I put "model" in quotes - because if you have to parse a string like that in your "model", we are using the term "model" very loosely and very very generously

Yes I totally agree with you

September 19, 2009 - 6:17 am UTC

Reviewer: Bhushan from Lagos,Nigeria

Dear Thomas,
Yes i totally agree with you using a term model for this set up is more than very very generous; however the query which i am trying to build up is to check the sanity of the data, just to confirm if whatever we have in our DB is atleast what we thought of or what we think.
Sorry yesterday my query was half posted, dont know why, atleast when i previewed before posting it showed the complete query.
I have narrowed down to what the problem could be just need your advice.
pleae clarify my doubt

When i run this query

SELECT --Outer Query
nvl(substr(twentythree ,2,instr(twentythree,'$',1,2)-instr(twentythree,'$',1,1)-1)* substr(twentythree,instr(twentythree,'$',1,2)+1,instr(twentythree,'$',1,3)-instr(twentythree,'$',1,2)),0)
FROM(
SELECT SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 22) + 1,
INSTR(CSV_STRING, '/', 2, 23) - INSTR(CSV_STRING, '/', 2, 22) - 1) TWENTYTHREE,
INV_NO,
PRD_GROUP,
PRODUCT_DTL,
BASE_AMOUNT,
TRAN_AMOUNT,
BASE_PRICE,
CSV_VALUE
FROM BRS_TRAN_DTL WHERE company='SNL' AND prd_group='CHEM' AND defaultcode !='SLA' AND tran_effect IN ('R','S')
)
where twentythree >0

When is the following condition applied to the data set
WHERE company='SNL' AND prd_group='CHEM' AND defaultcode !='SLA' AND tran_effect IN ('R','S')
1) Before applying twentythree >0
2) After applying twentythree >0
3) Might differ from time to time
4) Purely depends on the Statistics of the table. (Analyze table)
5) I am being completely dumb and you ask me to shutup and go on with life :(


And yes..thanks a ton for your quick answer earlier.

Cheers!!!
Bhushan
Tom Kyte

Followup  

September 28, 2009 - 12:05 pm UTC

you cannot control the order of predicate evaluation and as far as we are concerned:


select *
from ( select * from t where x = 'A' )
where y > 0

is the same as:


select * from t where y > 0 and x = 'A'

is the same as

select * from t where x = 'A' and y > 0

is the same as

select * from (select * from t where y > 0) and x = 'A'



twentythree is a string, compare it to strings and only strings.

When is the condition applied?

September 21, 2009 - 11:07 am UTC

Reviewer: Duke Ganote from Amelia, Ohio USA

Perfect Answer!!!

September 21, 2009 - 6:15 pm UTC

Reviewer: Bhushan from Lagos, Nigeria

Now i know why it fails.Though the data set that is returned does not contain any invalid number there are some rows who are violating that conversion. Mr.Duke thanks for the links. (Surprisingly i had gone through one of them before it came to my mind about the order of execution :) ) Anyways..very very useful information and good to increase one's knowledge bank.

Cheers!!!
Bhushan

Potential work around

July 18, 2011 - 2:40 am UTC

Reviewer: Mike W from Australia

With regards 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


A potential work around is:

select * from t where x = 2 and case when LENGTH(TRIM(TRANSLATE(y, ' +-.0123456789', ' '))) is null then to_number(y) end > 100



Tom Kyte

Followup  

July 18, 2011 - 10:49 am UTC

of course, as long as you don't have 5+5 in there.

IF you put a number in a string
THEN
someday someone will put garbage in there
END IF
and it'll always run slower than it should as you jump through hoops trying to workaround this "design"

trying to understand...

July 28, 2011 - 8:48 pm UTC

Reviewer: A reader

SQL> select count(num) from
  2  (select to_number(stringvalue) as num from attribute a, attrvalue av where a.LANGUAGE_ID = -1 and a.field1 = 'NoOfImage' and a.ATTRIBUTE_ID = av.ATTRIBUTE_ID) tab
  3  where num > 0;
where num > 0
      *
ERROR at line 3:
ORA-01722: invalid number


I'm pretty sure num is an integer here.  What's causing the error?

Tom Kyte

Followup  

August 01, 2011 - 11:00 am UTC

I'm pretty sure NUM is not a number. I'm pretty sure stringvalue is a string and you have hidden a number in there SOMETIMES.


Your query is the same as:

select count( to_number(stringvalue))
  from attribute a, attrvalue av 
 where a.LANGUAGE_ID = -1 
   and a.field1 = 'NoOfImage' 
   and a.ATTRIBUTE_ID = av.ATTRIBUTE_ID
   and to_number(stringvalue) > 0


SQL is not a procedural language -

select * from (select ...) Q1 where q1.xxx > 0

does not force (select ...) Q1 to be evaluated AND THEN have where q1.xxx > 0 applied to it, we do view merging and predicate pushing.

(reason 43143164 why EAV's - entity attribute value - models will bit you in the butt every time - time after time)


for example:



SQL> set autotrace on explain
SQL> select count(num)
  2    from (select to_number(stringvalue) as num
  3            from attribute a, attrvalue av
  4           where a.LANGUAGE_ID = -1
  5             and a.field1 = 'NoOfImage'
  6             and a.ATTRIBUTE_ID = av.ATTRIBUTE_ID) tab
  7   where num > 0;



Execution Plan
----------------------------------------------------------
Plan hash value: 4238808995

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |    73 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE     |           |     1 |    73 |            |          |
|*  2 |   HASH JOIN         |           |     1 |    73 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| ATTRIBUTE |     1 |    60 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ATTRVALUE |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ATTRIBUTE_ID"="AV"."ATTRIBUTE_ID")
   3 - filter("A"."LANGUAGE_ID"=(-1) AND "A"."FIELD1"='NoOfImage' AND
              TO_NUMBER("STRINGVALUE")>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

<b>see the where clause filter applied there?  That is the real predicate - step 3 is a killer, you would have to do something like this:</b>


SQL> select count(num)
  2    from (select case when language_id = -1 and field1 = 'NoIfImage' then to_number(stringvalue) end as num
  3            from attribute a, attrvalue av
  4           where a.LANGUAGE_ID = -1
  5             and a.field1 = 'NoOfImage'
  6             and a.ATTRIBUTE_ID = av.ATTRIBUTE_ID) tab
  7   where num > 0;


Execution Plan
----------------------------------------------------------
Plan hash value: 4238808995

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |    73 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE     |           |     1 |    73 |            |          |
|*  2 |   HASH JOIN         |           |     1 |    73 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| ATTRIBUTE |     1 |    60 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ATTRVALUE |     1 |    13 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ATTRIBUTE_ID"="AV"."ATTRIBUTE_ID")
   3 - filter("A"."LANGUAGE_ID"=(-1) AND "A"."FIELD1"='NoOfImage' AND
              CASE  WHEN ("LANGUAGE_ID"=(-1) AND "FIELD1"='NoIfImage') THEN
              TO_NUMBER("STRINGVALUE") END >0)

Note
-----
   - dynamic sampling used for this statement (level=2)

<b>see that predicate - that is "safe" as I assume that language_id and field1 have to have a certain value to ensure that stringvalue can be considered a number...</b>

SQL> set autotrace off



If you are going to use EAV's, at least have a stringvalue, numbervalue, datevalue SET of columns so you can put numbers dates and strings in there

And even then - rethink this bad idea in general... Most of the times, EAV's should not be used.

Invalid number error when comparin both numbers

July 17, 2012 - 7:46 am UTC

Reviewer: Deepa

Hi Tom,

I am facing one issue in oracle 10g

When I am running following query

SELECT DISTINCT 'MATCHED'||A.ISIN_CPTY AS ISIN_CPTY,
to_number(A.QTY_ALL) Aquan,to_number(b.qty_all) bquan, 'SLAVE' AS CREATION_SYSTEM, A.COUNTERPARTY,A.SECURITY_CODE,
A.SECURITY_DESCRIPTION, A.LOAN_VALUE, A.LOAN_VALUE_CURR,A.QUANTITY,A.MARKET_VALUE,
A.ISIN,A.MKT_VAL_LOAN_CURR,A.PRICE,A.PTYPE,A.CNTRY_ISS,A.
SRC_DEAL_ID_NM,A.BGNREF,A.TRADE_DATE,A.SEC_SET_DATE,A.BL_IND,A.
SECURITY_SEDOL,A.MSET_TRADE_REF,A.SMODE,A.COLL_FLAG,A.ASSET,A.
STOCK_NAME,A.DEPOT,A.STOCK_DESC_2,A.UNIT_PRICE,A.UNIT_PRICE_CCY,A.
OPENING_BALANCE,A.CLOSING_BALANCE,A.OP,A.USER_ID,A.COLL_HAIRCUT,A.
TRADE_STATUS,A.QUICK,A.TICKER,A.LENDER,A.BORROWER,A.
ESCROW_DESC,A.ESCROW_AGGREMENT,A.FUND_DESC,A.BASE_CCY,A.MARKET_VALUE_CCY,A.
BASE_CCY_VALUE,A.BASE_CCY_EXCHANGE_VALUE,A.CLASS
FROM
(
SELECT ISIN_CPTY , QTY_ALL,row_number() OVER (PARTITION BY ISIN_CPTY, QTY_ALL ORDER BY ISIN_CPTY, QTY_ALL) FROM V_JPM_RECORDS
INTERSECT
SELECT ISIN_CPTY , QTY_ALL, row_number() OVER (PARTITION BY ISIN_CPTY, QTY_ALL ORDER BY ISIN_CPTY, QTY_ALL) FROM V_GLOBAL1_RECORDS
) B,
V_JPM_RECORDS A
WHERE
A.ISIN_CPTY =B.ISIN_CPTY

It is giving me good result without any error.But if run the query as

select * from (
SELECT DISTINCT 'MATCHED'||A.ISIN_CPTY AS ISIN_CPTY,
to_number(A.QTY_ALL) Aquan,to_number(b.qty_all) bquan, 'SLAVE' AS CREATION_SYSTEM, A.COUNTERPARTY,A.SECURITY_CODE,
A.SECURITY_DESCRIPTION, A.LOAN_VALUE, A.LOAN_VALUE_CURR,A.QUANTITY,A.MARKET_VALUE,
A.ISIN,A.MKT_VAL_LOAN_CURR,A.PRICE,A.PTYPE,A.CNTRY_ISS,A.
SRC_DEAL_ID_NM,A.BGNREF,A.TRADE_DATE,A.SEC_SET_DATE,A.BL_IND,A.
SECURITY_SEDOL,A.MSET_TRADE_REF,A.SMODE,A.COLL_FLAG,A.ASSET,A.
STOCK_NAME,A.DEPOT,A.STOCK_DESC_2,A.UNIT_PRICE,A.UNIT_PRICE_CCY,A.
OPENING_BALANCE,A.CLOSING_BALANCE,A.OP,A.USER_ID,A.COLL_HAIRCUT,A.
TRADE_STATUS,A.QUICK,A.TICKER,A.LENDER,A.BORROWER,A.
ESCROW_DESC,A.ESCROW_AGGREMENT,A.FUND_DESC,A.BASE_CCY,A.MARKET_VALUE_CCY,A.
BASE_CCY_VALUE,A.BASE_CCY_EXCHANGE_VALUE,A.CLASS
FROM
(
SELECT ISIN_CPTY , QTY_ALL,row_number() OVER (PARTITION BY ISIN_CPTY, QTY_ALL ORDER BY ISIN_CPTY, QTY_ALL) FROM V_JPM_RECORDS
INTERSECT
SELECT ISIN_CPTY , QTY_ALL, row_number() OVER (PARTITION BY ISIN_CPTY, QTY_ALL ORDER BY ISIN_CPTY, QTY_ALL) FROM V_GLOBAL1_RECORDS
) B,
V_JPM_RECORDS A
WHERE
A.ISIN_CPTY =B.ISIN_CPTY)
where
Aquan=bquan

it giving me invalid number error although i have converted both the value in where in number in subquery.

What can be an issue as all the values in database are numbers.


Regards

Tom Kyte

Followup  

July 17, 2012 - 9:21 am UTC

show us the output of a dbms_xplan plan dump please, including any and all bits of information in the predicate section.

DBA_PROFILES

December 05, 2013 - 7:40 am UTC

Reviewer: Giridhar from India

Tom,
if storing numbers in varchar column is a bad practice, I am wondering why oracle stores numeric values in DBA_PROFILES.LIMIT column?

If someone wants to compare values in DBA_PROFILES using LIMIT column for numeric values, they get error.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> desc dba_profiles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE                                   NOT NULL VARCHAR2(30)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(40)



Thanks,
Giridhar

Thanks for your input. Breaking my head.

April 06, 2018 - 9:37 pm UTC

Reviewer: A reader