Home » SQL & PL/SQL » SQL & PL/SQL » Number conversion (Ora 11)
Number conversion [message #573970] Fri, 04 January 2013 12:05 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
Hi,I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).
I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"
I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)
Any clues
thank you
Re: Number conversion [message #573975 is a reply to message #573970] Fri, 04 January 2013 12:28 Go to previous message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
n a higher precision table of number(12,15)


Yes but lower range.
number(12,10) can store up to 99.9999999999
number(12,15) can store up to 0.000999999999999

SQL> create table t (v1 number(12,10), v2 number(12,15) );

Table created.

SQL> insert into t (v1) values (99.9999999999);

1 row created.

SQL> insert into t (v1) values (100);
insert into t (v1) values (100)
                           *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into t (v2)  values (0.000999999999999);

1 row created.

SQL> insert into t (v2)  values (0.001);
insert into t (v2)  values (0.001)
                            *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


Regards
Michel
Previous Topic: How to add Oracle constraint to below tables
Next Topic: Add Range Subpartition to a hash Parition table
Goto Forum:
  


Current Time: Mon Sep 15 20:41:21 CDT 2014

Total time taken to generate the page: 0.09704 seconds