This discussion is archived
12 Replies Latest reply: Oct 19, 2012 7:56 AM by tx103108 RSS

Oracle ODBC Gateway SELECT from Sybase fails on large column

tx103108 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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