Skip to Main Content
  • Questions
  • Moving data across DB link when one database uses pass-through configuration

Breadcrumb

May 4th

Question and Answer

Sergiusz Wolicki

Thanks for the question.

Asked: November 10, 2018 - 3:03 am UTC

Last updated: November 10, 2018 - 3:03 am UTC

Version:

Viewed 1000+ times

You Asked

There is a source with the settings below:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY CIS
NLS_CURRENCY ?.
NLS_ISO_CURRENCY CIS
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8DEC
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ?.
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

receiver with settings:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY CIS
NLS_CURRENCY р.
NLS_ISO_CURRENCY CIS
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET CL8MSWIN1251
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY р.
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

The question is: how to tighten the text in Cyrillic from source to receiver, without creating database objects on the source.

temporary solution is:
In the source database, a view is created in which all text fields are converted to RAW
select ... UTL_RAW.cast_to_raw(text_column) raw_column ... from...

DB link and view are created in the receiver base.
select ...UTL_RAW.cast_to_varchar2(raw_column) text_column ... from ...@DBLink_name;

Are there any solutions to the problem yet?

and we said...

There is no method to disable standard (expected) character set conversion that happens in a database link. Therefore, if one database stores your Cyrillic data incorrectly (e.g. in WE8DEC) and the other one correctly (e.g. in CL8MSWIN1251), then the conversion from WE8DEC to CL8MSWIN1251 will happen and will corrupt the data. To avoid this you can either protect the data by moving it as RAW or you can fix the character set of the original database.

Protecting data by casting it to RAW requires an intermediate view, as in your example, because otherwise you have no control over where the data type casting happens for a query. It may happen either in the source database, which is what we need, or in the target database, which would be too late, depending on the decisions made by the SQL engine.

You can change the (incorrect) character set of the original database using the CSREPAIR functionality of the DMU utility ( https://docs.oracle.com/cd/E89575_01/DUMAG/ch5_advanced_topics.htm#GUID-71456178-0A83-478B-BE03-272B73303FAC ) .

Another, non-trivial solution would be to use Golden Gate instead of DB link. In GG, you could override the source database character set to avoid the problematic conversion. Of course, GG replication and DB links are quite different approaches to data transfer.

 


Is this answer out of date? If it is, please let us know via a Comment