Oracle Database Discussions

What is "DateValue"? I assume it is a column in the source SQL Server database. Can you post it definition? If it is a date then the problem should be there...

Yes, it is a date. However there are no NULLS and the dates convert fine if I execute a CONVERT function against the column in SQL Server.

Sorry, but I do not understand, if it IS a date why are you converting it? Can you confirm the data type in SQL Server? If it is not a date, you may want to create a view on SQL Server that handles the conversion and run your remote select against that view...

Thanks Fernigrini. I certainly agree with you. Within the Oracle Reports code, previous developers wrote TO_CHAR(DateColumn, 'YYYY-MON-DD') to format the output. This is within a few hundred reports (feel my pain) . As you may know, removing this from the queries within Oracle Reports is very tedious. As it works in Oracle, we expected that using the gateway to SQL Server with the same data, it would be fine.

The original data in Oracle was DATE and the column in SQL is the same data in DATE format.

To complicate things, we are planning an upgrade to redevelop everything within 1-2 years, but hoped we could leverage the Gateway in the meantime.

It seems that Oracle is not understanding the column as actually being a Date, so it tries to implicitly convert it to a date before converting it to char.

HS_NLS_DATE_FORMAT="yyyy-dd-mm"

That is an AWFUL date format mask! Don't use it, ever. To an innocent bystander 2019-04-05 looks to be ISO format date 5th April 2019. The above erroneous format mask would make it 4th May 2019. Ugh.

As for your actual issue please share a SIMPLE example of a query that fails and the result of

SQL> desc your_table@yourdg40dbc_link

We migrated the data from Oracle to SQL Server, and want to still use our Oracle Forms and Reports in Oracle.

We have a Public database link that the public synonyms use to access the data on the Gateway.

If I remove the to_char function, it works. BUT we would have to do this to many hundreds of reports.

SELECT TO_CHAR(SEN_SERV_ELIG_START_DATE,'YYYY-MM-DD') ORIGINAL_START_DATE

FROM   EC_EMPLOYEE_SEN_SERV

ORA-01722: invalid number

01722. 00000 -  "invalid number"

*Cause:    The specified number was invalid.

*Action:   Specify a valid number.

desc EC_EMPLOYEE_SEN_SERV

Name                     Null     Type        

------------------------ -------- ------------

EMPLOYEE_ID              NOT NULL VARCHAR2(9) 

EMP_GROUP_CODE           NOT NULL VARCHAR2(8) 

SEN_SERV_CODE            NOT NULL VARCHAR2(8) 

SEN_SERV_VALUE           NOT NULL NUMBER(9,3) 

PAY_NUMBER                        VARCHAR2(8) 

CURRENT_SEN_SERV_VALUE            NUMBER(9,3) 

TIE_BREAKER                       NUMBER(12,4)

SCHOOL_LEVEL                      VARCHAR2(8) 

COUNTRY_CODE                      VARCHAR2(8) 

PROVINCE_CODE                     VARCHAR2(8) 

SEN_SERV_ELIG_START_DATE NOT NULL DATE        

SEN_SERV_CALC_DATE       NOT NULL DATE        

Thanks for the info.

What is the OS the gateway is running on?

Are you using dg4odbc or dg4msql for the Oracle gateway?

What are the DSN settings that are being used to connect to sqlserver via Oracle db_link?

In my testing I used dg4odbc and everything works fine, even using your dodgy date format mask of 'yyyy-dd'mm':

Oracle dg4odbc 11.2.0.2.0

Microsoft SQL Server Native Client 11.0

SQLExpress 2016

When I look at the column definition in SQL Server, it is date. Within the studio, my select statement returns numeric values as expected. I can issue a convert statement with no errors. It seems to me that it may be the data that was converted from Oracle?

And what is the answer to the further 3  questions:

What is the OS the gateway is running on?
Are you using dg4odbc or dg4msql for the Oracle gateway?
What are the DSN settings that are being used to connect to sqlserver via Oracle db_link?

?

Right. Apologies for that.

What is the OS the gateway is running on?  - Windows Server 2012 R2

Are you using dg4odbc or dg4msql for the Oracle gateway?  dg4msql

What are the DSN settings that are being used to connect to sqlserver via Oracle db_link?  I can return data for all tables. I created public synonyms for all objects that use the DBlink to the Gateway

DG4MSQL  =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=tcp)(HOST=<testserver>)(PORT=1528))

    (CONNECT_DATA=(SID=DG4MSQL))

  )

  )

TESTGW =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = <testserver>)(PORT = 1528))

    )

    (CONNECT_DATA = (SERVICE_NAME = DG4MSQL)

    )

  )

SQL Server Database collation is SQL_Latin1_General_CP1_CI_AS

Try to replicate the situation.

CREATE TABLE [dbo].[EC_EMPLOYEE_SEN_SERV](

    [SEN_SERV_ELIG_START_DATE] [datetime] NULL

) ON [PRIMARY]

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2016-12-27T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2016-07-19T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2011-07-03T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2016-12-05T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2017-03-08T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2017-05-06T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2016-12-14T00:00:00.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2012-06-18T10:34:09.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (CAST(N'2017-07-17T22:37:07.000' AS DateTime))

INSERT [dbo].[EC_EMPLOYEE_SEN_SERV] ([SEN_SERV_ELIG_START_DATE]) VALUES (NULL)

Query

select to_char(SEN_SERV_ELIG_START_DATE,'YYYY-MM-DD')

from EC_EMPLOYEE_SEN_SERV@serversql

Result:

to_char(SEN_SERV_ELIG_START_DATE,'YYYY-MM-DD')

2016-12-27

2016-07-19

2011-07-03

2016-12-05

2017-03-08

2017-05-06

2016-12-14

2012-06-18

2017-07-17

The conversion result has worked,

I remember that sometimes it is the date transformation, try to create a view from SQL Server and then consume it or use synonymous

CREATE VIEW [dbo].[VIEW_EC_EMPLOYEE_SEN_SERV] AS SELECT

  SEN_SERV_ELIG_START_DATE,

  left(convert(varchar, SEN_SERV_ELIG_START_DATE, 23),10) SEN_SERV_ELIG_START_DATE_VIEW

FROM EC_EMPLOYEE_SEN_SERV

select  to_char(SEN_SERV_ELIG_START_DATE_VIEW,'YYYY-MM-DD')

from VIEW_EC_EMPLOYEE_SEN_SERV@serversql

ORA-01722: invalid number

01722. 00000 -  "invalid number"

*Cause:    The specified number was invalid.

*Action:   Specify a valid number.

select  SEN_SERV_ELIG_START_DATE_VIEW

from VIEW_EC_EMPLOYEE_SEN_SERV@serversql

Result:

SEN_SERV_ELIG_START_DATE_VIEW

2016-12-27

2016-07-19

2011-07-03

2016-12-05

2017-03-08

2017-05-06

2016-12-14

2012-06-18

2017-07-17

We create a view;

create view EC_EMPLOYEE_SEN_SERV as

select to_date(SEN_SERV_ELIG_START_DATE_VIEW,'YYYY-MM-DD') SEN_SERV_ELIG_START_DATE_VIEW

from VIEW_EC_EMPLOYEE_SEN_SERV@serversql

Query;

select to_char(SEN_SERV_ELIG_START_DATE_VIEW,'YYY-MM-DD')

from EC_EMPLOYEE_SEN_SERV

Result;

016-12-27

016-07-19

011-07-03

016-12-05

017-03-08

017-05-06

016-12-14

012-06-18

017-07-17

In certain measures I prefer the view than a synonym.

Regards

Choc!