Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

insert statement with new 23c values_clause fails with ORA-00932 and default-on-null clause

Philipp SalvisbergJan 11 2024 — edited Jan 11 2024

To reproduce run the following script:

drop table if exists t;
create table t (
   id number generated always as identity not null,
   c1 number default on null 0 not null
);
insert into t(c1) values (null),(1);

This will produce the following error:

insert into t(c1) values (null),(1)
*
ERROR at line 1:
ORA-00932: expression is of data type -, which is incompatible with expected
data type NUMBER
Help: https://docs.oracle.com/error-help/db/ora-00932/

IMO this is a bug in the Oracle Database 23c Free Edition (23.3).

Workarounds:

-- a) insert based on subquery
insert into t(c1)
select null union all select 1;

-- b) insert based on subquery using values_clause
insert into t(c1)
select * from (values (null), (1)) as t2 (c1);

-- c) single row inserts
insert into t(c1) values(null);
insert into t(c1) values(1);

-- d) on a table without "default on null" clause
alter table t modify (c1 default null);
insert into t(c1) values (null),(1);
This post has been answered by Chris Saxon-Oracle on Jan 15 2024
Jump to Answer
Comments
Post Details
Added on Jan 11 2024
2 comments
123 views