New Oracle9i explicit defaults   Donald K. Burleson

In Oracle Data Definition Language (DDL), the Oracle DBA has the ability to assign default values to any data column.  This feature removes the tedium of naming all of the column values for every INSERT statement.

Create table

   Student

(

   student_id    number,

   student_rank   varchar2(3) DEFAULT ‘FRESHMAN’

);

When using a default value, the developer would simply omit the default column:

Insert into student (student_id) values ‘Jones’;

In the above SQL it is not clear that the default value of ‘Freshman’ was inserted into the table. Starting in Oracle9i, the DEFAULT keyword can be used to explicit assign the columns default value during any INSERT or UPDATE statement:

insert into

   student

values

   ('jones', DEFAULT);

update

   student

set

   student_rank = DEFAULT

where

   student_id = 12345;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: