CODEWITHSUNDEEP
sqloracle-database
mikcutu
mikcutu

Reputation: 1082

Why does my CAST to a float give an "invalid number" error in Oracle?

I have the following example:

create table test_1(c1 float);
insert into test_1 values(cast('000000000000001425.6' as float));

When I want to insert the value, I have

ORA-01722: invalid number

How can I pass over this error, please?

Upvotes: 1

Views: 1079

Answers (1)

user5683823
user5683823

Reputation:

Most likely reason is that your session's nls_numeric_characters is set to ,. meaning that comma is the decimal separator and dot is the thousands separator.

One easy way around that, if you really must accept varchar2 as input, is to apply translate first, to convert all dots to commas and vice versa.

In the illustration below, first I use an alter session statement to make my session behave like yours. Then I show what you are doing now, and at the end I show how you can fix it - notice the translate function:

alter session set nls_numeric_characters = ',.';

Session altered.


create table test_1(c1 float);

Table TEST_1 created.


insert into test_1 values(cast('000000000000001425.6' as float));

Error starting at line : 10 in command -
insert into test_1 values(cast('000000000000001425.6' as float))
Error report -
ORA-01722: invalid number



insert into test_1 values(cast(translate('000000000000001425.6', ',.', '.,')
                           as float));
                           
1 row inserted.

Upvotes: 3

Related Questions