Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Strange behaviour with Heterogenous service that connects to to SqlServer

Hi everyone,

I've created a connection to Sql Server 2016 from Oracle XE 18c using heterogenous services. Both Sql Server and Oracle are installed on the same server with Windows 2016

The connection works fine, but I've found a randomly problem.

I've create a pl/sql block that read the dictionary tables of Sql Server and extracts tables and column names of the schema. Then it creates the ddl command to generate a view on Oracle db for every table of Sql Server schema.

I don't know why, but sometimes the ddl is produces with strange characters. This is the pl/sql block:

declare

    k_owner    constant varchar2(30) := 'dbo';   

    v_sql       varchar2(32767);

   

BEGIN

  for r_tables in (select table_name from all_tables@tcmssql

                   where owner = k_owner and table_name != 'dual'

                   order by table_name

                ) loop

  begin

 

    v_sql := 'create or replace view ' || upper(r_tables.table_name) || ' as select ';

    for r_cols in(select tab."name"   as table_name

                       , col."name"   as column_name

                       , sc."name"    as schema_name

                  from sys.tables@tcmssql    tab

                 inner join sys.columns@tcmssql   col on tab."object_id" = col."object_id"

                 inner join sys.schemas@tcmssql   sc on sc."schema_id" = tab."schema_id"

                 where tab."name" = r_tables.table_name

                 and   sc."name" = k_owner

                 order by table_name

                        , "column_id") loop                 

    begin

       

        v_sql := v_sql || '"' || r_cols.column_name || '" as "' || upper(r_cols.column_name) || '", ';

       

    end;

    end loop;

   

    v_sql := rtrim(v_sql,', ') || ' from ' || r_tables.table_name || '@tcmssql';

   

    insert into appoggio values (v_sql); commit;

   

    --execute immediate v_sql;

   

  end;

  end loop;

end;

This is part of the result.

Correct:

create or replace view abc_items as

    select "id"                  as "ID"

           , "abc_id"              as "ABC_ID"

           , "function_num"        as "FUNCTION_NUM"

           , "data_id"             as "DATA_ID"

           , "group_id"            as "GROUP_ID"

           , "item_order"          as "ITEM_ORDER"

           , "key_properties_id"   as "KEY_PROPERTIES_ID"

           , "valid_from"          as "VALID_FROM"

           , "valid_to"            as "VALID_TO"

      from abc_items@tcmssql

Wrong:

create or replace view voucherman_vouchers as

    select "楤"                 as "楤"

           , "捯摥"                as "捯摥"

           , "癩獩扩汩瑹彣物瑥物慟楤"       as "癩獩扩汩瑹彣物瑥物慟楤"

           , "摥獣物灴楯"             as "摥獣物灴楯"

           , "扡牣潤敟灡瑴敲"           as "扡牣潤敟灡瑴敲"

           , "獡汥彡牴楣汥彩"           as "獡汥彡牴楣汥彩"

           , "畳慧敟癯畣桥牟楤"          as "畳慧敟癯畣桥牟楤"

           , "畳慧敟摩獣潵湴彩"          as "畳慧敟摩獣潵湴彩"

           , "楳獵敟浯摥"             as "楳獵敟浯摥"

           , "灡牴楡江畳慧敟浯摥"         as "灡牴楡江畳慧敟浯摥"

           , "摩獣潵湴彤楦晥牥湣敟慲瑩捬敟楤"   as "摩獣潵湴彤楦晥牥湣敟慲瑩捬敟楤"

           , "灡牴楡江畳慧敟癯畣桥牟楤"      as "灡牴楡江畳慧敟癯畣桥牟楤"

           , "浩湟牥晵湤彡浯畮"          as "浩湟牥晵湤彡浯畮"

           , "浡硟牥晵湤彡浯畮"          as "浡硟牥晵湤彡浯畮"

           , "畳慢汥彯晦汩湥"           as "畳慢汥彯晦汩湥"

           , "潮汩湥彣桥捫"            as "潮汩湥彣桥捫"

           , "瑩捫整彴敭灬慴敟楤"         as "瑩捫整彴敭灬慴敟楤"

           , "癡汩摩瑹彤慹"            as "癡汩摩瑹彤慹"

           , "楳獵敟獨潰彯湬"           as "楳獵敟獨潰彯湬"

           , "步数彵獥摟癯畣桥牟摡祳"       as "步数彵獥摟癯畣桥牟摡祳"

           , "灡祢慣歟癯畣桥"           as "灡祢慣歟癯畣桥"

           , "浵汴楰汥彳敬"            as "浵汴楰汥彳敬", "慬汯睟摩獣潵湴"           as "慬汯睟摩獣潵湴"

      from voucherman_vouchers@tcmssql

The behaviour is randomly, if I execute again the block, some ddl that previously were correct, become wrong.

This is the init of hs service:

# 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 = tcpos_mssql

HS_FDS_TRACE_LEVEL = on

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Any ideas?

Comments
Post Details
Added on Sep 5 2019
0 comments
280 views