Oracle Database Discussions

After much "finangling" I think I have my dg4msql link working good between Oracle and MSSQL.

Now, what I am trying to do, is populate a table in MSSQL with certain column values from an Oracle table whenever a new row is inserted into the Oracle table. I am using a very simple trigger on the oracle table that basically goes like this:

mware is dblink to MSSQL database, oracle col is DATE and mssql col is DATETIME

here is the trigger: (edited to display only the column in question)
==================================================================================
CREATE or replace TRIGGER <oracleowner>.INSERT_INTO_NVDB
AFTER INSERT ON <oracleowner>.<oracletable> for each row
BEGIN
insert into "<mssqlowner>"."<mssqltable>"@mware
(
"load_date_time",
)
VALUES(
:new.loading_date,
);

END;
==================================================================================

Now, when entering a record into the Oracle table the trigger fires ok, but does not insert a date into the MSSQL table, the "load_date_time" col in mssql table can have nulls so I guess it just inserts a null value.

When I enable triggers on the MSSQL table, which then takes these values and inserts them into another table along with values from other tables, the insert into the Oracle table fails because the trigger on the MSSQL database is trying to pass the date (which is blank) from the MSSQL table into another table where the datetime field is not null.

I am assuming this is because the Oracle date format it not compatible with MSSQL, which I believe is "yyyy-mm-dd hh:mm:ss" - so I have to either convert the default Oracle date format to match the MSSQL one, or convert it to a char value within the trigger. (The developer on the MSSQL side said if I can convert it to a char value that should be ok too)

I am thinking that leaving it in date format is probably preferable, would the best way to do this be to put an "alter session set nls_date_format= " at the start of the trigger?

Can I convert the date to a char format within the trigger easily? If there is a better way of doing this than the way I'm approaching it I'm all for it..

thanks!