CODEWITHSUNDEEP
oracle11gdefault-valueoracle9i
Goulutor
Goulutor

Reputation: 378

Oracle 9.2 migrated to 11g - Default values

We recently migrated from Oracle version 9.2 to 11g. But some bugs appeared with the application using the database. We get the following exception:

ORA-01400: cannot insert null into...

I looked for information about the error and especially about some new features in version 11g that might be the cause.

And here I'm. I know that the old way of defining table columns with default values could not work, but I don't why, and more important how to resolve the issue.

Here's the definition of the table I can see using SQL Developer:

COLUMN_NAME         DATA_TYPE       NULLABLE      DATA_DEFAULT   COLUMN_ID
--------------------------------------------------------------------------
...
REP_DOC_RECEIVED    CHAR(1 BYTE)    No            'n'            12 
...

When I try to insert a row with a null value for that column, the exception is thrown.

Upvotes: 1

Views: 852

Answers (1)

GWu
GWu

Reputation: 2787

A default value only applies, when no value is provided in the insert statement. So I guess your insert makes some insert into ... values ( ... , NULL, ...).

See for example http://sqlfiddle.com/#!4/cd58c/7 to show the difference:

-- ok
insert into a (n,REP_DOC_RECEIVED) values (1,'A');

-- ok, default is applied    
insert into a (n) values (1);

-- not ok, NOT NULL constraint violated    
insert into a (n,REP_DOC_RECEIVED) values (1,'');

You have to do either NVL(...,'n') in your insert statement or to leave out the column. And no, this didn't change in 11g.

Upvotes: 1

Related Questions