Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…

The Good Old Ways

Say we have a requirement for a table to hold details of gadgets through the ages.
This table has been modelled with a synthetic key, and also a couple of audit columns so we can track when a row was created and by whom.
Traditionally, the code to fulfill this requirement would follow a familiar pattern.

The table might look something like this :

create table gadgets
(
    id number constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default user,
    creation_date date default sysdate
)
/

NOTE – you’d normally expect to see NOT NULL constraints on the CREATED_BY and CREATION_DATE columns. I’ve left these off for for the purposes of the examples that follow.

We’ll also want to have a sequence to generate a value for the id…

create sequence gad_id_seq
/

As it stands, this implementation has one or two issues…

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

The first problem becomes apparent when we query the table after these inserts…

SQL> select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   1 Dial-Up Modem	  MIKE		       31-AUG-14
   2 Tablet Computer	  MIKE		       31-AUG-14
   3 Netbook

Yes, although the insert was successful for the Netbook row, the explicit specification of CREATED_BY and CREATION_DATE values as NULL has overidden the default values defined on the table.

What’s more, there’s nothing enforcing the use of the sequence to generate the ID value. This becomes a problem when we go to do the next insert…


-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

insert into gadgets(id, gadget_name, created_by, creation_date)
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.DINO_PK) violated

Because we didn’t use the sequence for the previous insert, it’s still set to the value it had after it was last invoked…


SQL> select gad_id_seq.currval from dual;

   CURRVAL
----------
	 3

The traditional solution to these problems is, of course, a trigger…

create or replace trigger gad_bir_trg
    before insert on gadgets
    for each row
    --
    -- Make sure that :
    --  - id is ALWAYS taken from the sequence
    --  - created_by and creation date are always populated
begin
	:new.id := gad_id_seq.nextval;
    :new.created_by := nvl(:new.created_by, user);
    :new.creation_date := nvl(:new.creation_date, sysdate);
end;
/

Now, if we re-run our insert…


insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted


SQL> select * from gadgets where gadget_name = 'Smart Phone';

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   5 Smart Phone	  MIKE		       31-AUG-14

Yes, even though we’ve invoked the sequence in the INSERT statement, the trigger invokes it again and assigns that value to the ID column ( in this case 5, instead of 4).
Reassuringly thought, the CREATED_BY and CREATION_DATE columns are now populated.

So, in order to fulfill our requirements, we need to create three database objects :

  • A table
  • a sequence
  • a DML trigger on the table

Or at least, we did….

12c – the Brave New World

Oracle Database 12c introduces a couple of enhancements which will enable us to do away with our trigger completely.
First of all…

Changes to Default Values Specification

You can now specify a default value for a column that will be used, even if NULL is explicitly specified on Insert.
Furthermore, you can now also use a sequence number as a default value for a column.

If we were writing this application in 12c, then the code would look a bit different….


create sequence gad_id_seq
/

create table gadgets
(
    id number default gad_id_seq.nextval 
        constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user,
    creation_date date default on null sysdate
)
/

We’ve dispensed with the trigger altogether.
The ID column now uses the sequence as a default.
The CREATED_BY and CREATION_DATE columns will now be populated, even if NULL is explicitly specified as a value in the INSERT statement….


-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/




  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

Whilst we can now guarantee that the CREATED_BY and CREATION_DATE columns are populated, we are still left with one issue, or so you might think…

-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted

That’s odd. You’d think that the sequence NEXTVAL would be 3, thus causing the same error as before. However…

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
  21 Smart Phone	  MIKE		 31-AUG-14

Hmmm. Let’s take a closer look at the sequence…

select min_value, increment_by, cache_size, last_number
from user_sequences
where sequence_name = 'GAD_ID_SEQ'  
/

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
	 1	      1 	20	    41

Yes, it looks like, in 12c at least, the default for sequences is a cache size of 20.
If we wanted to create the sequence in the same way as for 11g ( i.e. with no caching), we’d need to do this :

create sequence gad_id_seq
    nocache
/

We can now see that the sequence values will not be cached :

PDB1@ORCL> select cache_size
  2  from user_sequences
  3  where sequence_name = 'GAD_ID_SEQ'
  4  /

CACHE_SIZE
----------
	 0

All of this is a bit of an aside however. The fact is that, as it stands, it’s still quite possible to by-pass the sequence altogether during an insert into the table.
So, we still need to have a trigger to enforce the use of the sequence, right ?
Well, funny you should say that….

Identity Column in 12c

Time for another version of our table. This time however, we’re dispensing with our sequence, as well as the trigger…

create table gadgets
(
    id number generated as identity 
		constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

Let’s see what happens when we try to insert into this table. Note that we’ve modified the insert statements from before as the sequences does not exist ….

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/


-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( null, 'Tablet Computer')
/

The first statement succeeds with no problem. However, the second and third both fail with :

ORA-32795: cannot insert into a generated always identity column

We’ll come back to this in a bit.

In the meantime, if we check the table, we can see the ID column is automagically populated….

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14

Oh, it’s just like being on SQL Server.

How is this achieved ? Well, there are a couple of clues.
First of all, executing the create table statement for this particular version of the table requires that you have the additional privilege of CREATE SEQUENCE.
A further clue can be found by looking once again at USER_SEQUENCES…

select sequence_name, min_value, increment_by, cache_size, last_number
from user_sequences
/

SEQUENCE_NAME	      MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------- ---------- ------------ ---------- -----------
ISEQ$$_95898		      1 	   1	     20 	 21

If we have a look at the column details for the table, we get confirmation that this sequence is used as the default value for the ID column :

  1  select data_default
  2  from user_tab_cols
  3  where table_name = 'GADGETS'
  4* and column_name = 'ID'
PDB1@ORCL> /

DATA_DEFAULT
--------------------------------------------------------------------------------
"MIKE"."ISEQ$$_95898".nextval


It’s worth noting that this sequence will hang around, even if you drop the table, until or unless you purge the table from the RECYCLEBIN.

If you prefer your sequences to be, well, sequential, the good news is that you can use the Sequence Creation syntax when specifying an identity column.
The change in the default number of values cached for sequences created in 12c, compared with 11g and previously, may lead you to consider being a bit more specific in how you create your sequence, just in case things change again in future releases.

Here we go then, the final version of our table creation script….

 create table gadgets
(
    id number generated always as identity
    (
        start with 1
        increment by 1
        nocache
        nocycle
    )
    constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

As we saw earlier, the INSERT statements for this table, now need to change. We can either specify “DEFAULT” for the ID column :

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/

…or simply omit it altogether…

insert into gadgets(gadget_name, created_by, creation_date)
values('Smart Phone', user, sysdate)
/

And, of course, we can also omit the values for the other defaulted columns should we choose….

insert into gadgets(gadget_name)
values('Netbook')
/

If we check the table after these statements, we can see that all is as expected :


select * from gadgets
/

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

As with a “traditional” table/sequence/trigger setup, an erroneous INSERT will cause a gap in the sequence…

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
/

values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
                                   *
ERROR at line 2:
ORA-12899: value too large for column "MIKE"."GADGETS"."CREATED_BY" (actual:
48, maximum: 30)

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', default, default)
/

select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
   5 Psion Series 5	  MIKE		 31-AUG-14

Conclusion

While we can see that 12c hasn’t done away with sequences altogether, it is fair to say that they are now a lot more unobtrusive.
As for the good old DML trigger ? Well, they’ll still be with us, but they may well be a little lighter on the mundane default handling stuff we’ve been through in this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s