I try to insert date form oracle to postgresql
my database date format is dd/mm/yyyy
postgresql date format yyyy/mm/dd
insert into "public"."test"@PG_LINK
select 'test' name, TO_DATE (TO_CHAR(SU.DATE_OF_BIRTH, 'yyyy/mm/dd'),'yyyy/mm/dd') test_date
from MOH.HEAC_STUDENT_DATA su
but I got this error
ORA-02070: database PG_LINK does not support in this context
Oracle Database stores dates in its own internal format. Whether it's shown as dd/mm/yyyy or yyyy-mm-dd is purely a client-side setting:
alter session set nls_date_format = 'dd/mon/yyyy';
select sysdate from dual;
SYSDATE
05/feb/2018
alter session set nls_date_format = 'yyyy-mm-dd';
select sysdate from dual;
SYSDATE
2018-02-05
So there's no need for the to_char/to_date conversion. Remove these from your SQL!
That said, Oracle Database dates
include the time. PostgreSQL dates
exclude the time. So you may still have conversion issues.
If this doesn't help check you've got the ODBC Gateway configured correctly:
https://docs.oracle.com/cd/E11882_01/gateways.112/e12013/toc.htm