Good Morning,
We have completed an Oracle gateway for SQL Server installation and tested our Oracle Forms and Reports using the Gateway. We have successfully validated that the query is returning data from the SQ Server via the Gateway.
There is one issue that we have not been able to resolve despite the research.
When a query having a TO_CHAR function in it is executed in the gateway (TO_CHAR(DateValue, mask) , we receive an ORA-01722 - Invalid Number. This same query worked fine in Oracle previously.
Our init file is as follows:
# HS init parameters
HS_FDS_CONNECT_INFO=[ec-k212hrp-db]/IPPSSQLSERVER/WRDSB
HS_FDS_TRACE_LEVEL=ON
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_NLS_DATE_FORMAT="yyyy-dd-mm"
The data is fine in SQL Server, the column in question is not null with valid date values.
We have tried various HS_NLS_DATE_FORMAT="yyyy-dd-mm" values with no success. Would anyone have any further ideas to try or research further?
Many Thanks
Rob