Updating Date/Time value in an SQL Server table from Oracle

0
votes

From Oracle, via a DBLINK, I am trying to update the value in a date field in an MS-SQL Server database.

However when I run this query 

update axp@mis
SET "ExpireDate" = '2013-06-04 12:00:00'
WHERE "IdNumber" = 103;

I get the message:

[Error] Execution (13: 16): ORA-28534: Heterogeneous Services preprocessing error

I have also tried

update axp@mis
SET "ExpireDate" = to_date('2013-06-04 12:00:00','yyyy-mm-dd HH24:MI:SS')
WHERE "IdNumber" = 103;

and I get the message:

[Error] Execution (14: 1): ORA-02070: database MISDOORACCESS does not support TO_NUMBER in this context

Can anyone advise how I can perform this update from Oracle?

1
sql-serveroracledate
Can you SELECT from the table, i.e. are you sure that the dblink itself is configured and working correctly? Have you tried assigning the value to an Oracle date variable and updating using it (to avoid date formatting issues)? And what versions of Oracle and SQL Server do you have? - Pondlife
I believe there was a network problem which, when fixed, also fixed my problem. - user2451523

1 Answers

1
votes

You have to use the CAST function. Try this:

declare
     v_date date:=to_date('2013-06-04 12:00:00','yyyy-mm-dd HH24:MI:SS');
begin
     update axp@mis set "ExpireDate"=cast(v_date as date) WHERE "IdNumber" = 103;
     commit;
end;