How to Insert Nothing into a Table in Oracle

Suppose you needed to create a record in a table just to generate an id field which is automatically generated (as an identity column). An example of why you’d want to do such a thing is if you need to generate invoices. All the invoices that are generated at the same time should be part of the same invoice_batch. Before creating an invoice, the parent record (invoice_batch) needs to be created.

So how do you insert a record into a table with no values? The follow examples shows how to do this in Oracle:

Table Setup

1
2
3
4
create table invoice_batch (
invoice_batch_id number generated always as identity not null
-- ... (additional columns)
);

Examples

Suppose you want to insert a record into the invoice_batch table just to get an invoice_batch_id it could look like:

1
2
3
4
5
insert into invoice_batch;

-- Error
SQL Error: ORA-00926: missing VALUES keyword
00926. 00000 - "missing VALUES keyword"

The above code raises an error as Oracle requires a values clause. But what do you put in there? If null is used the following happens:

1
2
3
4
5
6
insert into invoice_batch(invoice_batch_id)
values(null);

-- Error
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 - "cannot insert into a generated always identity column"

To get around this you need to use the keyword default instead of null:

1
2
3
4
5
insert into invoice_batch(invoice_batch_id)
values(default);

-- Success
1 row inserted.

In our case it would be recommended to have a returning into clause to get back the invoice_batch_id to be used elsewhere.