Oracle: ORA-01727 Error Message

ORA-01727 Error Message

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

Description

When you encounter an ORA-01727 error, the following error message will appear:

• ORA-01727: numeric precision specifier is out of range 1 to 38

Cause

You tried to specify a NUMERIC datatype, but you did not specify a precision value between 1 and 38.

Resolution

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

Option #1

Try modifying your NUMERIC datatype so that precision is between 1 and 38. If you do not specify a precision, Oracle assumes a precision of 22.

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

CREATE TABLE supplier
( supplier_id numeric(39) not null,
  supplier_name varchar2(50) not null
);

You would receive the following error message:

You could correct this error by defining the supplier_id column as a NUMERIC column with precision between 1 and 38. In this example, we've defined the precision as 8.

CREATE TABLE supplier
( supplier_id numeric(8) not null,
  supplier_name varchar2(50) not null
);

OR

You can omit the precision. Oracle will then assume a precision of 22.

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

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