How does Number data type work in Oracle 21c?-CodePudding

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>