Oracle: ORA-01728 Error Message

ORA-01728 Error Message

Learn the cause and how to resolve the ORA-01728 error message in Oracle.

Description

When you encounter 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, but you did not specify a scale value between -84 and 127.

Resolution

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

Option #1

Try modifying 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: 0 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 could correct this error by defining the quantity column as a NUMERIC column with scale between -84 and 127. In this example, we've defined the scale as 3.

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

OR

You can omit the scale. Oracle will then assume 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).