Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

12c Database : Default Values enhancements

Prior to 12c, if the developer wants to create a sequential ID number generator , he/she has to use a sequence and then a trigger or reference that seq value in the insert statement.

From 12c Onwards, this has been enhanced by providing the sequence.nextval in the table definition itself.

Further it even enhanced that without sequence you can now generate a sequential number with identity columns, where this kind of feature available in MS-SQL etc.

 

Let’s see the default value enhancements.

	SQL> create sequence t1_seq; 
	Sequence created. 

	SQL> create table t1 (id number default t1_seq.nextval, name varchar2(20)) tablespace users; 
	Table created. 

	SQL> insert into t1(id,name) values(1,'Geek DBA'); 
	1 row created. 

	SQL> insert into t1(name) values('Ramesh'); 
	1 row created. 


	SQL> insert into t1(id,name) values(NULL,'Brijest'); 
	1 row created. 

	SQL> select * from t1;

		ID NAME
	---------- --------------------
		 1 Geek DBA
		 1 Ramesh
		   Brijesh

	SQL>

As you see there we have inserted the rows with ID, without ID and with NULL and if you observe the output, ID populates

	1) For first insert, the statement has been inserted as is
	2) For second insert, Used sequence.nextval and inserted 1 in ID column
	3) For third insert, as we used NULL for ID, NULL has been inserted.

In addition to above we can use explicitly sequences where NULL has been provided like for second case above. Let test with what happened when nulls added, as I have

	a) Created a sequence
	b) Add a column to the table and made that column default value to a sequece, note the difference is 
	   default on null (this is new)
	c) First insert having all columns and inserted as is
	d) Second insert with no sequences appended
	e) Third insert wiht null values, as you see the ID1 column is not populated but the ID2 

with default on null sequence is populated with value

	SQL> truncate table t1;

	Table truncated.

	SQL> create sequence t1_seq_on_null;

	Sequence created.

	SQL> alter table t1 add id2 number default on null t1_seq_on_null.nextval;

	Table altered.

	SQL> insert into t1(id,name,id2) values(1,'Geek DBA',101);

	1 row created.

	SQL> insert into t1(name) values('Geek DBA');

	1 row created.

	SQL> insert into t1(id,name,id2) values(null,'Geek DBA',null);

	1 row created.

	SQL> commit;

	Commit complete.

	SQL> select * from t1;

		ID NAME                        ID2
	---------- -------------------- ----------
		 1 Geek DBA                      101
		21 Geek DBA                        1
		   Geek DBA                        2

Comments are closed.