Mr. Kyte,
I am aware that using the DEFAULT keyword will place the default value for a column if used in the following manner.
create table foo
(
col1 number(*,0),
col2 varchar2(25) default 'standard'
);
insert into foo (col1, col2) values (my_val, DEFAULT);
However, I find myself in a scenario where the user can override the defaults if they wish, however I am usually not privy to which ones they choose to override. I attempted to following to use the DEFAULT value if the value passed in happens to be null, but without success.
insert into foo (col1, col2) values (my_val, nvl(my_val,DEFAULT));
However according to the documentation only an expression OR the default keyword can be used there, not my psuedo-expression I used above. Is there a way to use the column default when a value is null, without having to code the defaults at the table level and in my pl/sql?
No, that is not the way default is spelled out to work in the standard.
Default is used only when the column is left out of the column list in the insert statement OR when explicitly used standalone in the values clause.
If you wanted "nulls" to be defaulted to some value, you would be in the realm of a BEFORE FOR EACH ROW trigger. You would define the column as NOT NULL (because apparently it is :) ) and use a trigger to default the value if set to null.
I would prefer NOT to use a trigger and to use a stored procedure transactional API that "does the right thing" (so the application itself never uses insert, update, delete) if possible.
https://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html details why I think that way. Having "magic" happen like this - my NULL I tried to insert magically disappears and becomes something else - will be the cause of heartburn at some point in the future. It seems like a really "neat" idea but isn't long term.