Home » SQL & PL/SQL » SQL & PL/SQL » Inserting values from lower precision to higher (Oracle 10g/10.2.0.4 on Sun Solaris)
Inserting values from lower precision to higher [message #411237] Thu, 02 July 2009 12:04 Go to next message
hibyte
Messages: 11
Registered: March 2006
Junior Member
I'm trying to insert some rows from one table (t1) to another (t2).

It is giving me an error:
ORA-01438: value larger than specified precision allowed for this column

The only difference between t1 & t2 is that of one columns which is defined as number(15,9) in t2 (higher precision) where as it is defined as number(15,6) in t1 (lower precision). Rows are inserted into t2(higher precision) from t1 (lower precision)

I thought oracle implicitly converts data during insert.

Any help is appreciated.

[Updated on: Thu, 02 July 2009 12:13]

Report message to a moderator

Re: Inserting values from lower precision to higher [message #411239 is a reply to message #411237] Thu, 02 July 2009 12:19 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
No, how would you expect Oracle to do this:
SQL> desc g
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 G                                                  NUMBER(15,9)

SQL> desc h
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 H                                                  NUMBER(15,6)

SQL> insert into h values (1234567);

1 row created.

SQL> insert into g values (1234567);
insert into g values (1234567)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
Re: Inserting values from lower precision to higher [message #411241 is a reply to message #411239] Thu, 02 July 2009 12:36 Go to previous messageGo to next message
hibyte
Messages: 11
Registered: March 2006
Junior Member
But, all the values stored in t1 are 123.456789.
I ever tried to make t2 column as number(18,9) but it is still giving me the same error.
Re: Inserting values from lower precision to higher [message #411242 is a reply to message #411241] Thu, 02 July 2009 12:38 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Then provide a test case with DDL, DML and sample data.

By
Vamsi
Re: Inserting values from lower precision to higher [message #411243 is a reply to message #411237] Thu, 02 July 2009 12:41 Go to previous message
hibyte
Messages: 11
Registered: March 2006
Junior Member
It worked after I dropped both the tables and re-created t2 with number(18,9) and re-loaded the same data into t1 and then moved to t2. Thanks.
Previous Topic: using cursor returned from another stored proc
Next Topic: Missing Numbers
Goto Forum:
  


Current Time: Sat Dec 03 12:16:14 CST 2016

Total time taken to generate the page: 0.13161 seconds