5 Replies Latest reply on Apr 18, 2013 10:02 AM by Kgronau-Oracle

    Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse

    1003598
      Hi all,

      I installed my enviroment as below.

      Server:
      Windows 7 Professional
      SQL Server 20012
      Character Set: UCS2

      Client:
      Linux REDHAT 5
      Oracle Warehouse 11gR2 (11.2.0.1)
      Oracle Database Gateway for MSSQL Server 11.2.0.1
      Character Set:AL32UTF8


      I installed the gateway for connecting the Oracle Warehouse with MSSQL DB. However, the characters are not readable, because the character of MSSQL DB is coded via one byte character, and the Oracle character is coded like multibyte character.

      Input:
      Table name on the MSSQL DB: Table_Name

      Output:
      Table name on the Oracle Warehouse Import Wirzard:◊T◊a◊b◊le◊_◊N◊a◊m◊e


      It causes error by importing the table, because the OWB doesn't allow the unreadable characters of table name.

      Do you have an idea for resolving it.

      Thanks and kind regards,

      Hip
        • 1. Re: Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse
          Kgronau-Oracle
          It would be always good to add the gateway init file.
          First I would make sure that HS_NLS_NCHAR=UCS2 is present in the gateway init file.

          It would be good also to see the source table definition and a gateway trace level 255 when you try to selct one row using the gateway in SQL*Plus.

          Edited by: kgronau on Apr 17, 2013 3:12 PM
          • 2. Re: Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse
            1003598
            My gateway init file is after I added the HS_NLS_NCHAR=UCS2 as your reply:

            initbob.ora

            HS_FDS_CONNECT_INFO=xxx.xx.x.xxx:1433//bob
            HS_FDS_TRACE_LEVEL=255
            HS_FDS_RECOVERY_ACCOUNT=RECOVER
            HS_FDS_RECOVERY_PWD=RECOVER
            HS_TRANSACTION_MODEL=READ_ONLY
            HS_LANGUAGE=UCS2
            HS_NLS_NCHAR=UCS2
            HS_NLS_LENGTH_SEMANTICS=CHAR

            Table: Table_Name
            Table structure:
            Column Name Data Type Allow Nulls
            ID int no
            Value int yes


            SQL> select * from Table_Name@bob;

            ID Value
            ---------- ----------
            1 32323
            2 1313
            3 13232

            For seeing the gateway trace, please use the URL:
            https://sites.google.com/site/nguyengiapnguyenshomepage/
            • 3. Re: Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse
              Kgronau-Oracle
              HS_LANGUAGE does not make sense when set to UCS2 - please set it to: HS_LANGUAGE=american_america.we8mswin1252

              There was an issue a while back using the gateway with mimicked views like all_users etc., but this should have been solved.

              Could you please open a SQL*Plus window on your Unix box, connect to the database and execute:

              !env
              select * from all_users@bob;

              Then provide me the output of SQL*Plus
              • 4. Re: Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse
                1003598
                Ok, I have changed it as you wrote:

                HS_FDS_CONNECT_INFO=100.30.4.157:1433//bob
                HS_FDS_TRACE_LEVEL=255
                HS_FDS_RECOVERY_ACCOUNT=RECOVER
                HS_FDS_RECOVERY_PWD=RECOVER
                HS_TRANSACTION_MODEL=READ_ONLY
                HS_LANGUAGE=american_america.we8mswin1252
                HS_NLS_NCHAR=UCS2
                HS_NLS_LENGTH_SEMANTICS=CHAR

                and I also added the listern.ora:

                (SID_DESC =
                     (SID_NAME=bob)
                     (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2)
                     (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_2/dg4msql/driver/lib;/u01/app/oracle/product/11.2.0/dbhome_2/lib)
                     (PROGRAM=dg4msql)
                )

                tnsname.ora:

                bob =
                (DESCRIPTION =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                     (CONNECT_DATA = (SID = bob))
                     (HS = OK)
                )


                SQL> select * from all_users@bob;

                USERNAME USER_ID CREATED
                --------------------------------------------------------------------------------
                public 0 08-APR-03

                dbo 1 08-APR-03

                guest 2 08-APR-03

                INFORMATION_SCHEMA 3 13-APR-09

                sys 4 13-APR-09

                db_owner 16384 08-APR-03

                db_accessadmin 16385 08-APR-03

                db_securityadmin 16386 08-APR-03

                db_ddladmin 16387 08-APR-03

                db_backupoperator 16389 08-APR-03

                db_datareader 16390 08-APR-03

                db_datawriter 16391 08-APR-03

                db_denydatareader 16392 08-APR-03

                db_denydatawriter 16393 08-APR-03


                14 rows selected.


                The Oracle Warehouse Builder still has the same error by the conversion of character sets.

                Hip
                • 5. Re: Conversion of character sets (UCS2 of MSSQL to AL32UTF8 of Oracle Warehouse
                  Kgronau-Oracle
                  the env output is missing .. I wanted to check if you have set NLS_LANG in your environment as it has an impact if you for example use NLS_LANG with AL32UTF8 and then start the listener that spawns the gateway. In those cases you have to set the HS_LANGUAGE to american_america.al32utf8 rather then we8mswin1252 (unfortunately a NLS_LANG setting is not visible in 11.2.0.2 gateway trace files)