Hi,
I am trying to migrate a huge amount of data, I am trying to figure out a way to do it. How would you insert on oracle and use the default value if you are inserting a null on a column that has a default value?
For example:
create table foo(
bar integer default 1 not null
)
insert into foo (bar) values (null)
I know that not putting a value null into the column will get the default value. The problem is the insert statement coming from mysql is already done.
Based from my simple research, Tom Lane of PG said that Oracle works this way (or maybe used to) and SQL 92 should not work with this way. There seems to be a feature in 9i that you can use a default keyword if you want to use the default value. But the target db is 8i :(
I tried using a before insert trigger, does not seem to work as the not null constraint gets fired first.
I am thinking of disabling the not null constraint, migrate the data, then update the data so null values will be set to default. Is there a better way of doing this? Another option is populating a temp table then using insert select and nvl combo populate the real table.
Thanks.
