It is a new feature in 12c – now you can use sequence while creating a column.

In the example below, the sequence wasn’t utilized because the first parameter inserted in VALUES clause was ” (or NULL in other words)

So, in order to utilize this new 12c feature, we must have written our query as

This is how it is designed to work. If you use just DEFAULT seq.NEXTVAL without specifying ON NULL, then sequence won’t be engaged and NULL will be inserted

DEFAULT ON NULL means DEFAULT value will be inserted even when NULL is explicitly used in INSERT statement

Now let’s have a look how DEFAULT ON NULL will play in combination with the sequence.

Despite DEFAULT ON NULL seq.NEXTVAL was used, you can’t insert a row by not specifying explicitly the column (first insert statement). You have to insert either NULL through your SQL statement (second insert statement),  either single quotes ” (third insert statement), which will be translated to NULL anyway.


Leave a comment

Your email address will not be published. Required fields are marked *