Oracle 9i and up provide you with a new keyword: DEFAULT. This means you can get to the default values of a column when doing an insert or update.

I have tried some things with this on my 10G XE database and I have come up with the following script to test it all (a bit).

DROP TABLE customer;
— Create table
create table customer
(
id number(8),
name varchar2(50)
)
;
— Add comments to the columns
comment on column customer.id
is 'Customer ID';
comment on column customer.name
is 'Customer Name';
— Create/Recreate primary, unique and foreign key constraints
alter table customer
add constraint pk_customer primary key (ID);
— create the sequence
CREATE SEQUENCE customer_id START WITH 1 NOCACHE;
— add a couple of customers
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Patrick');
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Dana');
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Quinty');
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Kayleigh');
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Mitchell');
INSERT INTO customer(ID, name) VALUES (customer_id.NEXTVAL, 'Steven');
SELECT * FROM customer;
— now add a new column with a default
alter table customer add location varchar2(50);
SELECT * FROM customer;
— Add/modify columns
alter table CUSTOMER modify LOCATION default 'Almere';
— Add comments to the columns
comment on column customer.location
is 'Customer Location';
— now check the values
SELECT * FROM customer;
— the column has *NOT* been filled with the default.
— Now update Steven
UPDATE customer SET customer.LOCATION = 'Chicago' WHERE NAME = 'Steven';
SELECT * FROM customer;
— update the empty columns with the default value
UPDATE customer SET customer.LOCATION = DEFAULT
WHERE customer.LOCATION IS NULL;
SELECT * FROM customer;

When you run this script, you can see what the DEFAULT keyword does.

I have found that the default keyword cannot be used in the nvl function. At least not on my instance ;-). I still have to try how it works in PL/SQL code, like in triggers, but usually, stuff that doesn't work in SQL, doesn't work in PL/SQL either.

I can see use for this keyword in update scripts, after you alter a column to have a default, while there is already data in the table. But it might also help, to prevent NULL values in the table, even without a NOT NULL constraint.

All in all, it seems like a nice addition to the language, which I need to get in the back of my head when doing PL/SQL development. I know I am still much of an Oracle 7 developer, because that's where my PL/SQL development 'career' started.