nulls into Oracle Oracle

[Darren Reely]

Harold,

If your processing can't wait to change the value after inserting into 
your OO database, then you may want to try triggers against your new OO 
tables.  I only think this is valuable if your transfers are a 
reoccurring action. Not a one shot move from RDB to OO.  For a one shot 
I'd go with Blues suggestion of converting the date after inserting your 
records.

I'm no trigger expert but came up with the following...

CREATE OR REPLACE TRIGGER
my_table_bir
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
        select decode(:new.inactived_date ,'18581117' ,null 
,:new.inactived_date)
                ,decode(:new.active_date ,'18581117' ,null 
,:new.active_date)
        into :new.inactived_date
                , :new.active_date
        from dual;
        :new.inactived_date := :new.inactived_date;
        :new.active_date := :new.active_date;
END;
/

The decode says to place NULL into the :new.inactived_date column if the 
value passed in is 18581117, otherwise keep the value passed in.  Notice 
I can affect more than one column.

NOTE: I've altered my personal date format from the Oracle default.

Darren

P.S.  Use at your own risk. My test used four records.


Johnson, Harold A EDUC:EX wrote:

> Hi all.   I'm using VMS Powerhouse (830d) to convert data directly 
> from an RDB database to an Oracle Oracle database.  When I assign a 
> field to the "OO" database, a NULL value from RDB gets converted to 
> that old "1858/11/17 00:00:00.00" null date.  Is there anyway to force 
> an OO null into the field without checking every single field in RDB 
> for "if ... is null"?????
>  
> If I don't assign anything to a field, then it gets assigned properly 
> to a "~".
>  
>  
> thnx!
>  
>