I created a table like this:
CREATE TABLE table(
id INTEGER GENERATED ALWAYS AS IDENTITY,
nome VARCHAR2(100 CHAR)
)
ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (ID);
CREATE UNIQUE INDEX TABLE_UNIQ_IDX ON TABLE(NOME ASC);
ALTER TABLE table ADD (PERC NUMBER(1, 2) NOT NULL);
Then I tried to write 2 records on it:
INSERT INTO TABLE(NOME,PERC)VALUES('a',0.8);
INSERT INTO TABLE(NOME,PERC)VALUES('b',0.2);
Then I received this error:
ORA-01438: valor maior que a precisão especificada usado para esta coluna
Translated:
ORA-01438: value larger than specified precision allows for this column
I tried select cast (0.8 as number(1,1)) from dual;
and it worked but when I tried select cast (0.8 as number(1,2)) from dual;
I received the same error.
I then tried select cast (0.81 as number(1,2)) from dual;
and received the same ORA-01438.
I changed my field to number(1,1)
, no big deal, but how does this "Number" data type work?
- Shouldn't
select cast (0.81 as number(1,2)) from dual;
have worked? - Why does
select cast (0.81 as number(2,2)) from dual;
work and select cast (0.81 as number(2,3)) from dual;
does not?
Thanks for any help
CodePudding user response:
If you have NUMBER(precision, scale)
then precision
is the number of digits and scale
is the number of decimal places.
So, NUMBER(1, 2)
has a single digit and 2 decimal places. The minimum value it can store is -0.09
and the maximum it can store is 0.09
.
NUMBER(2,2)
works as it stores 2 digits in 2 decimal places (from -0.99 to 0.99).
NUMBER(2,3)
does not work as it stores 2 digits in 3 decimal places (from -0.099 to 0.099).
CodePudding user response:
What you said, is that perc
column should accept numeric values whose length is 1
, and out of that 1
, you want to keep 2
decimal places. That won't work.
SQL> create table test (perc number(1, 2));
Table created.
SQL> insert into test values (0.8);
insert into test values (0.8)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
Perhaps you meant to put it vice versa?
SQL> alter table test modify perc number(2, 1);
Table altered.
SQL> insert into test values (0.8);
1 row created.
SQL>