Thanks for test script - always makes our lives easier. I've replicated what you see on my database

SQL> drop table test purge;

Table dropped.

SQL>
SQL> create table test(name varchar2(20) not null,age varchar2(30) not null);

Table created.

SQL>
SQL> insert /*+append*/ into test(name,age) select '1','2' from dual where 1=2;

0 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> insert /*+append*/ into test(name) select '1' from dual where 1=2;
insert /*+append*/ into test(name) select '1' from dual where 1=2
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ASKTOM"."TEST"."AGE")


SQL> commit;

Commit complete.

I'll offer a hypothesis as to what might be happening.

With direct mode insert, rather than inserting rows, Oracle is preparing entire blocks to be written to the datafiles. I would suspect, that in the process of preparing the block, it is examining the nullability of the columns to be populated and deciding at the point that the AGE column will not be provided by the SQL and thus errors out.

In reality, it probably *should* be making that decision *after* discovering if any rows are coming into the insert at all.

An interesting find by you, I must say. A potential workaround would be:

SQL> insert /*+append*/ into test(name,age) select '1',null from dual where 1=2;

0 rows created.

Interestingly, the documentation has this statement:


If any omitted column has a NOT NULL constraint and no default value, then the database returns an error indicating that the constraint has been violated and rolls back the INSERT statement


so it could be argued that the direct-mode insert is the more correct one ...

Hope this helps.