13 Replies Latest reply on May 6, 2019 4:57 PM by Choc Cac

    ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server

    977699

      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

        • 1. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
          L. Fernigrini

          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...

          • 2. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
            977699

            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.

            • 3. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
              L. Fernigrini

              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...

              • 4. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                977699

                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.

                • 5. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                  L. Fernigrini

                  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.

                  • 6. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                    Gaz in Oz

                    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

                    • 7. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                      977699

                      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        

                      • 8. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                        Gaz in Oz

                        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

                        • 9. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                          977699

                          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?

                          • 10. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                            Gaz in Oz

                            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?

                            ?

                            • 11. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                              977699

                              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)

                                  )

                                )

                              • 12. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                                977699

                                SQL Server Database collation is SQL_Latin1_General_CP1_CI_AS

                                • 13. Re: ORA-01722 - Invalid Number while using TO_CHAR in Gateway for SQL Server
                                  Choc Cac

                                  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!