SQL & PL/SQL

If ORG_ID is already a number, why are you doing a TO_NUMBER() on it?

Because of implicit number to string conversion and then converting that string into a number.

E.g. (using explicit conversion with format model to demonstrate)

SQL> select to_char(123456,'999,990.00') from dual;

TO_CHAR(123
-----------
123,456.00

SQL> select to_number(to_char(123456,'999,990.00')) from dual;
select to_number(to_char(123456,'999,990.00')) from dual
                *
ERROR at line 1:
ORA-01722: invalid number

SQL>

Hi,

Please let me know , in which scenario does implicit conversion happens. Thanks

2913938 wrote:

Hi,
Please let me know , in which scenario does implicit conversion happens. Thanks

When you call a function using a parameter of the wrong type, Oracle tries to implicitly convert it to one of the right type. So if you do this:

      TO_NUMBER(123456)

TO_NUMBER() expects its input parameter to be a VARCHAR2 (or similar character type), but 123456 is a number. So what Oracle effectively does is this:

       TO_NUMBER(TO_CHAR(123456))

The format masks it uses to do those conversions are dependent on the NLS settings of the session, and it's entirely possible that the result of TO_CHAR(123456) will not be a valid input for TO_NUMBER().

The take homes from this are: (1) Always call functions with parameters of the correct type, and (2) if the values you have are not of the right type, use the appropriate TO_...() function to convert them including the correct format mask to do the conversion. Otherwise you're dependent on Oracle figuring out what to do, today and in the future, and that's likely to come back and bite you one day.

when I User the dump function on the value of column that is giving me "Invalid Number " I got this

select ORG_ID, DUMP(ORG_ID), DUMP(1000000) from DV_OM_AR_DETAIL ;

I got the out put as

If you see the value of column is 1000000 and the its dump output is Typ=2 Len=3: 196,2,1.

but if I use the 1000000 inside the dump then the output is Typ=2 Len=2: 196,2.

So the question is why dump output of  Typ=2 Len=3: 196,2,1 is giving me the "invalid number" error.

{code}

If ORG_ID is already a number, why are you doing a TO_NUMBER() on it?

{code}

We are loading this column into other table of same numeric datatype and hence got the error. we are not using to_number while loading into other table

but still getting the error.

Thanks

Nawneet

One more interesting observation :-

create table test123 (col1 number );   -- Created table

-- Inserted that value into the number column  -- this works fine

insert into test123

select --to_number(ORG_ID) ,

ORG_ID

from Triton.DV_OM_AR_DETAIL;

-- If tried the same to_number , go the error

select to_number(col1) from test123;

"ORA-01722: invalid number"

I am not sure about the above behaviour

If ORG_ID is a NUMBER, what you're in effect doing is

TO_NUMBER(TO_CHAR(ORG_ID))

So what do you get if you do TO_CHAR(ORG_ID)? Presumably it's something that TO_NUMBER() won't digest.

to_char(org_id) gives same error "ORA-01722: invalid number"

I found post on net

http://stackoverflow.com/questions/19273610/to-charnumber-function-returns-ora-01722-invalid-number

I  also have Trailing Zero on my column and hence getting the same error.

How can we design our ETL so that above errors can be identified early ?

If we tried to insert these column in next table these will never be found , So how to find these issue early

Thanks

Nawneet

Clearly the problem here is not with the calls to to_number or forced convesions, since org_id is already invalid in the DB - this is shown by dump(org_id) (OP's reply #5).

As discussed recently in another thread, there are ways to store invalid values in the DB. In that case, the DB accepted an invalid DATE value produced by add_months - the DB didn't reject the value it was presented, even though it was not a valid DATE. To answer your question, you will need to go back and find out HOW the org_id got into the DB in the first place.

BluShadow has a nice writeup on the numeric data type, explaining how to understand the dump output.   For 1000000, the internal representation is in two bytes (Len=2), 196 and 2. This is what it means:  First, decompose 1000000 in base 100; this is easy, the exponent is 3 (100^3 = 1000000) and the mantissa is 1. 1*100^3 = 1000000. The sign is +. In the DB, the first byte in the representation is 128+64+4 (binary 11000100). The first bit, the left-most 1, simply indicates the + sign; 1000000 is a positive number. The remaining bits encode the exponent. For its own reasons, Oracle adds 1 to the exponent, and then it adds 64 (so that the second bit is always 1 for positive exponents). So that's how you get 196: 128+64+3+1. 128 indicates positive number. 64 indicates positive EXPONENT (1000000 and 0.000001 are both positive numbers, but the first has positive exponent while the second has negative exponent). The remaining bytes represent the "digits" of the original number, but in base 100, not 10. That is, the digits are between 0 and 99. Again here Oracle FIRST ADDS ONE (1) to the base-100 digit, and then stores the result; so valid bytes are between 1 and 100, when translated from base 2 to base 10. This is why 1000000 is represented as 196, 2.

Note however that "1" should not be a valid value for the last byte in a "Type 2" representation of a number. That would indicate the last base-100 digit of the original number is 00; but then this shouldn't be shown in the mantissa, it should only make the exponent greater by 1!  "1" is a perfectly legitimate value for other bytes, but not for the last one. So the three-byte representation, 196, 2, 1  does not represent a valid number.  EDIT: This is not quite right; the bytes after the SECOND byte represent digits after the decimal point. Still, there is no point in representing a number as 1.00; it should just be 1. Wonder if this may happen with CURRENCY type representations though, where one might force two decimal places?  END EDIT

I would not be at all surprised to learn that the "1" byte at the end (resulting from adding 1 to the number zero) is caused somehow by the null CHARACTER, \0 in C notation, which is the string terminator in C (and very likely in Oracle as well, which is written mostly in C); somewhere in the input process, before org_id ever got stored, it may have been in some sort of character format.

Can you review the process by which org_id gets put into the DB in the first place? Do you have the needed access and privileges? If not, perhaps you can find out from others who do.

Thanks for the detailed explanation but still few more question

1. Why does not the oracle database flag this as error when its first comes to the database. ( If I tried to insert this column into other table using insert into ...select .. . It works fine ).

2. We do not have much control to the source system as it was developed long back , but how can we build a air tight solution for our data warehouse while extracting it.?

Thanks

Nawneet

2913938 wrote:

Hi,
Please let me know , in which scenario does implicit conversion happens. Thanks

Implicit conversion happens when you call a procedure or function with a parameter value that does not match that parameter's data type.

TO_NUMBER has a single character (string aka varchar2) parameter. You are passing a number value. This value is converted to a string in order to match that parameter's data type.

TO_NUMBER thus receives a string as parameter value, and converts it to a number.

Which is silly in this regard as it

a) is not logical

b) does not validate the number as a number

