I have an Oracle hs database link set up between SQL Server 2012 and Oracle 11g.
When Selecting date columns in Oracle from the SQL Server database the date comes through fine but as soon as it has to pass through any function then the date gets cropped from 10 to 5 characters.
For example:
Select Input_Date from schema.table@Database
would return 2000-08-18
Select Len(Input_Date) from schema.table@Database
would return 5
(Select Input_Date from schema.table@Database1
Union
Select Input_Date from schema.table@Database2)
would return 2000-
I am at a loss at what to do, at first the select statement also returned 2000- but then I changed the NLS_DATE_FORMAT parameter which allows a view of the full date in a select statement but has not fixed any of the other issues.
When I select dump(input_date,1016) from schema.table@Database I get
Typ=1, Len=10, CharacterSet=AL16UTF16.
I would really appreciate some help as there seems to be very little information about this online.
Many thanks.