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?