You may also want to
see this article about the ORA-12899 which is returned if a value larger than
column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what
is defined for the NUMBER datatype column. Number datatype columns are defined
in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”,
it would mean that maximum width of the data could be 5 digits, out of which 2
will be decimal part (for example 123.45). Following are some examples that
explain this concept further.
-- In the following example,
the inserted value should only have 2 digits, and both digits should be in
the decimal part of the number
SQL>
create table test (sal number(2,2));
Table
created.
SQL>
insert into test values(2.3333);
insert
into test values(2.3333)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(2.3);
insert
into test values(2.3)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(2);
insert
into test values(2)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(.2);
1
row created.
SQL>
-- In the following example,
the inserted value should only have 2 digits for precision part only as no
decimal could be inserted in this column, although INSERT statement would
succeed if decimal value is mentioned.
SQL>
create table test (sal number(2));
Table
created.
SQL>
insert into test values(2.1);
1
row created.
SQL>
insert into test values(2.11);
1
row created.
SQL>
insert into test values(2.111);
1
row created.
SQL>
insert into test values(2.1110000);
1
row created.
SQL>
insert into test values(22.1110000);
1
row created.
SQL>
insert into test values(223.1110000);
insert
into test values(223.1110000)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
select * from test;
SAL
----------
2
2
2
2
22
-- In the following example,
maximum 6 digits could be inserted in the column out of which 4 have to be
decimal part. As soon as we try to insert 3 digits in precision part,
ORA-01438 would be returned because 4 places have to be reserved for the decimal
part.
SQL>
create table test (sal number(6,4));
Table
created.
SQL>
insert into test values(25.65743);
1
row created.
SQL>
insert into test values(2534333.65743);
insert
into test values(2534333.65743)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(253433.65743);
insert
into test values(253433.65743)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(2534.65743);
insert
into test values(2534.65743)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(2.65743);
1
row created.
SQL>
insert into test values(22.65743);
1
row created.
SQL>
insert into test values(223.65743);
insert
into test values(223.65743)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
insert into test values(243.5);
insert
into test values(243.5)
*
ERROR
at line 1:
ORA-01438:
value larger than specified precision allowed for this column
SQL>
select * from test;
SAL
----------
25.6574
2.6574
22.6574
|
No comments:
Post a Comment