{"id":46,"date":"2006-05-19T20:48:21","date_gmt":"2006-05-19T19:48:21","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=46"},"modified":"2006-05-20T08:29:13","modified_gmt":"2006-05-20T07:29:13","slug":"defaults","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=46","title":{"rendered":"Defaults"},"content":{"rendered":"

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

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).<\/p>\n

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

When you run this script, you can see what the DEFAULT<\/strong> keyword does.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-46","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/46","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=46"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/46\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}