Home » SQL & PL/SQL » SQL & PL/SQL » How to Avoid ORA-01438: value larger than specified precision allowed for this column (Oracle11g)
How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666764] Fri, 24 November 2017 02:28 Go to next message
ssyr
Messages: 65
Registered: January 2017
Member
Hi All,

I am using one insert statement that is selecting data form other table. But when I tried to execute insert statement it is giving me below error,
"ORA-01438: value larger than specified precision allowed for this column".

In my query how can I modify that column to actual precision .I dnot want to go for alter table command.
insert stg_rw_ 
select    
    
    ,cast(oi.quantity as number(20)) quantity 
    ,cast(nvl(oi.quantity*oi.unit_priceoi.discount,0) as number(38,8)) usd_price_num 
    ,cast(nvl(oi.quantity*oi.unit_priceoi.discount,0) * nvl(o.currency_from_usd_rate,1) as number(38,8)) tx_price_num 
    ,o.currency tx_currency_code 
    ,o.vreferrer aid 
from orders o 
join order_items oi 
on o.orderid = oi.orderid ;

I have to used in cast operator.
Current size and data type of columns are,
QUANTITY	NUMBER(10,0)
TX_PRICE_NUM	NUMBER(38,8)
USD_PRICE_NUM	NUMBER(38,8)

HOw can I truncate it to precision point.
Could you please suggest me some things on below?

Thank you in Advance.



[LF applied CODE tags to table description]

[Updated on: Fri, 24 November 2017 02:52] by Moderator

Report message to a moderator

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666765 is a reply to message #666764] Fri, 24 November 2017 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that the simplest option is to drop the target table (if it is empty) and create all columns as NUMBERs (with no precision).
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666766 is a reply to message #666765] Fri, 24 November 2017 02:43 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you for your prompt reply.

But I cant do this .I want to use substring or trunc function How can I use this?
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666767 is a reply to message #666766] Fri, 24 November 2017 02:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SUBSTR is related to strings so I'd avoid it.

TRUNC accepts two parameters:Oracle

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point
so give it a try. By the way, do you know which column raises an error? What if it is QUANTITY? A simple example: if QUANTITY was NUMBER(2) (which means that the max number it accepts is 99) and you'd want to insert 540 in there, what will you do? TRUNC to what, exactly? So - consider making those columns NUMBERs.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666768 is a reply to message #666767] Fri, 24 November 2017 03:02 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you.

How can I use Substr function here?
Please suggest
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666770 is a reply to message #666768] Fri, 24 November 2017 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you NOT to use SUBSTR, but you still insist on it?
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666771 is a reply to message #666770] Fri, 24 November 2017 03:16 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Okay. Sorry for that.

What could be the better solution for this ?

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666772 is a reply to message #666771] Fri, 24 November 2017 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, either I can't express myself properly, or you don't (want to?) understand what I'm saying. Do you suffer from selective blindness?
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666775 is a reply to message #666772] Fri, 24 November 2017 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Trunc is zero use here.
You don't get that error when you have too many decimals, which trunc could fix (though so could round and it's more obvious).
You get that error when you have too many non-decimals.
Say if you try to insert 1000 into a number(2).

Trunc won't make 1000 into a 2 digit number, nor will cast - it'll throw the same error.

The only sensible solution is modify the columns so they are big enough, anything else is just corrupting data.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666779 is a reply to message #666775] Fri, 24 November 2017 09:02 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
do not use trunc, use round. For example if the precision in the first table is number(15,5) and the precision on the new table is number(15,2) then on your insert use round(my_column,2) and it will use rouding logic to change the precision

[Updated on: Fri, 24 November 2017 09:03]

Report message to a moderator

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666781 is a reply to message #666779] Fri, 24 November 2017 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to round the result when only the scale is reduced, Oracle does it automatically:
SQL>  create table t1 (val number(15,5));

Table created.

SQL>  create table t2 (val number(15,2));

Table created.

SQL> insert into t1 values(1.11555);

1 row created.

SQL> insert into t2 select * from t1;

1 row created.

SQL> select * from t1;
       VAL
----------
   1.11555

1 row selected.

SQL> select * from t2;
       VAL
----------
      1.12

1 row selected.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666808 is a reply to message #666781] Sun, 26 November 2017 22:34 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you for all.


Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666809 is a reply to message #666781] Sun, 26 November 2017 23:38 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Hi all,

Can anybody tell me the difference between Data type storage,

