1 2 Previous Next 15 Replies Latest reply: Sep 11, 2013 7:08 AM by Kgronau-Oracle RSS

    Dates in Gateway 12c for SQL Server or ODBC

    shachou1434

      Hi,

      I'm having a problem with dates when selecting from an SQL Server 2008 R2 database through OGW 12c.

      Both dg4odbc and dg4msql returns garbage data while Oracle Gateway 11g R2 is working fine! on the same machines with the same configurations.

      Oracle database version is 10.2.0.4 x64 on windows 2008 r2

       

      Thank you.

        • 1. Re: Dates in Gateway 12c for SQL Server or ODBC
          Kgronau-Oracle

          Could you please provide the table definition as it is defined in your SQL Server and also post the output as it is seen using DG4MSQL 11.2 and 12c. in addition please post the gateway init file you've used.

           

          - Klaus

          • 2. Re: Dates in Gateway 12c for SQL Server or ODBC
            shachou1434

            The table is consisted of two columns with datatypes smalldatetime, datetime respectively

            Real values are:

            2012-05-12 17:15:00, 2012-05-12 05:01:05.470

            Using 11g gateway, data appears like this:

            12/05/2012 17:15:00, 12/05/2012 05:01:05

            Using 12c: White spaces and some characters like "U         ,?      " are shown

             

            Init file contents for both 11g and 12c are:

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

            # This is a customized agent init file that contains the HS parameters

            # that are needed for the Database Gateway for Microsoft SQL Server

             

             

            #

            # HS init parameters

            #

            HS_FDS_CONNECT_INFO=serverip//databasename

            HS_FDS_TRACE_LEVEL=OFF

            HS_FDS_RECOVERY_ACCOUNT=RECOVER

            HS_FDS_RECOVERY_PWD=RECOVER

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

             

             

            Thank you

            • 3. Re: Dates in Gateway 12c for SQL Server or ODBC
              Kgronau-Oracle

              When certain date columns show corrupted date content it might indicate that the HS catalog is out of sync.

              Please connect as sysdba to the Oracle 10.2.0.4 database and execute in SQL*Plus the scripts:

              @$ORACLE_HOME/rdbms/admin/catnohs

              @$ORACLE_HOME/rdbms/admin/caths

              commit;

               

              this will drop and recreate the HS catalog in your Oracle database. Next time now when the gateway starts a new connection it will recognize there are no capabilities present in the Oracle database for the gateway and upload its caps again.

              Make sure to first use the 12c gateway, then the 11g gateway and check the output of both selects.

              • 4. Re: Dates in Gateway 12c for SQL Server or ODBC
                shachou1434

                I did as you told me with no changes to the results, 11g is still okay, 12c is not!

                 

                 

                I noticed some errors while executing the second script "caths", but I'm not sure if it has any effect

                DROP TYPE SYS.HS$_DDTF_SQLTables_T

                *

                ERROR at line 1:

                ORA-04043: object HS$_DDTF_SQLTABLES_T does not exist

                and

                DROP TYPE SYS.HS$_DDTF_SQLColumns_T

                *

                ERROR at line 1:

                ORA-04043: object HS$_DDTF_SQLCOLUMNS_T does not exist

                 

                 

                Thank you

                • 5. Re: Dates in Gateway 12c for SQL Server or ODBC
                  shachou1434

                  I've just tried Database 11g and gateway 12c with the same configuration as I did with 10g and it worked!

                  So I'm quite sure that the problem is with 10g. Is it supported with gateway 12c?

                   

                  Thank you

                  • 6. Re: Dates in Gateway 12c for SQL Server or ODBC
                    Kgronau-Oracle

                    That's fine - these errors are expected.

                     

                    I created a demo table in SQL Server:

                     

                    CREATE TABLE [dbo].[Date_Test](

                        [col1] [int] NULL,

                        [col2] [date] NULL,

                        [col3] [smalldatetime] NULL,

                        [col4] [datetime2](7) NULL,

                        [col5] [datetime] NULL

                    ) ON [PRIMARY]

                     

                    and inserted a demo record: insert into Date_Test values (1,getdate(),getdate(),getdate(),getdate());

                     

                    When I now try to select the record I get the correct result or when changing the gateway config an ORA-28528: Heterogeneous Services datatype conversion error which is different from showing poor results.

                     

                    Is it possible for you to create the same table as I've used and insert a demo record like I did? What's the result, still corrupted date info?

                    Could you please enable gateway tracing level 255 (HS_FDS_TRACE_LEVEL=255) and post the gateway trace when you get corrupted results while selecting the "Date_Test" table?

                     

                     

                    Please do not forget to remove the trace parameter afterwards.

                     

                     

                    - Klaus

                    • 7. Re: Dates in Gateway 12c for SQL Server or ODBC
                      shachou1434

                      I tried your demo table, and it gave the same datatype conversion error as you said!

                      But when removing the int column or moving it to the end of the table, I got the corrupted info. I don't know why the int column changes the results like this!

                       

                      The trace file contents is:

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

                      Oracle Corporation --- TUESDAY   SEP 10 2013 13:04:26.371

                      Heterogeneous Agent Release

                      12.1.0.1.0

                      Oracle Corporation --- TUESDAY   SEP 10 2013 13:04:26.371

                          Version 12.1.0.1.0

                      Entered hgogprd

                      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"

                      Entered hgosdip

                      setting HS_OPEN_CURSORS to default of 50

                      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"

                      setting HS_FDS_RECOVERY_PWD to default value

                      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG

                      setting HS_IDLE_TIMEOUT to default of 0

                      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"

                      setting HS_NLS_NCHAR to default of "UCS2"

                      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"

                      setting HS_FDS_DATE_MAPPING to default of "DATE"

                      setting HS_RPC_FETCH_REBLOCKING to default of "ON"

                      setting HS_FDS_FETCH_ROWS to default of "100"

                      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"

                      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"

                      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"

                      setting HS_FDS_MAP_NCHAR to default of "TRUE"

                      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"

                      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"

                      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"

                      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"

                      setting HS_FDS_QUERY_DRIVER to default of "FALSE"

                      setting HS_FDS_SUPPORT_STATISTICS to default of "TRUE"

                      setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"

                      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"

                      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"

                      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"

                      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"

                      setting HS_FDS_DELAYED_OPEN to default of "TRUE"

                      setting HS_FDS_WORKAROUNDS to default of "0"

                      setting HS_FDS_ARRAY_EXEC to default of "TRUE"

                      Exiting hgosdip, rc=0

                      ORACLE_SID is "dg4msql"

                      Product-Info:

                        Port Rls/Upd:1/0 PrdStat:0

                        Agent:Oracle Database Gateway for MSSQL

                        Facility:hsa

                        Class:MSSQL, ClassVsn:12.1.0.1.0_0017, Instance:dg4msql

                      Exiting hgogprd, rc=0

                      hostmstr: 8795934724096: HOA After hoagprd

                      hostmstr: 8795934724096: HOA Before hoainit

                      Entered hgoinit

                      HOCXU_COMP_CSET=1

                      HOCXU_DRV_CSET=178

                      HOCXU_DRV_NCHAR=1000

                      HOCXU_DB_CSET=560

                      HS_LANGUAGE not specified

                      rc=2156938 attempting to get LANG environment variable.

                      HOCXU_SEM_VER=102000

                      HOCXU_VC2_MAX=4000

                      HOCXU_RAW_MAX=2000

                      Entered hgolofn at 2013/09/10-13:04:28

                      RC=-1 from HOSGIP for "PATH"

                      PATH from environment is "F:\oracle\product\10.2.0\db_1\bin;F:\OraGateW\product\12.1.0\tghome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\"

                      Setting PATH to "F:\oracle\product\10.2.0\db_1\bin;F:\OraGateW\product\12.1.0\tghome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\"

                      Exiting hgolofn, rc=0 at 2013/09/10-13:04:28

                      HOSGIP for "HS_OPEN_CURSORS" returned "50"

                      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"

                      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"

                      HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"

                      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"

                      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"

                      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"

                      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"

                      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"

                      treat_SQLLEN_as_compiled = 1

                      Exiting hgoinit, rc=0 at 2013/09/10-13:04:28

                      hostmstr: 8795930529792: HOA After hoainit

                      hostmstr: 8795930529792: HOA Before hoalgon

                      Entered hgolgon at 2013/09/10-13:04:28

                      reco:0, name:sales, tflag:0

                      Entered hgosuec at 2013/09/10-13:04:28

                      uencoding=UTF16

                      Entered shgosuec at 2013/09/10-13:04:29

                      Exiting shgosuec, rc=0 at 2013/09/10-13:04:29

                      shgosuec() returned rc=0

                      Exiting hgosuec, rc=0 at 2013/09/10-13:04:29

                      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

                      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"

                      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"

                      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"

                      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

                      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"

                      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"

                      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

                      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

                      using sales as default schema

                      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

                      Entered hgocont at 2013/09/10-13:04:29

                      HS_FDS_CONNECT_INFO = "10.0.1.14//SalesDB"

                      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

                      Entered hgogenconstr at 2013/09/10-13:04:29

                      dsn:10.0.1.14//SalesDB, name:sales

                      optn:

                      Entered hgocip at 2013/09/10-13:04:29

                      dsn:10.0.1.14//SalesDB

                      Exiting hgocip, rc=0 at 2013/09/10-13:04:29

                      Entered shgogohn at 2013/09/10-13:04:29

                      ohn is 'OraGTW12Home1'

                      Exiting shgogohn, rc=0 at 2013/09/10-13:04:29

                      RC=-1 from HOSGIP for "HS_FDS_ENCRYPT_SESSION"

                      using 0 as default value for "HS_FDS_ENCRYPT_SESSION"

                      RC=-1 from HOSGIP for "HS_FDS_VALIDATE_SERVER_CERT"

                      using 1 as default value for "HS_FDS_VALIDATE_SERVER_CERT"

                      Entered hgocont_OracleCsidToIANA at 2013/09/10-13:04:30

                      Returning 2252

                      Exiting hgocont_OracleCsidToIANA at 2013/09/10-13:04:30

                      ##>Connect Parameters (len=283)<##

                      ## DRIVER=Oracle 12g dg4msql-OraGTW12Home1;

                      ## SERVER=10.0.1.14;

                      ## Database=SalesDB;

                      #! UID=sales;

                      #! PWD=*

                      ## AnsiNPW=Yes;

                      ## EnableQuotedIdentifiers=1;

                      ## EncryptionMethod=0;

                      ## ValidateServerCertificate=1;

                      ## IANAAppCodePage=2252;

                      ## OctetSizeCalculation=1;

                      ## PadVarbinary=0;

                      ## SupportNumericPrecisionGreaterThan38=1;

                      Exiting hgogenconstr, rc=0 at 2013/09/10-13:04:30

                      Entered hgopoer at 2013/09/10-13:04:30

                      hgopoer, line 240: got native error 5701 and sqlstate 01000; message follows...

                      [Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Changed database context to 'SalesDB'. {01000,NativeErr = 5701}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}

                      Exiting hgopoer, rc=0 at 2013/09/10-13:04:31

                      hgocont, line 2775: calling SqlDriverConnect got sqlstate 01000

                      Entered hgolosf at 2013/09/10-13:04:31

                      ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFFFF 0x03FF 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000

                                                    0x0000 0x0000 0xFE00 0x3F5F

                      Exiting hgolosf, rc=0 at 2013/09/10-13:04:31

                      DriverName:HGsqls23.dll, DriverVer:07.01.0074 (B0079, U0054)

                      DBMS Name:Microsoft SQL Server, DBMS Version:10.50.1600

                      Exiting hgocont, rc=0 at 2013/09/10-13:04:32 with error ptr FILE:hgocont.c LINE:2775 ID:SQLDriverConnect

                      SQLGetInfo returns Y for SQL_CATALOG_NAME

                      SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN

                      Exiting hgolgon, rc=0 at 2013/09/10-13:04:32

                      hostmstr: 8795908349952: HOA After hoalgon

                      RPC Calling nscontrol(0), rc=0

                      hostmstr: 8795908349952: RPC Before Upload Caps

                      hostmstr: 8795908349952: HOA Before hoaulcp

                      Entered hgoulcp at 2013/09/10-13:04:32

                      Entered hgowlst at 2013/09/10-13:04:32

                      Exiting hgowlst, rc=1 at 2013/09/10-13:04:33

                      SQLGetInfo returns Y for SQL_PROCEDURES

                      SQLGetInfo returns 0x1f for SQL_SCHEMA_USAGE

                      TXN Capable:2, Isolation Option:0xf

                      SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN

                      SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN

                      SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN

                      HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER

                      SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR

                      13 instance capabilities will be uploaded

                        capno:5992, context:0x0001ffff, add-info:        0

                        capno:7042, context:0x00000000, add-info:        0, translation:"42"

                        capno:7047, context:0x00000000, add-info:        0, translation:"57"

                        capno:7049, context:0x00000000, add-info:        0, translation:"59"

                        capno:7050, context:0x00000000, add-info:        0, translation:"60"

                        capno:7066, context:0x00000000, add-info:        0

                        capno:7067, context:0x00000000, add-info:        0

                        capno:7068, context:0x00000000, add-info:        0

                        capno:7069, context:0x00000000, add-info:        0

                        capno:7500, context:0x00000001, add-info:       91, translation:"42"

                        capno:7501, context:0x00000001, add-info:       93, translation:"57"

                        capno:7502, context:0x00000001, add-info:      107, translation:"59"

                        capno:7503, context:0x00000001, add-info:      110, translation:"60"

                      Exiting hgoulcp, rc=0 at 2013/09/10-13:04:38

                      hostmstr: 8795908349952: HOA After hoaulcp

                      hostmstr: 8795908349952: RPC After Upload Caps

                      hostmstr: 8795908349952: RPC Before Upload DDTR

                      hostmstr: 8795908349952: HOA Before hoauldt

                      Entered hgouldt at 2013/09/10-13:04:38

                      NO instance DD translations were uploaded

                      Exiting hgouldt, rc=0 at 2013/09/10-13:04:38

                      hostmstr: 8795908349952: HOA After hoauldt

                      hostmstr: 8795908349952: RPC After Upload DDTR

                      hostmstr: 8795908349952: RPC Before Begin Trans

                      hostmstr: 8795908349952: HOA Before hoabegn

                      Entered hgobegn at 2013/09/10-13:04:38

                      tflag:0 , initial:1

                      hoi:0x20ebf8, ttid (len 37) is ...

                        00: 44423130 472E5049 58454C2E 4C4F4341  [DBSRM.PRINC.LOCA]

                        10: 4C2E3163 34316461 382E312E 32352E31  [L.1c41da8.1.25.1]

                        20: 30343038 31                          [04081]

                                       tbid (len 10) is ...

                        0: 01001900 91960100 0104               [..........]

                      Exiting hgobegn, rc=0 at 2013/09/10-13:04:39

                      hostmstr: 8795908349952: HOA After hoabegn

                      hostmstr: 8795908349952: RPC After Begin Trans

                      hostmstr: 8795908349952: RPC Before Describe Table

                      hostmstr: 8795908349952: HOA Before hoadtab

                      Entered hgodtab at 2013/09/10-13:04:39

                      count:1

                        table: AHHD

                      Allocate hoada[0] @ 00000000019A0100

                      Free hoada[0] @ 00000000019A0100

                      SQL text from hgodtab, id=0, len=20 ...

                           00: 73656C65 6374202A 2066726F 6D202241  [select * from "A]

                           10: 48484422                             [HHD"]

                      Entered hgodscr_process_sellist_description at 2013/09/10-13:04:41

                      Entered hgopcda at 2013/09/10-13:04:41

                      Column:1(col2): dtype:91 (DATE), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:41

                      Entered hgopcda at 2013/09/10-13:04:41

                      Column:2(col3): dtype:93 (TIMESTAMP), prc/scl:16/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:41

                      Entered hgopcda at 2013/09/10-13:04:41

                      Column:3(col4): dtype:93 (TIMESTAMP), prc/scl:27/7, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:41

                      Entered hgopcda at 2013/09/10-13:04:41

                      Column:4(col5): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:41

                      Entered hgopcda at 2013/09/10-13:04:42

                      Column:5(col1): dtype:4 (INTEGER), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:42

                      The hoada for table AHHD follows...

                      hgodtab, line 1079: Printing hoada @ 00000000019A0100

                      MAX:5, ACTUAL:5, BRC:100, WHT=5 (SELECT_LIST)

                      DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      91 DATE    Y         16         16   0/  0    0   0   0 col2

                      91 DATE    Y         16         16   0/  0    0   0   0 col3

                      91 DATE    Y         16         16   0/  0    0   0   0 col4

                      91 DATE    Y         16         16   0/  0    0   0   0 col5

                        4 INTEGER Y          4          4   0/  0    0   0   0 col1

                      Exiting hgodtab, rc=0 at 2013/09/10-13:04:42

                      hostmstr: 8795908349952: HOA After hoadtab

                      hostmstr: 8795908349952: HOA Before hoadafr

                      Entered hgodafr, cursor id 0 at 2013/09/10-13:04:42

                      Free hoada @ 00000000019A0100

                      Exiting hgodafr, rc=0 at 2013/09/10-13:04:42

                      hostmstr: 8795908349952: HOA After hoadafr

                      hostmstr: 8795908349952: RPC After Describe Table

                      hostmstr: 8795908349952: RPC Before Get Statistics

                      hostmstr: 8795908349952: HOA Before hoxtcis

                      Entered hgotcis at 2013/09/10-13:04:43

                      Calling SQLStatistics for AHHD

                      Calling SQLColumns for sales.AHHD

                      Exiting hgotcis, rc=0 at 2013/09/10-13:04:44

                      hostmstr: 8795908349952: HOA After hoxtcis

                      hostmstr: 8795908349952: RPC After Get Statistics

                      hostmstr: 8795908349952: RPC Before SQL Bundling

                      hostmstr: 8795908349952: HOA Before hoxpars

                      Entered hgopars, cursor id 1 at 2013/09/10-13:04:45

                      type:0

                      SQL text from hgopars, id=1, len=71 ...

                           00: 53454C45 43542041 312E2263 6F6C3222  [SELECT A1."col2"]

                           10: 2C41312E 22636F6C 33222C41 312E2263  [,A1."col3",A1."c]

                           20: 6F6C3422 2C41312E 22636F6C 35222C41  [ol4",A1."col5",A]

                           30: 312E2263 6F6C3122 2046524F 4D202241  [1."col1" FROM "A]

                           40: 48484422 204131                      [HHD" A1]

                      Exiting hgopars, rc=0 at 2013/09/10-13:04:45

                      hostmstr: 8795908349952: HOA After hoxpars

                      hostmstr: 8795908349952: HOA Before hoxopen

                      Entered hgoopen, cursor id 1 at 2013/09/10-13:04:45

                      hgoopen, line 87: NO hoada to print

                      Deferred open until first fetch.

                      Exiting hgoopen, rc=0 at 2013/09/10-13:04:45

                      hostmstr: 8795908349952: HOA After hoxopen

                      hostmstr: 8795908349952: HOA Before hoxdscr

                      Entered hgodscr, cursor id 1 at 2013/09/10-13:04:45

                      Allocate hoada @ 00000000019A00A8

                      Entered hgodscr_process_sellist_description at 2013/09/10-13:04:45

                      Entered hgopcda at 2013/09/10-13:04:46

                      Column:1(col2): dtype:91 (DATE), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:46

                      Entered hgopcda at 2013/09/10-13:04:46

                      Column:2(col3): dtype:93 (TIMESTAMP), prc/scl:16/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:46

                      Entered hgopcda at 2013/09/10-13:04:46

                      Column:3(col4): dtype:93 (TIMESTAMP), prc/scl:27/7, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:46

                      Entered hgopcda at 2013/09/10-13:04:46

                      Column:4(col5): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:46

                      Entered hgopcda at 2013/09/10-13:04:46

                      Column:5(col1): dtype:4 (INTEGER), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0

                      Exiting hgopcda, rc=0 at 2013/09/10-13:04:47

                      hgodscr, line 470: Printing hoada @ 00000000019A00A8

                      MAX:5, ACTUAL:5, BRC:100, WHT=5 (SELECT_LIST)

                      DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      91 DATE    Y         16         16   0/  0    0   0   0 col2

                      91 DATE    Y         16         16   0/  0    0   0   0 col3

                      91 DATE    Y         16         16   0/  0    0   0   0 col4

                      91 DATE    Y         16         16   0/  0    0   0   0 col5

                        4 INTEGER Y          4          4   0/  0    0   0   0 col1

                      Exiting hgodscr, rc=0 at 2013/09/10-13:04:47

                      hostmstr: 8795908349952: HOA After hoxdscr

                      hostmstr: 8795908349952: RPC After SQL Bundling

                      hostmstr: 8795908349952: RPC Before Fetch Row

                      hostmstr: 8795908349952: HOA Before PreFetch

                      hostmstr: 8795908349952: HOA Before hoaftch

                      Entered hgoftch, cursor id 1 at 2013/09/10-13:04:47

                      hgoftch, line 135: Printing hoada @ 00000000019A00A8

                      MAX:5, ACTUAL:5, BRC:100, WHT=5 (SELECT_LIST)

                      DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      91 DATE    Y         16         16   0/  0    0   0   0 col2

                      91 DATE    Y         16         16   0/  0    0   0   0 col3

                      91 DATE    Y         16         16   0/  0    0   0   0 col4

                      91 DATE    Y         16         16   0/  0    0   0   0 col5

                        4 INTEGER Y          4          4   0/  0    0   0   0 col1

                      Performing delayed open.

                      SQLBindCol: column 1, cdatatype: 93, bflsz: 16

                      SQLBindCol: column 2, cdatatype: 93, bflsz: 16

                      SQLBindCol: column 3, cdatatype: 93, bflsz: 16

                      SQLBindCol: column 4, cdatatype: 93, bflsz: 16

                      SQLBindCol: column 5, cdatatype: -16, bflsz: 4

                      SQLFetch: row: 1, column 1, bflsz: 16, bflar: 16

                      SQLFetch: row: 1, column 1, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)

                      SQLFetch: row: 1, column 2, bflsz: 16, bflar: 16

                      SQLFetch: row: 1, column 2, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)

                      SQLFetch: row: 1, column 3, bflsz: 16, bflar: 16

                      SQLFetch: row: 1, column 3, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)

                      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16

                      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)

                      SQLFetch: row: 1, column 5, bflsz: 4, bflar: 4

                      SQLFetch: row: 1, column 5, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)

                      SQLFetch: row: 2, column 1, bflsz: 16, bflar: 16

                      SQLFetch: row: 2, column 1, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 2, column 2, bflsz: 16, bflar: 16

                      SQLFetch: row: 2, column 2, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 2, column 3, bflsz: 16, bflar: 16

                      SQLFetch: row: 2, column 3, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 2, column 4, bflsz: 16, bflar: 16

                      SQLFetch: row: 2, column 4, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 2, column 5, bflsz: 4, bflar: 4

                      SQLFetch: row: 2, column 5, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)

                      SQLFetch: row: 3, column 1, bflsz: 16, bflar: 16

                      SQLFetch: row: 3, column 1, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 3, column 2, bflsz: 16, bflar: 16

                      SQLFetch: row: 3, column 2, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 3, column 3, bflsz: 16, bflar: 16

                      SQLFetch: row: 3, column 3, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 3, column 4, bflsz: 16, bflar: 16

                      SQLFetch: row: 3, column 4, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 3, column 5, bflsz: 4, bflar: 4

                      SQLFetch: row: 3, column 5, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)

                      SQLFetch: row: 4, column 1, bflsz: 16, bflar: 16

                      SQLFetch: row: 4, column 1, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 4, column 2, bflsz: 16, bflar: 16

                      SQLFetch: row: 4, column 2, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 4, column 3, bflsz: 16, bflar: 16

                      SQLFetch: row: 4, column 3, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 4, column 4, bflsz: 16, bflar: 16

                      SQLFetch: row: 4, column 4, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 4, column 5, bflsz: 4, bflar: 4

                      SQLFetch: row: 4, column 5, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)

                      SQLFetch: row: 5, column 1, bflsz: 16, bflar: 16

                      SQLFetch: row: 5, column 1, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 5, column 2, bflsz: 16, bflar: 16

                      SQLFetch: row: 5, column 2, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 5, column 3, bflsz: 16, bflar: 16

                      SQLFetch: row: 5, column 3, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 5, column 4, bflsz: 16, bflar: 16

                      SQLFetch: row: 5, column 4, bflsz: 16, bflar: 16, (bfl: 0, mbl: 16)

                      SQLFetch: row: 5, column 5, bflsz: 4, bflar: 4

                      SQLFetch: row: 5, column 5, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)

                      5 rows fetched

                      Exiting hgoftch, rc=0 at 2013/09/10-13:04:50

                      hostmstr: 8795908349952: HOA After hoaftch

                      hostmstr: 8795908349952: HOA Before hoaftch

                      Entered hgoftch, cursor id 1 at 2013/09/10-13:04:50

                      hgoftch, line 135: Printing hoada @ 00000000019A00A8

                      MAX:5, ACTUAL:5, BRC:5, WHT=5 (SELECT_LIST)

                      DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      91 DATE    Y         16         16   0/  0    0   0   0 col2

                      91 DATE    Y         16         16   0/  0    0   0   0 col3

                      91 DATE    Y         16         16   0/  0    0   0   0 col4

                      91 DATE    Y         16         16   0/  0    0   0   0 col5

                        4 INTEGER Y          4          4   0/  0    0   0   0 col1

                      0 rows fetched

                      Exiting hgoftch, rc=1403 at 2013/09/10-13:04:51

                      hostmstr: 8795908349952: HOA After hoaftch

                      hostmstr: 8795908349952: HOA After PreFetch

                      hostmstr: 8795908349952: RPC After Fetch Row

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

                       

                      Thank you

                      • 8. Re: Dates in Gateway 12c for SQL Server or ODBC
                        Kgronau-Oracle

                        Wait.. the gateway is on Windows .... not sure why I was thinking only the database is on Windows and GTW runs on Linux.

                         

                        Can you please provide me a description of the SQL Server table using the gateway:

                        desc ahhd@<your database link>

                         

                        Then add to your gateway init file the parameters:

                        HS_FDS_TIMESTAMP_MAPPING=CHAR

                        HS_FDS_DATE_MAPPING=CHAR

                         

                        Open a new SQL*Plus session, perform another describe and select also from your table.

                         

                        - Klaus

                        • 9. Re: Dates in Gateway 12c for SQL Server or ODBC
                          shachou1434

                          Here is the description:

                          Before mapping:

                          Name                                      Null?    Type

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

                          col2                                               VARCHAR2(16)

                          col3                                               VARCHAR2(16)

                          col4                                               VARCHAR2(16)

                          col5                                               VARCHAR2(16)

                          col1                                               NUMBER(10)

                           

                          After mapping:

                          Name                                      Null?    Type

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

                          col2                                               CHAR(10)

                          col3                                               CHAR(16)

                          col4                                               CHAR(27)

                          col5                                               CHAR(23)

                          col1                                               NUMBER(10)

                           

                          Selecting data gives the following error after mapping:

                          ERROR:

                          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

                          [Oracle][ODBC SQL Server Wire Protocol driver]Numeric overflow. Error in column

                          2. {22003}[Oracle][ODBC SQL Server Wire Protocol driver]Numeric overflow. Error

                          in column 2. {22003}[Oracle][ODBC SQL Server Wire Protocol driver]Numeric

                          overflow. Error in column 2. {22003}[Oracle][ODBC SQL Server Wire Protocol

                          driver]Numeric overflow. Error in column 2. {22003}[Oracle][ODBC SQL Server

                          Wire Protocol driver]Numeric overflow. Error in column 2. {22003}

                          ORA-02063: preceding 2 lines from DBLINK

                           

                          Thank you

                          • 10. Re: Dates in Gateway 12c for SQL Server or ODBC
                            Kgronau-Oracle

                            Windows Gateway seems to behave here a little bit different the the Linux gateway but I'm pretty sure now that you hit these two related defects  17434002/17434137.

                            Onl Linux you normally get the ORA-28528 error message rather wrong content. Windows seems to behave here a little bit different. But the trace and also the desc output as well as the error message when switching to CHAR indicate you're hitting the same issue.

                            Unfortunately there's currently not yet a fix available. Best would be to to file a service request and please ask to get it assigned to me (kgronau). I will then link you to the bugs and I can then notify you once the issue is fixed.

                             

                            - Klaus

                            • 11. Re: Dates in Gateway 12c for SQL Server or ODBC
                              shachou1434

                              Thank you very much for your effort.

                              I could not open the links! but I understand that these issues has no fix yet.

                              Is it specific to 10g? Should I recommend upgrading database to 11g 11.2.0.1 or 11.2.0.3 to my client?

                               

                              Thank you

                              • 12. Re: Dates in Gateway 12c for SQL Server or ODBC
                                shachou1434

                                Another request please

                                Could you please tell me how to open the articles?

                                Thank you

                                • 13. Re: Dates in Gateway 12c for SQL Server or ODBC
                                  Kgronau-Oracle

                                  the issue is 10g database related and upgrading to 11.2.0.4 (is available for a couple of platforms already) or 11.2.0.3 would be better then waiting for a 10.2.0.5 fix. Please take care just in case you want to have a fix for 10.2.0.5 that extended support ended 31st of July 2013  => see Lifetime support policy for Oracle databases available here: Lifetime Support Policy | Oracle Support | Oracle)  and only a very limited additional fixing is available for a few rare cases.

                                   

                                  The references above are bug numbers and you can view them in My Oracle Support.

                                   

                                  - Klaus

                                  • 14. Re: Dates in Gateway 12c for SQL Server or ODBC
                                    shachou1434

                                    Thank you very much

                                    I got the approval to upgrade to 11g, I read in the Oracle Support article that 11.2.0.4 for windows platform will be available in the forth quarter. I'll use it if it's released before November, and if not, I'll use 11.2.0.3.

                                    About the references, I didn't find it in bug database or in KB.

                                     

                                    Thank you

                                    1 2 Previous Next