2 Replies Latest reply: May 22, 2013 6:33 PM by 1006458 RSS

    DG4MSQL Unable to Extract VARCHAR(1) Columns

    1006458
      Hi,

      We have an issue with a DG4MSQL gateway to a SQL Server 2005 database. It is successfully bringing back data for all types of columns except the flag columns that have a datatype of VARCHAR(1). If I execute a SELECT then I see all the values including the flags.
      select "prof_id", "default_flag", "superuser_flag", "scope_type", "prof_name"
      from profile@stage2pmdb
      where "prof_name" = '<Admin Superuser>'
      prof_id default_flag superuser_flag scope_type prof_name
      -------------------- -------------------- -------------------- ---------------------------------------- --------------------------------------------------------------------------------
      12 N Y ST_Global <Admin Superuser>

      However when the data is inserted into a table in the oracle database using the gateway the flag columns (defined as VARCHAR2(1)) are empty:
      insert into profile_test (prof_id, default_flag, superuser_flag, scope_type, prof_name)
      select "prof_id", "default_flag", "superuser_flag", "scope_type", "prof_name" from profile@stage2pmdb where "prof_name" = '<Admin Superuser>'
      1 rows inserted.
      select prof_id, default_flag, superuser_flag, scope_type, prof_name from profile_test
      PROF_ID DEFAULT_FLAG SUPERUSER_FLAG SCOPE_TYPE PROF_NAME
      -------------------- -------------------- -------------------- ---------------------------------------- --------------------------------------------------------------------------------
      12 ST_Global <Admin Superuser>

      So my assumption has been that this is a character set issue. The SQL Server database is using SQL_Latin1_General_CP1_CI_AS, the Oracle database AL32UTF8. I added the following lines to the initdg4msql.ora file:

      HS_LANGUAGE=american_america.WE8ISO8859P15
      HS_NLS_NCHAR=UCS2

      But having done a dump select I'm not so sure - its showing null for the flags.
      select dump("prof_id",16) as prof_id,
      dump("default_flag",16) as default_flag,
      dump("superuser_flag",16) as superuser_flag,
      dump("scope_type",16) as scope_type,
      dump("prof_name",16) as prof_name
      from profile@stage2pmdb
      where "prof_name" = '<Admin Superuser>'
      PROF_ID DEFAULT_FLAG SUPERUSER_FLAG SCOPE_TYPE PROF_NAME
      -------------------- -------------------- -------------------- ---------------------------------------- --------------------------------------------------------------------------------
      Typ=2 Len=2: c1,d NULL NULL Typ=1 Len=9: 53,54,5f,47,6c,6f,62,61,6c Typ=1 Len=17: 3c,41,64,6d,69,6e,20,53,75,70,65,72,75,73,65,72,3e

      Any suggestions?

      Adrian
        • 1. Re: DG4MSQL Unable to Extract VARCHAR(1) Columns
          Kgronau-Oracle
          What's your gateway version and could you also please post the create table statement as the table is defined at the SQL Server side?

          In addition what's the output when you execute in SQL*Plus: desc profile@stage2pmdb

          - Klaus
          • 2. Re: DG4MSQL Unable to Extract VARCHAR(1) Columns
            1006458
            The issue has been resolved. For future reference I'll complete the details:

            The Gateway is version 11.2.0.1.

            The Create statement from SQL Server is unavailable to me - Toad for SQL Server states that due to an invalid constraint it is unable to generate the SQL statement.

            Describe the profile table through the link produces the following:

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning and OLAP options

            SQL> describe profile@stage2pmdb;
            Name Null? Type
            ----------------------------------------- -------- ----------------------------
            prof_id NOT NULL NUMBER(10)
            default_flag NOT NULL VARCHAR2(1 CHAR)
            superuser_flag NOT NULL VARCHAR2(1 CHAR)
            scope_type NOT NULL VARCHAR2(12 CHAR)
            prof_name NOT NULL VARCHAR2(100 CHAR)
            update_date DATE
            update_user VARCHAR2(255 CHAR)
            create_date DATE
            create_user VARCHAR2(255 CHAR)
            delete_session_id NUMBER(10)
            delete_date DATE

            I requested the DBA add the following lines to the initialisation parameter file:

            HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
            HS_NLS_LENGTH_SEMANTICS=CHAR
            HS_LANGUAGE=american_america.WE8MSWIN1252

            In doing so the DBA noticed that the previously requested changes of

            HS_LANGUAGE=american_america.WE8ISO8859P15
            HS_NLS_NCHAR=UCS2

            had been made to the default initialisation file rather than the one matching the service name. All four changes were made to the correct initialisation file and now VARCHAR2(1) columns are populated when using the gateway.

            Thanks for the quick reply.

            Adrian