Fixing ORA-01407

The Oracle error ORA-01407 occurs when you are trying to INSERT or  UPDATE column value to NULL when the column does not accept NULL values.


To resolve ORA-01407, change the UPDATE statement to ensure that when a column is defined as NOT NULL then there is no attempt to UPDATE it with a NULL value. In case of an update statement with a correlated sub-query, one solution to the ORA-01407 error in SQL is to check for NULL rows using the where exists

clause.

update ORDERS a
set a.amount = (select b.qty * b.item_price
                from ITEM b
                where a.item_id = b.item_id);

Error: ORA-01407: cannot update ("SCHEMA_NAME"."ORDERS"."AMOUNT") to NULL.

Replacing the above update statement with this one will remove the ORA-01407 error:

update ORDERS a
set a.amount = (select b.qty * b.item_price
                from ITEM b
                where a.item_id = b.item_id)
where exists (select 1
              from ITEM b
              where a.item_id = b.item_id);
commit;

Using & (ampersand) in INSERT statements

By default, SQL*Plus interprets text preceded by one or two ampersands as a
substitution variable. You can turn this feature off in SQL*Plus by issuing
the command:

SET DEFINE OFF