ORA-01400

ORA-01400: cannot insert null into (string) is a specific error to the null value in Oracle. According to Oracle, a null value can appear in columns of any datatype as long as it is not set to the value “not null” or is restricted by primary key integrity constraints. Empty strings or those with a character value with a length of zero is not the same as a null value and should not be treated as one. Click here to read more about nulls from official Oracle documentation. Although is a straightforward error to correct, having a full understanding of the null value will help the user practice efficient programming techniques and avoid ORA-01400: cannot insert null into (string) in the future.

 

The Problem

The error occurs when you try to insert a NULL value in a column that does not accept NULL values. You will see the following:

ORA-01400: cannot insert NULL into (string)

This means that the data cannot be set to a null value; it must have some value other than null for the query to run successfully. You can see if a table will allow null values by using the SQL*Plus desc command:

SQL>desc table_name

 

The Solution

To correct error ORA-01400: cannot insert null into (string), you must enter a value other than null and rerun the operation.

You may also choose to modify the table itself so that it will allow null values for columns. However, this may not be the best solution as it may not be appropriate for the given table to have a null value.

Take the following example. This is a table called employees and is defined as the following:

 

CREATE TABLE employees

(employee_id not null,

employee_name varchar(30) not null);

 

Given this table, you attempted to execute the following INSERT statement:

 

INSERT INTO employees

(employee_id)

values

(413);

 

If you execute this statement, you would see ORA-01400: cannot insert null into (string). In the INSERT statement, you have entered the employee_id number but no employee_name. Employee_name in the TABLE employees was previously defined as NOT NULL, and therefore, must have a value other than NULL. To correct this, simply write the INSERT statement to include the name of the employee:

 

INSERT INTO employees

(employee_id)

values

(413, ‘Ronnie’);

 

This will resolve the error and run the query.

The second possible solution mentioned previously is to change the table to accept null values. You may do this will an “alter table” statement. As in the previous example with the table called “employees,” run the following statement:

 

Alter table

Employees

Modify

(mycol NULL);

 

This statement should successfully allow the table column values to be null. Use caution when using this option by making sure it actually makes sense to allow null values. In this example, it is likely not a good idea to allow null values. Both employee names and ids are important forms of identification and therefore, should be kept not null in the system.

 

Moving Forward

Being knowledgeable about the null value and when it is appropriate to use is the first step in avoiding seeing errors such as ORA-01400: cannot insert null into (string). It is a good idea to have a solid understanding in the null value as it is something you will use over and over again as a programmer.

If you are still struggling with the error, contact your database administrator. Your DBA will most likely be able to help you with this issue. Although this error does not warrant consultation of an outside professional, you may choose to hire a consultant if you are unable to resolve the issue yourself. Always check certification and credentials to ensure your Oracle needs are met.

STILL HAVE QUESTION?

Submit your question