This discussion is archived
2 Replies Latest reply: Aug 26, 2013 12:54 PM by tx103108 RSS

HS_KEEP_REMOTE_COLUMN_SIZE parameter setting causing error

tx103108 Newbie
Currently Being Moderated

Remote DB: Sybase SQL Anywhere 11.0 on Windows Server

Local DB: Oracle EE 11gR1 64-bit on Linux RHEL 6.3 64-bit

Oracle ODBC Gateway

===========================

 

In my HS initSID.ora file I get the following errors when running a SELECT:

 

If HS_KEEP_REMOTE_COLUMN_SIZE parameter = OFF or REMOTE

 

ERROR at line 1:
ORA-28562: Heterogeneous Services data truncation error
[Sybase][ODBC Driver]Data truncated {01004}
ORA-02063: preceding 2 lines from ORCL
ORA-06512: at line 8

 

If HS_KEEP_REMOTE_COLUMN_SIZE parameter = ALL or LOCAL

 

ERROR at line 1:
ORA-00997: illegal use of LONG datatype
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2

 

The parameter HS_NLS_LENGTH_SEMANTICS=CHAR.

 

Any suggestions?  Seems like whatever parameter setting I use, I get an error.

Also, this environment use to work when parameter HS_KEEP_REMOTE_COLUMN_SIZE = ALL.

Could some remote source (Sybase) data have changed to now expose this issue?

 

One final note:  There are 3 other environments using HS_KEEP_REMOTE_COLUMN_SIZE=ALL and HS_NLS_LENGTH_SEMANTICS=CHAR but are each attached to different Sybase remote db sources.

All 3 of these environments are working w/o issues.

Thank u.

 

Message was edited by: tx103108 Figured out one issue -- HS_KEEP_REMOTE_COLUMN_SIZE and HS_NLS_LENGTH_SEMANTICS don't seem to be available parameters with the ODBC Gateway (as they are with, say TeraData).  Are there equivalent parameters available for the ODBC initSID.ora file?

  • 1. Re: HS_KEEP_REMOTE_COLUMN_SIZE parameter setting causing error
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

    The parameter HS_KEEP_REMOTE_COLUMN_SIZE is available with the DG4ODBC gateway.
    To check further can you send the following information -

     

    1. What is the Sybase create table statement for tabl giving the problem ?

     

    2. What is the character set used by the Oracle RDBMS ? Is it a Unicode character set ? To check, run -

    select * from nls_database_parameters ;

    Do the Oracle databases where you do not havee problem use a different character set ?

     

    3. What is the character set of the Sybase database ? Is it different from those where you do not have a problem ?

     

    4. With the different HS_KEEP_REMOTE_COLUMN_SIZE values - LOCAL, ALL and REMOTE what is returned by -

     

    describe "owner"."sybase_table"@dg4odbc_link

     

    Do not put a semicolon atthe end of the describe.

     

    Regards,

    Mike

  • 2. Re: HS_KEEP_REMOTE_COLUMN_SIZE parameter setting causing error
    tx103108 Newbie
    Currently Being Moderated

    HS_KEEP_REMOTE_COLUMN_SIZE=REMOTE

     

    SQL> describe "rsccc"."sr_discpln"@txeis;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------

    DISCPLN_COMM                              NOT NULL LONG

    HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

     

    SQL> describe "rsccc"."sr_discpln"@txeis;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------

    DISCPLN_COMM                              NOT NULL VARCHAR2(3270 CHAR)

    HS_KEEP_REMOTE_COLUMN_SIZE=ALL

    SQL> describe "rsccc"."sr_discpln"@txeis;
      Name                                      Null?    Type
      ----------------------------------------- -------- ----------------------------

      DISCPLN_COMM                              NOT NULL VARCHAR2(3270 CHAR)
     

Legend

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