In an insert, I am passing a value like "9.9". How can I insert it into a numeric column like "NUMBER (4,2)"?
I try to_number ('9.9', '9G999D99') but in the table it is like '99' and I need it to be 9.9
1 Answer
If you pass a number, you'll insert a number. Otherwise, use TO_CHAR with appropriate format mask (and TO_NUMBER to explicitly inform Oracle what you're doing).
SQL> create table test (val number(4, 2));
Table created.
SQL> insert into test (val) values (1.1);
1 row created.
SQL> insert into test (val) values (to_number(to_char('2,2', '99D99')));
1 row created.
SQL> insert into test (val) values
2 (to_number(to_char('3,3', '99D99', 'nls_numeric_characters=,.')));
1 row created.
SQL> select * From test order by val;
VAL
----------
1,1
2,2
3,3
Error appears if I use it in a wrong manner:
SQL> insert into test (val) values
2 (to_number(to_char('4,4', '99D99', 'nls_numeric_characters=.,')));
(to_number(to_char('4,4', '99D99', 'nls_numeric_characters=.,')))
*
ERROR at line 2:
ORA-01722: invalid number
SQL>
In Croatia, we have a decimal comma, not point; switch to decimal point:
SQL> alter session set nls_numeric_characters = '.,';
Session altered.
SQL> select * From test order by val;
VAL
----------
1.1
2.2
3.3
SQL>
Comments
