Ошибка ora 01400 cannot insert null into

Have you gotten the “ORA-01400: cannot insert null into (string)” error? Learn what causes this and how to resolve it in this article.

ORA-01400 Cause

If you try to run an INSERT statement to insert data into a table, you may get this error.

ORA-01400: cannot insert NULL into (string)

In Oracle databases, you can store a NULL value in any column of any data type, as long as the column is not defined as “NOT NULL” or is a primary key.

A NULL value is not the same as 0 or an empty string of ”.

When you attempt to insert a record into a table, and a value of NULL is being inserted into a column that does not allow NULL values, then this error will occur.

To resolve the ORA-01400 error, you have a few options:

  1. Change your INSERT statement so it inserts a value that is not NULL
  2. Change your table definition so that it allows for NULL values.

Let’s take a look at these solutions in more detail.

Solution 1: Adjust your INSERT Statement

To avoid the ORA-01400 error, you can adjust your INSERT statement to ensure that a non-NULL value is inserted.

Let’s see an example of this.

We have a customer table here:

CREATE TABLE customer (
  customer_id NUMBER PRIMARY KEY,
  customer_name VARCHAR2(100) NOT NULL,
  email_address VARCHAR2(400)
);

The customer_id has been set as the primary key, which means it cannot take NULL values.

The customer_name field has the words NOT NULL after it. This means a NOT NULL constraint has been applied and NULL values are not allowed in this column.

The email_address column is allowed NULL values.

Now, let’s try insert a value.

INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (1, 'John', '[email protected]');

This value is inserted successfully.

Now, let’s try specify a NULL value for the customer_name:

INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (2, NULL, '[email protected]');
SQL Error: ORA-01400: cannot insert NULL into ("INTRO_USER"."TEST_CUSTOMER"."CUSTOMER_NAME")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.

The error appears because a NULL value is specified for the customer_name column, which is not allowed to have NULL values.

This could also happen with this statement. We have not specified the customer_name field in the INSERT statement columns, so a NULL value is used.

INSERT INTO customer (customer_id, email_address)
VALUES (3, '[email protected]');
SQL Error: ORA-01400: cannot insert NULL into ("INTRO_USER"."TEST_CUSTOMER"."CUSTOMER_NAME")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.

To resolve this, we need to ensure that a value of NULL is not added into this column.

Change the value of customer_name to something that is not NULL:

INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (3, 'Sarah', '[email protected]');

Or, if you’re getting data from another source, surround your value with the NVL function, which translates a NULL value to something else.

INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (3, NVL(input_name, 'Unknown'), '[email protected]');

If your values are coming from another system or source and definitely should not be NULL, then you might need to investigate the source of the data to find out why the values are NULL.

Solution 2: Change the Table Definition

Another way to resolve the ORA-01400 error is to change the definition of the table so that it allows for NULL values.

You can do this using the ALTER TABLE statement.

For example, to remove the NOT NULL constraint from the customer_name field in the customer table (mentioned above), you can run this command:

ALTER TABLE customer MODIFY COLUMN customer_name VARCHAR2(100);

This will remove the NOT NULL constraint from the table.

Now, you can insert a NULL value into this column.

INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (4, NULL, '[email protected]');

Conclusion

So, that’s how you can resolve the ORA-01400 error.

It’s caused by inserting a NULL value into a column that cannot be NULL.

You can resolve it by adjusting your INSERT statement or modifying your table.

Источник

I’m trying to insert a record into a table, but getting the error —

