12 Replies Latest reply: Oct 19, 2012 9:56 AM by tx103108 RSS

    Oracle ODBC Gateway SELECT from Sybase fails on large column

    tx103108
      OS: CentOS 5.8 64-bit
      DB: Oracle XE 11gR1 64-bit
      Gateway: Oracle Gateway for ODBC 64-bit
      Database and gateway reside on same Linux Server.
      Connecting to remote Sybase SQL Anywhere 10 server on WindowsXP.
      Using SQL Anywhere 11 odbc driver and unixODBC driver manager on Linux server.
      isql tool connects without any problems.
      One LISTENER, service for db and gateway on same port.
      ===================================
      Via SQL*Plus, the following error occurs....

      SQL> select * from mytable@dblink;
      select * from mytable@dblink

      ORA-02070: database dblink does not support outer joins in this context

      Gateway does not like the 'large' column which is varchar(3270) in length. Also it is the only
      column in the table that is a varchar.
      Take that column out and SELECT works fine.

      Here is the initdblink.ora file:

      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC

      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO=dblink
      HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
      HS_FDS_TRACE_LEVEL=255

      HS_LANGUAGE=american_america.we8iso8859p1
      HS_NLS_NCHAR=UTF-8

      #
      # ODBC specific environment variables
      #
      set ODBCINI=/etc/odbc.ini

      Please advise....

      Edited by: user601798 on Oct 17, 2012 7:09 AM

      Edited by: user601798 on Oct 17, 2012 7:09 AM

      Edited by: user601798 on Oct 17, 2012 7:13 AM
        • 1. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
          Mkirtley-Oracle
          Hi,
          it will be useful to see the same information we mentioned in your other thread so we can see what is happening with the datatype handling of the column -


          - Sybase create staement
          - an ODBC trace
          - a DG4ODBC trace - HS_FDS_TRACE_LEVEL=255

          from a select giving problems. May be an idea to make a select onl of the column causing the problem.
          There can be problems with 'long' columns so we need to see how it is handled.

          Regards,
          Mike
          • 2. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
            tx103108
            My error on the environment specs listed previously.
            See below for corrections:

            Gateway OS: WindowsXP
            Gateway: Oracle Gateway for ODBC 32-bit
            Gateway Listener on WindowsXP
            Connecting to remote Sybase SQL Anywhere 10 server on WindowsXP.
            Using ODBC on WindowsXP
            DB OS: CentOS 5.4 64-bit
            DB: Oracle EE 11gR1 64-bit
            DB Listener on CentOS 5.4 64-bit
            APEX 4.1.1

            select *
            from my_table@dblink LEFT JOIN other_table@dblink
            on my_table.id = other_table.id;

            ORA-02070: database dblink does not support outer joins in this context.

            An inner join does not produce the error.

            I can not get a trace file at this time due to high user activity.

            Edited by: user601798 on Oct 17, 2012 8:25 AM
            • 3. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
              Mkirtley-Oracle
              Hi,
              You previously said this select -

              select * from mytable@dblink;

              gave the ORA-2070 but does it actually only happen when you explicitly issue a select with an 'outer join' or does it always happen for the select of this table ?

              If the syntax -

              select *
              from my_table@dblink LEFT JOIN other_table@dblink
              on my_table.id = other_table.id;

              does not have the varchar(3270) column then does it work successfully ?

              Regards,
              Mike
              • 4. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                tx103108
                If the varchar(3270) column is taken out of the 'select' and an 'left join' is used, then it works.
                If the 'left join' is taken out of the 'select' (and an 'equi-join is used) but the varchar(3270) column is kept in the SQL, then it also works.
                But if both the varchar(3270) column and 'left join' are kept in the 'select, it fails with the ORA-02070 error.

                Sybase DB character set is 'windows-1252' (Latin-1). Gateway HS character set is UTF-8.

                HS_LANGUAGE=american_america.we8iso8859p1
                HS_NLS_NCHAR=UTF-8

                Sybase table structure (column DISCPLN_COMM of type varchar(3270) is the problem)

                Column Type Nullable Primary Key
                -------------------------------- -------------------------------- -------- -----------
                SCH_YR char(4) 0 1
                STU_ID char(6) 0 1
                OFENS_STAMP char(27) 0 1
                OFENS_TIME char(8) 0 0
                CAMPUS_ID char(3) 0 0
                DT_OFENS char(8) 0 0
                MODIFIER char(10) 0 0
                OFENS_SEMCYC char(2) 0 0
                REP_BY char(3) 0 0
                REP_BY_NAME_F char(17) 0 0
                REP_BY_NAME_L char(25) 0 0
                INC_LOC char(3) 0 0
                COURSE char(4) 0 0
                SECTION char(2) 0 0
                CRS_TITLE char(15) 0 0
                PERIOD char(2) 0 0
                INSTR char(3) 0 0
                PARENT_CONTACT char(1) 0 0
                CONTACT_DT char(8) 0 0
                CONF_REQUESTED char(1) 0 0
                CONF_DATE char(8) 0 0
                INFORMAL_HEARING char(1) 0 0
                APPEAL_EXP char(1) 0 0
                WITNESS char(1) 0 0
                DISCPLN_COMM varchar(3270) 0 0
                ADMIN_BY char(3) 0 0
                ADMIN_BY_NAME_F char(17) 0 0
                ADMIN_BY_NAME_L char(25) 0 0
                REPORTED_BY_DESC char(60) 0 0
                INCIDENT_NUM char(6) 0 0
                REPORT_PD char(1) 0 0

                Please advise and thank you.
                • 5. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                  Kgronau-Oracle
                  Might be related to the data type mapping from Sybase data types to Oracle equivalents - could you please describe the table in Oracle (desc my_table@dblink) and provide the output?

                  I also think to remember that only right outer joins were supported in some releases, but need to check this first.

                  - Klaus

                  Edited by: kgronau on Oct 18, 2012 8:49 AM

                  It would be also good to get the complete select you try to execute and the table description of the other tables part of this select statement as we could then for example rewrite the query to use a "no_merge" hint.
                  • 6. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                    tx103108
                    I agree that it is most likely a data type mapping issue. Possibly a buffer issue as well.

                    The tables on the Oracle side are not tables but synonyms pointing to the Sybase tables across a dblink.
                    The SQL works except when that 1 col -- and a right or left join -- is issued.

                    Is there some gateway / HS parameter or ODBC parameter whch effects the data type length?
                    I notice in my PL/SQL Developer IDE that this 1 column comes back as a LONG. (?)

                    Please advise.
                    • 7. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                      Mkirtley-Oracle
                      Hi,
                      How DG4ODBC converts an ODBC datatype to an Oracle dataatype depends on the datatype passed to it by the ODBC driver. Oracle have no control over this.
                      Have a look at the documentation -

                      Oracle® Database Gateway for ODBC
                      User’s Guide
                      11g Release 2 (11.2)

                      Appendix A -

                      Mapping ODBC Data Types to Oracle Data Types

                      THis shows the Oracle datatype for any particulat ODBC datatype.
                      PL?SQL Developer must map th eODBC data types in a different way from DG4ODBC if you get different types displayed.
                      That's one of the reason we have been asking for ODBC traces to see how the ODBC driver handles the datatypes.

                      Regards,
                      Mike
                      • 8. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                        tx103108
                        All users are off the system and one user tried the problematic SQL.

                        HS_FDS_CONNECT_INFO = dblink
                        HS_FDS_TRACE_LEVEL = 255

                        HS_LANGUAGE=american_america.we8iso8859p1
                        HS_NLS_NCHAR=UTF-8

                        However, when executed no trace file is generated.
                        Please advise and thanks.
                        • 9. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                          Mkirtley-Oracle
                          Hi,
                          Did your user try the select from a new SQLPLUS session after you set the trace level ? As lomng as they made a connection to the gateway executable then some sort of trace should have been created, unless it failed immediately.
                          What did the customer see in their SQLPLUS session ?
                          • 10. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                            tx103108
                            Here is the trace file:


                            Oracle Corporation --- THURSDAY OCT 18 2012 14:55:34.259

                            Heterogeneous Agent Release
                            11.2.0.1.0

                            Oracle Corporation --- THURSDAY OCT 18 2012 14:55:34.259

                            Version 11.2.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_CHARACTER_SEMANTICS 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 "TRUE"
                            setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
                            Parameter HS_FDS_QUOTE_IDENTIFIER is not set
                            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"
                            Default value of 32 assumed for HS_FDS_SQLLEN_INTERPRETATION
                            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"
                            Exiting hgosdip, rc=0
                            ORACLE_SID is "dblink"
                            Product-Info:
                            Port Rls/Upd:1/0 PrdStat:0
                            Agent:Oracle Database Gateway for ODBC
                            Facility:hsa
                            Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:dblink
                            Exiting hgogprd, rc=0
                            hostmstr: 2056122368: HOA After hoagprd
                            hostmstr: 2056122368: HOA Before hoainit
                            Entered hgoinit
                            HOCXU_COMP_CSET=1
                            HOCXU_DRV_CSET=31
                            HOCXU_DRV_NCHAR=1000
                            HOCXU_DB_CSET=873
                            HOCXU_SEM_VER=110000
                            Entered hgolofn at 2012/10/18-14:55:39
                            Exiting hgolofn, rc=0 at 2012/10/18-14:55:39
                            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_CHARACTER" 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"
                            Invalid value of 32 given for HS_FDS_SQLLEN_INTERPRETATION
                            treat_SQLLEN_as_compiled = 1
                            Exiting hgoinit, rc=0 at 2012/10/18-14:55:40
                            hostmstr: 2056122368: HOA After hoainit
                            hostmstr: 2056122368: HOA Before hoalgon
                            Entered hgolgon at 2012/10/18-14:55:40
                            reco:0, name:dba, tflag:0
                            Entered hgosuec at 2012/10/18-14:55:41
                            Exiting hgosuec, rc=0 at 2012/10/18-14:55:41
                            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_CHARACTER_SEMANTICS" returned "FALSE"
                            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 dba as default value for "HS_FDS_DEFAULT_OWNER"
                            HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                            Entered hgocont at 2012/10/18-14:55:42
                            HS_FDS_CONNECT_INFO = "dblink"
                            RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                            Entered hgogenconstr at 2012/10/18-14:55:43
                            dsn:dblink, name:dba
                            optn:
                            Entered hgocip at 2012/10/18-14:55:43
                            dsn:dblink
                            Exiting hgocip, rc=0 at 2012/10/18-14:55:43
                            ##>Connect Parameters (len=25)<##
                            ## DSN=dblink;
                            #! UID=dba;
                            #! PWD=*
                            Exiting hgogenconstr, rc=0 at 2012/10/18-14:55:44
                            Entered hgolosf at 2012/10/18-14:55:44
                            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 2012/10/18-14:55:46
                            DriverName:DBODBC10.DLL, DriverVer:10.00.0001
                            DBMS Name:SQL Anywhere, DBMS Version:10.00.0001
                            Exiting hgocont, rc=0 at 2012/10/18-14:55:47
                            SQLGetInfo returns N for SQL_CATALOG_NAME
                            Exiting hgolgon, rc=0 at 2012/10/18-14:55:48
                            hostmstr: 2027339776: HOA After hoalgon
                            RPC Calling nscontrol(0), rc=0
                            hostmstr: 2027339776: RPC Before Upload Caps
                            hostmstr: 2027339776: HOA Before hoaulcp
                            Entered hgoulcp at 2012/10/18-14:55:48
                            Entered hgowlst at 2012/10/18-14:55:48
                            Exiting hgowlst, rc=0 at 2012/10/18-14:55:49
                            SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
                            TXN Capable:3, Isolation Option:0xf
                            SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
                            SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
                            SQLGetInfo returns 128 for SQL_MAX_PROCEDURE_NAME_LEN
                            SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
                            SQLGetInfo returns Y for SQL_COLUMN_ALIAS
                            3 instance capabilities will be uploaded
                            capno:1989, context:0x00000000, add-info: 0
                            capno:1991, context:0x0001ffff, add-info: 0
                            capno:1992, context:0x0001ffff, add-info: 0
                            Exiting hgoulcp, rc=0 at 2012/10/18-14:56:05
                            hostmstr: 2026291200: HOA After hoaulcp
                            hostmstr: 2026291200: RPC After Upload Caps
                            hostmstr: 2026291200: RPC Before Upload DDTR
                            hostmstr: 2026291200: HOA Before hoauldt
                            Entered hgouldt at 2012/10/18-14:56:06
                            NO instance DD translations were uploaded
                            Exiting hgouldt, rc=0 at 2012/10/18-14:56:06
                            hostmstr: 2026291200: HOA After hoauldt
                            hostmstr: 2026291200: RPC After Upload DDTR
                            hostmstr: 2026291200: RPC Before Begin Trans
                            hostmstr: 2026291200: HOA Before hoabegn
                            Entered hgobegn at 2012/10/18-14:56:06
                            tflag:0 , initial:1
                            hoi:0x12f094, ttid (len 27) is ...
                            00: 44415441 5748442E 65623465 33343931 [DATAWHD.eb4e3491]
                            10: 2E322E36 322E3839 363837 [.2.62.89687]
                            tbid (len 24) is ...
                            00: 44415441 5748445B 322E3632 2E383936 [DATAWHD[2.62.896]
                            10: 38375D5B 312E345D [87][1.4]]
                            Exiting hgobegn, rc=0 at 2012/10/18-14:56:08
                            hostmstr: 2026291200: HOA After hoabegn
                            hostmstr: 2026291200: RPC After Begin Trans
                            hostmstr: 2026291200: RPC Before Describe Table
                            hostmstr: 2026291200: HOA Before hoadtab
                            Entered hgodtab at 2012/10/18-14:56:08
                            count:1
                            table: RSCCC.SR_SPEC_PGM_SPEC_ED
                            Allocate hoada[0] @ 025B799C
                            Entered hgopcda at 2012/10/18-14:56:12
                            Column:1(SCH_YR): dtype:12 (VARCHAR), prc/scl:4/0, nullbl:0, octet:4, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:13
                            Entered hgopcda at 2012/10/18-14:56:13
                            Column:2(CAMPUS_ID): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:13
                            Entered hgopcda at 2012/10/18-14:56:14
                            Column:3(STU_ID): dtype:12 (VARCHAR), prc/scl:6/0, nullbl:0, octet:6, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:14
                            Entered hgopcda at 2012/10/18-14:56:14
                            Column:4(DT_ENTRY_STU): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:14
                            Entered hgopcda at 2012/10/18-14:56:15
                            Column:5(PRI_HANDI_IND): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:15
                            Entered hgopcda at 2012/10/18-14:56:15
                            Column:6(INSTRUCT_SET_CD): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:16
                            Entered hgopcda at 2012/10/18-14:56:16
                            Column:7(SPEECH_THRPY_IND): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:16
                            Entered hgopcda at 2012/10/18-14:56:17
                            Column:8(DT_WD): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:17
                            Entered hgopcda at 2012/10/18-14:56:17
                            Column:9(DT_ENTRY_STU_RECIP): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:18
                            Entered hgopcda at 2012/10/18-14:56:18
                            Column:10(WD_RSN_CD): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:18
                            Entered hgopcda at 2012/10/18-14:56:19
                            Column:11(VOC_HRS_ELIG): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:19
                            Entered hgopcda at 2012/10/18-14:56:19
                            Column:12(REG_DAY_SCH_PGM_DEAF): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:19
                            The hoada for table RSCCC.SR_SPEC_PGM_SPEC_ED follows...
                            hgodtab, line 904: Printing hoada @ 025B799C
                            MAX:12, ACTUAL:12, BRC:1, WHT=6 (TABLE_DESCRIBE)
                            hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)
                            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
                            12 VARCHAR N 4 4 0/ 0 0 0 200 SCH_YR
                            12 VARCHAR N 3 3 0/ 0 0 0 200 CAMPUS_ID
                            12 VARCHAR N 6 6 0/ 0 0 0 200 STU_ID
                            12 VARCHAR N 8 8 0/ 0 0 0 200 DT_ENTRY_STU
                            12 VARCHAR N 2 2 0/ 0 0 0 200 PRI_HANDI_IND
                            12 VARCHAR N 2 2 0/ 0 0 0 200 INSTRUCT_SET_CD
                            12 VARCHAR N 1 1 0/ 0 0 0 200 SPEECH_THRPY_IND
                            12 VARCHAR N 8 8 0/ 0 0 0 200 DT_WD
                            12 VARCHAR N 8 8 0/ 0 0 0 200 DT_ENTRY_STU_RECIP
                            12 VARCHAR N 2 2 0/ 0 0 0 200 WD_RSN_CD
                            12 VARCHAR N 1 1 0/ 0 0 0 200 VOC_HRS_ELIG
                            12 VARCHAR N 1 1 0/ 0 0 0 200 REG_DAY_SCH_PGM_DEAF
                            Exiting hgodtab, rc=0 at 2012/10/18-14:56:22
                            hostmstr: 2026291200: HOA After hoadtab
                            hostmstr: 2026291200: HOA Before hoadafr
                            Entered hgodafr, cursor id 0 at 2012/10/18-14:56:23
                            Free hoada @ 025B799C
                            Exiting hgodafr, rc=0 at 2012/10/18-14:56:23
                            hostmstr: 2026291200: HOA After hoadafr
                            hostmstr: 2026291200: RPC After Describe Table
                            hostmstr: 2026291200: RPC Before Describe Table
                            hostmstr: 2026291200: HOA Before hoadtab
                            Entered hgodtab at 2012/10/18-14:56:23
                            count:1
                            table: RSCCC.SR_DISCPLN
                            Allocate hoada[0] @ 025B799C
                            Entered hgopcda at 2012/10/18-14:56:27
                            Column:1(SCH_YR): dtype:12 (VARCHAR), prc/scl:4/0, nullbl:0, octet:4, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:28
                            Entered hgopcda at 2012/10/18-14:56:28
                            Column:2(STU_ID): dtype:12 (VARCHAR), prc/scl:6/0, nullbl:0, octet:6, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:28
                            Entered hgopcda at 2012/10/18-14:56:29
                            Column:3(OFENS_STAMP): dtype:12 (VARCHAR), prc/scl:27/0, nullbl:0, octet:27, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:29
                            Entered hgopcda at 2012/10/18-14:56:29
                            Column:4(OFENS_TIME): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:29
                            Entered hgopcda at 2012/10/18-14:56:30
                            Column:5(CAMPUS_ID): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:30
                            Entered hgopcda at 2012/10/18-14:56:30
                            Column:6(DT_OFENS): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:30
                            Entered hgopcda at 2012/10/18-14:56:31
                            Column:7(MODIFIER): dtype:12 (VARCHAR), prc/scl:10/0, nullbl:0, octet:10, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:31
                            Entered hgopcda at 2012/10/18-14:56:31
                            Column:8(OFENS_SEMCYC): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:32
                            Entered hgopcda at 2012/10/18-14:56:32
                            Column:9(REP_BY): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:32
                            Entered hgopcda at 2012/10/18-14:56:33
                            Column:10(REP_BY_NAME_F): dtype:12 (VARCHAR), prc/scl:17/0, nullbl:0, octet:17, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:33
                            Entered hgopcda at 2012/10/18-14:56:33
                            Column:11(REP_BY_NAME_L): dtype:12 (VARCHAR), prc/scl:25/0, nullbl:0, octet:25, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:33
                            Entered hgopcda at 2012/10/18-14:56:34
                            Column:12(INC_LOC): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:34
                            Entered hgopcda at 2012/10/18-14:56:35
                            Column:13(COURSE): dtype:12 (VARCHAR), prc/scl:4/0, nullbl:0, octet:4, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:35
                            Entered hgopcda at 2012/10/18-14:56:35
                            Column:14(SECTION): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:35
                            Entered hgopcda at 2012/10/18-14:56:36
                            Column:15(CRS_TITLE): dtype:12 (VARCHAR), prc/scl:15/0, nullbl:0, octet:15, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:36
                            Entered hgopcda at 2012/10/18-14:56:36
                            Column:16(PERIOD): dtype:12 (VARCHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:36
                            Entered hgopcda at 2012/10/18-14:56:37
                            Column:17(INSTR): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:37
                            Entered hgopcda at 2012/10/18-14:56:37
                            Column:18(PARENT_CONTACT): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:37
                            Entered hgopcda at 2012/10/18-14:56:38
                            Column:19(CONTACT_DT): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:38
                            Entered hgopcda at 2012/10/18-14:56:38
                            Column:20(CONF_REQUESTED): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:38
                            Entered hgopcda at 2012/10/18-14:56:39
                            Column:21(CONF_DATE): dtype:12 (VARCHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:39
                            Entered hgopcda at 2012/10/18-14:56:39
                            Column:22(INFORMAL_HEARING): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:39
                            Entered hgopcda at 2012/10/18-14:56:40
                            Column:23(APPEAL_EXP): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:40
                            Entered hgopcda at 2012/10/18-14:56:40
                            Column:24(WITNESS): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:41
                            Entered hgopcda at 2012/10/18-14:56:41
                            Column:25(DISCPLN_COMM): dtype:12 (VARCHAR), prc/scl:3270/0, nullbl:0, octet:3270, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:41
                            Entered hgopcda at 2012/10/18-14:56:42
                            Column:26(ADMIN_BY): dtype:12 (VARCHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:42
                            Entered hgopcda at 2012/10/18-14:56:42
                            Column:27(ADMIN_BY_NAME_F): dtype:12 (VARCHAR), prc/scl:17/0, nullbl:0, octet:17, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:42
                            Entered hgopcda at 2012/10/18-14:56:43
                            Column:28(ADMIN_BY_NAME_L): dtype:12 (VARCHAR), prc/scl:25/0, nullbl:0, octet:25, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:43
                            Entered hgopcda at 2012/10/18-14:56:43
                            Column:29(REPORTED_BY_DESC): dtype:12 (VARCHAR), prc/scl:60/0, nullbl:0, octet:60, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:43
                            Entered hgopcda at 2012/10/18-14:56:44
                            Column:30(INCIDENT_NUM): dtype:12 (VARCHAR), prc/scl:6/0, nullbl:0, octet:6, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:44
                            Entered hgopcda at 2012/10/18-14:56:44
                            Column:31(REPORT_PD): dtype:12 (VARCHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0
                            Exiting hgopcda, rc=0 at 2012/10/18-14:56:45
                            The hoada for table RSCCC.SR_DISCPLN follows...
                            hgodtab, line 904: Printing hoada @ 025B799C
                            MAX:31, ACTUAL:31, BRC:1, WHT=6 (TABLE_DESCRIBE)
                            hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)
                            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
                            12 VARCHAR N 4 4 0/ 0 0 0 200 SCH_YR
                            12 VARCHAR N 6 6 0/ 0 0 0 200 STU_ID
                            12 VARCHAR N 27 27 0/ 0 0 0 200 OFENS_STAMP
                            12 VARCHAR N 8 8 0/ 0 0 0 200 OFENS_TIME
                            12 VARCHAR N 3 3 0/ 0 0 0 200 CAMPUS_ID
                            12 VARCHAR N 8 8 0/ 0 0 0 200 DT_OFENS
                            12 VARCHAR N 10 10 0/ 0 0 0 200 MODIFIER
                            12 VARCHAR N 2 2 0/ 0 0 0 200 OFENS_SEMCYC
                            12 VARCHAR N 3 3 0/ 0 0 0 200 REP_BY
                            12 VARCHAR N 17 17 0/ 0 0 0 200 REP_BY_NAME_F
                            12 VARCHAR N 25 25 0/ 0 0 0 200 REP_BY_NAME_L
                            12 VARCHAR N 3 3 0/ 0 0 0 200 INC_LOC
                            12 VARCHAR N 4 4 0/ 0 0 0 200 COURSE
                            12 VARCHAR N 2 2 0/ 0 0 0 200 SECTION
                            12 VARCHAR N 15 15 0/ 0 0 0 200 CRS_TITLE
                            12 VARCHAR N 2 2 0/ 0 0 0 200 PERIOD
                            12 VARCHAR N 3 3 0/ 0 0 0 200 INSTR
                            12 VARCHAR N 1 1 0/ 0 0 0 200 PARENT_CONTACT
                            12 VARCHAR N 8 8 0/ 0 0 0 200 CONTACT_DT
                            12 VARCHAR N 1 1 0/ 0 0 0 200 CONF_REQUESTED
                            12 VARCHAR N 8 8 0/ 0 0 0 200 CONF_DATE
                            12 VARCHAR N 1 1 0/ 0 0 0 200 INFORMAL_HEARING
                            12 VARCHAR N 1 1 0/ 0 0 0 200 APPEAL_EXP
                            12 VARCHAR N 1 1 0/ 0 0 0 200 WITNESS
                            12 VARCHAR N 3270 3270 0/ 0 0 0 200 DISCPLN_COMM
                            12 VARCHAR N 3 3 0/ 0 0 0 200 ADMIN_BY
                            12 VARCHAR N 17 17 0/ 0 0 0 200 ADMIN_BY_NAME_F
                            12 VARCHAR N 25 25 0/ 0 0 0 200 ADMIN_BY_NAME_L
                            12 VARCHAR N 60 60 0/ 0 0 0 200 REPORTED_BY_DESC
                            12 VARCHAR N 6 6 0/ 0 0 0 200 INCIDENT_NUM
                            12 VARCHAR N 1 1 0/ 0 0 0 200 REPORT_PD
                            Exiting hgodtab, rc=0 at 2012/10/18-14:56:50
                            hostmstr: 2026291200: HOA After hoadtab
                            hostmstr: 2026291200: HOA Before hoadafr
                            Entered hgodafr, cursor id 0 at 2012/10/18-14:56:50
                            Free hoada @ 025B799C
                            Exiting hgodafr, rc=0 at 2012/10/18-14:56:50
                            hostmstr: 2026291200: HOA After hoadafr
                            hostmstr: 2026291200: RPC After Describe Table
                            hostmstr: 2026291200: RPC Before Rollback Trans
                            hostmstr: 2026291200: HOA Before hoaroll
                            Entered hgoroll at 2012/10/18-14:56:51
                            tflag:1 , cmt(0):
                            hoi:0x12f098, ttid (len 27) is ...
                            00: 44415441 5748442E 65623465 33343931 [DATAWHD.eb4e3491]
                            10: 2E322E36 322E3839 363837 [.2.62.89687]
                            tbid (len 24) is ...
                            00: 44415441 5748445B 322E3632 2E383936 [DATAWHD[2.62.896]
                            10: 38375D5B 312E345D [87][1.4]]
                            Entered hgocpctx at 2012/10/18-14:56:52
                            Exiting hgocpctx, rc=0 at 2012/10/18-14:56:52
                            Exiting hgoroll, rc=0 at 2012/10/18-14:56:52
                            hostmstr: 2026291200: HOA After hoaroll
                            hostmstr: 2026291200: RPC After Rollback Trans

                            Please advise and thanks..
                            • 11. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                              Kgronau-Oracle
                              From the gateway trace which shows "12 VARCHAR N 3270 3270 0/ 0 0 0 200 DISCPLN_COMM" I would expect the column is mapped to an Oracle varchar2 as the precision does not exceed the varchar2(4000) limit.
                              But there might be a risk as the the Oracle database is using Unicode HOCXU_DB_CSET=873 which might cause a tripling of the varchar columns as some Unicode characters require 3 bytes. When this happens then the column DISCPLN_COMM will be mapped to an Oracle long data type and there are dozens of restrictions when dealing with long data types.

                              Edited by: kgronau on Oct 19, 2012 7:37 AM

                              From a previous update you mentioned:
                              I notice in my PL/SQL Developer IDE that this 1 column comes back as a LONG. (?)
                              which indicates that a tripling of the precision might happen.

                              Could you please add to the gateway init file these 2 parameters:
                              HS_KEEP_REMOTE_COLUMN_SIZE=ALL
                              HS_NLS_LENGTH_SEMANTICS=CHAR

                              Now open a new SQL*Plus session and describe the table in SQL*Plus (desc RSCCC.SR_SPEC_PGM_SPEC_ED@<dblink>) and report its output. Is the column DISCPLN_COMM still mapped to a long column?

                              If yes, then please change the 2 gateway parameters to:
                              HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
                              HS_NLS_LENGTH_SEMANTICS=CHAR
                              open again a new SQL*Plus session and describe the table again. Is DISCPLN_COMM still mapped to a long?

                              Edited by: kgronau on Oct 19, 2012 7:43 AM
                              • 12. Re: Oracle ODBC Gateway SELECT from Sybase fails on large column
                                tx103108
                                I did the following:

                                Could you please add to the gateway init file these 2 parameters:
                                HS_KEEP_REMOTE_COLUMN_SIZE=ALL
                                HS_NLS_LENGTH_SEMANTICS=CHAR

                                and the SQL no longer fails and returns the data accurately.

                                I'll need QA to test further before closing this issue.

                                Thanks for your help.