This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 11, 2013 5:08 AM by kgronau RSS

Dates in Gateway 12c for SQL Server or ODBC

shachou1434 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points