905179 wrote:
We are loading this column into other table of same numeric datatype and hence got the error. we are not using to_number while loading into other table
but still getting the error.

No, you are not. A number is a number in Oracle. It is simply ridiculous to assume that a value from one column fails as a value for another column, due to a type error, when both columns have identical data types.

So your load process has a bug (like causing implicit type conversions), and failing.  It is not the data. It is (as it usually is), shoddy code.

Why the DB doesn't flag invalid values... can't speak for Oracle. Also, for full disclosure, note that I am a complete novice, so many things I say may not be quite right.

One benefit of not checking for validity is speed. However, as you have seen, this comes at the cost of accuracy.

What to do now?  Well, you can try to catch invalid values in various ways (for example write org_id + 0 wherever you would normally use org_id; whenever org_id is invalid, you will get an error message). Pretty tedious, hard to maintain, ... At least make sure to comment explaining WHY you do this so future programmers won't think that's silly and remove "+ 0" from your expressions.

@Reply #14   I respectfully disagree. As  I explained in my reply (#11), the value in the DB is already invalid.

To put this particular issue to bed: If the input data is correct, Oracle DB is smart enough to do the typecasts correctly. to_number(1000000) causes an implicit to_char to be executed, but that does not give the DB any difficulty, as seen here:

SQL> select dump(to_number(1000000)) from dual;

DUMP(TO_NUMBER(100
------------------
Typ=2 Len=2: 196,2

1 row selected.

That is the correct internal representation of the original number, 1000000.

Hi Mathguy,

@Reply #14   I respectfully disagree. As  I explained in my reply (#11), the value in the DB is already invalid

You are right the value or number datatype column is wrong in the database  and if you check the below link we can insert the "non numeric data into numeric column" and more dangerous issue is this can be carried forwards to any number of tables.

and we will hit on the issue only when we are doing any calculation on it.

http://stackoverflow.com/questions/19273610/to-charnumber-function-returns-ora-01722-invalid-number

Actually, I am not sure anymore that you can detect invalid values by throwing them into a function or operation. I played with it a little bit and found some strange results. I will post it as a separate (but related) question; I believe that is the protocol in this forum. Stay tuned.