Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Question about insert and defaut values

Re: Question about insert and defaut values

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Mon, 22 Mar 1999 18:45:37 +0100
Message-ID: <36f681c4$0$15229@pascal>


Eugenio Reis wrote
> INSERT INTO TABLE ('Any Value', default, default )
>
> is the easiest because the keyword 'default' does the work. No
> triggers at all. Is it possible in Oracle?

No.

If you do not want to change your code, then at least you need the trigger. You could create a script that creates a trigger for each table that has columns with a default value. When the triggers are created, all you need to do is global replace DEFAULT with NULL. Or, define a constant DEFAULT in all your scripts, if Oracle does not think it is a reserved word (which I think it is). You do not even have to assign a value to it (it defaults to NULL anyway).

Maybe you can create a package, define a constant DEFAULT in the package, and also define a public synonym DEFAULT for MY_PACKAGE.DEFAULT (if you try that, let us know if you succeeded). However, I would prefer the NULL, as DEFAULT could make someone think that it is a Oracle keyword -- if it is not already...

Again, triggers that check if the value is NULL do not behave exactly like a normal Oracle default would.

Below is some code I once used and editted a bit for you. It has not been tested. Furthermore, I do not know how date type defaults are stored, so you might need to change the code to handle dates correctly.

Arjan.

prompt Creating temporary script to generate before insert triggers

set termout off
spool CreateTriggers.tmp
prompt -- This is a generated script. It may be deleted. declare
  cursor cTables is
    select table_name
    from user_tab_columns
    where data_default is not null;
  --
  cursor cColumns( pTableName varchar2 ) is     select tab.column_name, tab.data_type, tab.data_default     from user_tab_columns tab
    where tab.table_name = pTableName;     --
  procedure WriteLn( pLine in varchar2 ) is   begin
    dbms_output.put_line( pLine );
  end WriteLn;
  --
begin
  for rTable in cTables
  loop

    WriteLn( 'end bi_' || rTable.table_name || ';' );     WriteLn( '/' );
  end loop;
end;
/
spool off
set termout on
set feedback on
spool CreateTriggers.log
prompt Executing temporary script CreateTriggers.tmp start CreateTriggers.tmp
spool off
prompt Completed temporary script CreateTriggers.tmp

You might want to use

    loop

      WriteLn( '-- assign default value if NULL is entered' );
      WriteLn( 'if :new.' || rColumn.column_name || ' is null then' );
      WriteLn( 'select data_default' );
      WriteLn( 'into   :new.' || rColumn.column_name );
      WriteLn( 'from   user_tab_columns' );
      WriteLn( 'where  table_name = ''' || rTable.table_name || '''' );
      WriteLn( 'and    column_name = ''' || rColumn.column_name || ''';' );
      WriteLn( 'end if;' );

    end loop;

You might also want to delete the 'or replace' part to avoid overwriting existing triggers.

Arjan. Received on Mon Mar 22 1999 - 11:45:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US