totn Oracle Error Messages

Oracle / PLSQL: ORA-01407 Error Message

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

Description

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

  • ORA-01407: cannot update ("SCHEMA"."TABLE_NAME"."COLUMN_NAME") to NULL

Cause

You tried to update a column to a NULL value but the column will not accept NULL values.

Resolution

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

Option #1

Correct your UPDATE statement so that you do not UPDATE a column with a NULL value when the column is defined as NOT NULL.

For example, if you had a table called suppliers defined as follows:

CREATE TABLE suppliers
( supplier_id number not null,
  supplier_name varchar2(50) not null 
);

And you tried to execute the following UPDATE statement:

UPDATE suppliers
SET supplier_name = null
WHERE supplier_id = 10023;

You would receive the following error message:

Oracle PLSQL

You have defined the supplier_name column as a NOT NULL field. Yet, you have attempted to update the field with a NULL value.

You could correct this error with the following UPDATE statement:

UPDATE suppliers
SET supplier_name = 'IBM'
WHERE supplier_id = 10023;

Now, you are inserting a NOT NULL value into the supplier_name column.