‘ORA-01400: cannot insert NULL into (….’. The table structure is:

I migrate from Mysql to Oracle.

On Mysql this works, but on Oracle it is not working. How can I fix this? Do I need write all column insert query or unselect not null option

a_horse_with_no_name's user avatar

asked Mar 6, 2013 at 12:25

neoerol's user avatar

9

Try this:

create or replace trigger EDITIONS_COR
  before insert or update on EDITIONS
  for each row
begin
  if INSERTING then
    select EDITIONS_SEQ.nextval into :new.ID from DUAL;
  end if;
  :new.CORDATE:=SYSDATE;
  :new.USERNAME:=USER;
end;

ChrisF's user avatar

ChrisF♦

134k31 gold badges254 silver badges325 bronze badges

answered Apr 17, 2014 at 17:07

user3546225's user avatar

Источник

totn Oracle Error Messages

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

Description

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

  • ORA-01400: cannot insert NULL into («SCHEMA».»TABLE_NAME».»COLUMN_NAME»)

Cause

You tried to insert a NULL value into a column that does not accept NULL values.

Resolution

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

Option #1

Correct your INSERT statement so that you do not insert a NULL value into a column that 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 INSERT statement:

INSERT INTO suppliers
( supplier_id )
VALUES
( 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 insert a NULL value into this field.

You could correct this error with the following INSERT statement:

INSERT INTO suppliers
( supplier_id, supplier_name )
VALUES
( 10023, 'IBM' );

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

Источник

ORA-01400

ORA-01400: обязательная (NOT NULL) колонка пропущена, или произошел NULL во время вставки

Причина:

Когда вы вставляете или изменяете строки, вы не указываете значение для колонки определенной как NOT NULL.

Действие:

Укажите значение для каждой колонки NOT NULL, или измените определение таблицы разрешающее нулевые значения в колонках, которые сейчас определены как NOT NULL.

Источник

ORA-01400 means that there’s a column which was found as NOT NULL is not listed in the INSERT statement, you have to provide a value for it. For example, I tried to insert a row into a table.

SQL> conn hr/hr
Connected.
SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');
insert into hr.countries (country_id, country_name) values ('SE', 'Sweden')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."COUNTRIES"."REGION_ID")

Let’s describe the table’s definition.

SQL> desc hr.countries;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 REGION_ID                                 NOT NULL NUMBER

As you can see, the column is NOT NULL which is one type of constraints to keep data integrity.

Please note that, not only in a normal INSERT, but also in import, SQL*Loader, GoldenGate, Hibernate and Informatica could see ORA-01400 sometimes. Especially for data import which includes imp of original import and impdp of data pump.

As for JDBC exception handling, sometimes the error message may not be so obvious because it’s related to constraint violation signals, but fortunately the error stack that companies with ORA-01400 is mostly the same:

  • java.sql.SQLException
  • java.sql.SQLNonTransientException
  • java.sql.SQLIntegrityConstraintViolationException

Solutions to ORA-01400

Now, we can have 3 choices to solve ORA-01400.

  1. Put the Column on the List of INSERT
  2. Remove NOT NULL constraint from the Column
  3. Provide a default Value for the Column

Put the Column on the List of INSERT

You have to modify the statement and provide a proper value to it at run-time.

SQL> insert into hr.countries (country_id, country_name, region_id) values ('SE', 'Sweden', 1);

1 row created.

It’s successful. Now we have to revert the operation for later steps.

SQL> rollback;

Rollback complete.

I know, sometimes, you don’t have any proper value of this column. So you can go for the next choice.

Remove NOT NULL constraint from the Column

You can either disable the constraint or drop it, but the drawback is that you have to change the definition of the table, the data integrity may be compromised.

Disable the constraint

Let’s check which constraint should be disabled from the column.

SQL> column constraint_name format a25;
SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME           STATUS
------------------------- --------
SYS_C007819               ENABLED

Then we disable the constraint by ALTER TABLE DISABLE CONSTRAINT.

SQL> alter table countries disable constraint SYS_C007819;

Table altered.

Check the status again.

SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME           STATUS
------------------------- --------
SYS_C007819               DISABLED

SQL> desc countries;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 REGION_ID                                          NUMBER

As you can see, the constraint was removed, OK, just temporarily.

Drop the constraint

To remove the constraint permanently, you can drop it by ALTER TABLE DROP CONSTRAINT.

SQL> alter table countries drop constraint SYS_C007819;

Table altered.

SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C';

no rows selected

Dropping a NOT NULL constraint can be easier than the above statement. I guess you’d like to know more ways to add or drop a NOT NULL constraint.

No matter you disable or drop it, you can insert the row now.

SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');

1 row created.

It’s successful. Now we have to revert the operation for later steps.

SQL> rollback;

Rollback complete.

SQL> alter table countries modify (region_id not null);

Table altered.

Provide a default Value for the Column

This could be the best solution to ORA-01400.

SQL> alter table hr.countries modify (region_id default 1);

Table altered.

SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');

1 row created.

The best thing is that you don’t have to modify INSERT statements or remove NOT NULL constraints from the column. The business logic stays stable.

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка out of video memory trying to allocate
  • Ошибка origin seems to be running no communication
  • Ошибка ora 00979 not a group by expression
  • Ошибка out of range что делать
  • Ошибка orangeemu64 dll unravel two