2 Replies Latest reply: Aug 26, 2013 2:54 PM by tx103108 RSS

    HS_KEEP_REMOTE_COLUMN_SIZE parameter setting causing error

    tx103108

      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

          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

            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)