Re: Question about insert and defaut values

Eugenio Reis wrote
> I'm insisting on a positional approach because many Insert statements
> I had on SQL Server (a VB program) use that approach and I have no
> time to specify columns. I tested:
>
> INSERT INTO TABLE
> VALUES ( 'AnyName', , 'AnyValue' )

This indeed is not the same. You should really not mention the column when you want Oracle to use the default. I guess you need to write a simple trigger that changes NULL (or any keyword you like) into the default. Note that this is not the same behavior as a table level default. In the latter, when you insert NULL into a column that has a default, NULL is still inserted, not the default. In this trigger either specifying NULL or nothing at all both yield the default.:

    create or replace trigger bi_mytable     before insert on my mytable
    for each row
    begin

        :new.my_column =nvl(:new.my_column, 'my default value');     end bi_mytable;
    /
    show errors

If you insist on taking the default out of the table definition instead of hardcoding it in the trigger, then you could use something like:

        if :new.my_column is null then
            select data_default
            into :new.my_column
            from user_tab_columns
            where table_name = 'MY_TABLE'
            and column_name = 'MY_COLUMN';
        end if;

Not too nice, in my opinion, but it may save you some redesign time.

> In SQL Server I could write
> INSERT INTO TABLE VALUES ( 'Any name', default )

So, maybe you can change your code and global replace the word default with the word null.

By the way: note that positional approach is not to be preferred either. When you add a column (using alter table, the column will be added as the last column), or change the installation scripts (where you might put the new column in another position), the column order in two databases may be different!

Arjan. Received on Sun Mar 21 1999 - 10:23:53 CST