QUANTITY	NUMBER(10,0)--Means how many numbers?
TX_PRICE_NUM	NUMBER(38,8)---How many numbers?
PRODUCT_NAME	VARCHAR2(255 BYTE)---How many characters?
PRODUCT_TYPE	VARCHAR2(4 BYTE)

In my above query it used :"cast(te.quantity as number(20))))as quantity "---what exactly it means?

in my target table Quantity is defined as " QUANTITY NUMBER(10,0)"

Please help me on this?

Thank you in advance.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666811 is a reply to message #666809] Mon, 27 November 2017 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In NUMBER(5, 2), 5 is called "precision" (total number of digits) and 2 is called "scale" (number of digits to the right of the decimal point). You can read it as a column which accepts numbers which have total of 5 digits, whose 2 digits are decimals which means that only 3 digits are left for the whole part of a number. Therefore, you can store 123.45 in there, but you can't store 1234.56.

In VARCHAR2(4 BYTE), you can store 4 bytes. How many characters? It depends on how many bytes those characters take. There are single-byte characters (which occupy 1 byte, so you can store 4 characters), but there are also multi-byte characters (such as in Chinese character set) where "1 characters <> 1 byte".

Have a look at Oracle datatypes which explains it far better than me.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666812 is a reply to message #666811] Mon, 27 November 2017 00:25 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you for your help.

one more thing
cast(te.quantity as number(20))))as quantity 

In m query I used cast operator like this.but in in my target table quantity is declare as,
QUANTITY	NUMBER(10,0)
SO what is highest amount data Quantity column can store?

Please help.

Thanks in Advance.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666813 is a reply to message #666812] Mon, 27 November 2017 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
9999999999
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666816 is a reply to message #666813] Mon, 27 November 2017 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think the real question to ask is why you have such huge numbers in the first place.

number(10) can store 1 less than 10 billion. What on earth are you recording the quantity of that you would need hold numbers >= 10 billion?
And number(38,8) can store 1 less than 1 nonillion (10 to the power 30).

Are you sure the the data you are trying to move is actually correct?

[Updated on: Mon, 27 November 2017 03:44]

Report message to a moderator

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666819 is a reply to message #666816] Mon, 27 November 2017 03:30 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thanks for all .

My question is if I declared like this in select query ,
"cast(te.quantity as number(20))))as quantity " Why is it declared like this "number(20) as quantity "
in my table actual data type and length is number(10,0)

What is the use of this notation in select query and Why to use CAST operator here?

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666820 is a reply to message #666816] Mon, 27 November 2017 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

He stores nanoseconds. Smile

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666821 is a reply to message #666819] Mon, 27 November 2017 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ssyr wrote on Mon, 27 November 2017 10:30
Thanks for all .

My question is if I declared like this in select query ,
"cast(te.quantity as number(20))))as quantity " Why is it declared like this "number(20) as quantity "
in my table actual data type and length is number(10,0)

What is the use of this notation in select query and Why to use CAST operator here?
Ask the one that wrote this, we can't answer the question "why some write bad code?".

Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666822 is a reply to message #666821] Mon, 27 November 2017 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only time it can make sense to use cast on a number to make it number is when you are doing a create table as select.
SQL> create table bob as select rownum a, cast(1 as number(10)) b from dual;

Table created.

SQL> desc bob;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  NUMBER(10)

SQL>
It's used the cast to define the column size.

The way you're using it doesn't make sense. Source and destination size are known.
If the number you are trying to cast is > 20 digits then the cast will throw the error.
If the number is between 11 and 20 digits then the insert will throw the error.

If the number is greater than 10 digits then what do expect it to be transformed into to fit in a number(10)?
If you can't come up with a sensible answer to that (and I very much doubt there is a sensible answer to that) then you need to increase the size of the number 10.
Unless the too large data is erroneous - in which case you need to find that wrong data and fix it in the source.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666828 is a reply to message #666822] Mon, 27 November 2017 05:47 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thanks for you prompt reply.

Yes. I have found the wrong data for Quantity column and its length is 13 .
And value is- 9999999999999

Is this is a data issue?
Due to this only ,is I am facing the error?
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666829 is a reply to message #666828] Mon, 27 November 2017 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since we don't know anything about the data your tables are supposed to hold we couldn't possibly know if that data is correct or not. Only you or other people at your company can answer that.

Any data that's larger than the destination columns will throw the error. Again, you can tell if that's the only one, we can't.
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666830 is a reply to message #666829] Mon, 27 November 2017 06:01 Go to previous message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you for all your suggestions.
Previous Topic: Procedure
Next Topic: Packages
Goto Forum:
  


Current Time: Thu Mar 28 23:48:58 CDT 2024