How to resolve ORA-01728 Error Message in Oracle database

Isaac
Posted by By Isaac 1 year Ago 2 Min Read

Learn the motive and how to get to the bottom of the ORA-01728 error message in Oracle.

Description

When you come upon an ORA-01728 error, the following error message will appear:

ORA-01728: numeric scale specifier is out of range -84 to 127

Cause

You tried to specify a NUMERIC datatype, however you did now not specify a scale cost between -84 and 127.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try editing your NUMERIC datatype so that scale is between -84 and 127. If you do not specify a scale, Oracle assumes a scale of 0 (ie: zero decimal places).

For example, if you tried to create the following table:

CREATE TABLE supplier
( supplier_id numeric(5) not null,
  supplier_name varchar2(50) not null,
  quantity numeric(10,128)
);

You would receive the following error message:

You should right this error via defining the volume column as a NUMERIC column with scale between -84 and 127. In this example, we’ve defined the scale as three

CREATE TABLE supplier
( supplier_id numeric(5) not null,
  supplier_name varchar2(50) not null,
  quantity numeric(10,3)
);

OR

You can miss the scale. Oracle will then count on a scale of 0

CREATE TABLE supplier
( supplier_id numeric(5) not null,
  supplier_name varchar2(50) not null,
  quantity numeric(10)
);

In this example, NUMERIC(10) is the same as NUMERIC(10,0).