Problem Summary: How to remove whitespace characters before & after from character types on Oracle sources
Description:
When you have an Oracle database on the source that contains leading or trailing whitespace characters in their character datatypes but desire to have them stripped off when integrating to your target.
Details:
One way you can do this is by defining an Action of /ColumnProperties on your channel for your source group.
The text would look like /Name=mystring /CaptureExpression=”LTRIM(RTRIM({string},’ ‘),’ ‘)” or entered via the HVR GUI
But note, this approach can lead to slower capture performance.
An alternative is to use an /IntegrateExpression for replication instead but a /CaptureExpression for refresh/compare. A context should be added to switch between the two.
The solution is to define ColumnProperties /CaptureExpression=sqlexpr /Context=bulk & ColumnProperties /IntegrateExpression=sqlexpr /Context=!bulk instead
During refresh, use ‘bulk’ context (-Cbulk) to include expression in the select statement
During replication, use /IntegrateExpression instead to include expression in the insert statement
A basic test of this is to configure the replication in Oracle with the Capture, Integrate & /ColumnProperties actions. The setup would look instead like the below
So your channel now looks like this:
A basic test of this is to configure the replication in Oracle with the Capture, Integrate & /ColumnProperties actions;
Creating a table on the source
SQL> create table demo (id number primary key, string varchar2(20)); SQL> commit;
- Define your channel then Initialize and Refresh. Start your scheduler.
Inserting some test records in your source database
SQL> insert into spa values(1, ' mymy '); SQL> insert into spa values(5, ' icey '); SQL> insert into spa values(6, ' ic ey '); SQL> insert into spa values(8, ' right '); SQL> insert into spa values(9, ' ri ght '); SQL> commit; so your SOURCE data looks like this SQL> select id,string,length(string) from mytest.spa;
ID STRING LENGTH(STRING) ---------- -------------------- -------------- 1 mymy 10 5 icey 14 6 ic ey 15 8 right 12 9 ri ght 13 SQL> commit;And verify data on the target schema after a performing a Refresh
TARGET
SQL> select id, string, length(string) from <TargetSchema>.spb; ID STRING LENGTH(STRING) ---------- -------------------- -------------- 1 mymy 4 5 icey 4 6 ic ey 5 8 right 5 9 ri ght 6
For Compare for after initial refresh & subsequent compares make sure you select bulk comparison so that the conversion logic is applied to the data.
Comparison is successful
Then if we want to see the queries performed for the comparison you can enable tracing (Action /Name=HVR_SQL_TRACE /Value=2) the run compare again as demonstrated above. You can view this on the Compare ‘Output’ tab.
HVR first selects (into memory for this small data set) from the target for the ‘spa’ table
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: select
hvr_cn_."ID",
hvr_cn_."STRING"
from "SPA" hvr_cn_ [prepare]
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: "SELECT
HVR_CN_."ID",
HVR_CN_."STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 1
"STRING"= unistr('mymy')
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: "SELECT
HVR_CN_."ID",
HVR_CN_."STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 5
"STRING"= unistr('icey')
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: "SELECT
HVR_CN_."ID",
HVR_CN_."STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 6
"STRING"= unistr('ic ey')
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: "SELECT
HVR_CN_."ID",
HVR_CN_."STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 8
"STRING"= unistr('right')
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: "SELECT
HVR_CN_."ID",
HVR_CN_."STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 9
"STRING"= unistr('ri ght')
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: select
hvr_cn_."ID",
hvr_cn_."STRING"
from "SPA" hvr_cn_ [close]
mch1-cmp-ora_s-ora_t[spa-ora_t]: $HVR_SQL_TRACE[ora_t]: commit
Then select (into memory for this small data set) from the source table ‘spa’ & compares
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: select
hvr_cn_."ID",
(LTRIM(RTRIM(hvr_cn_."STRING" ,' '),' ')) as "STRING"
from "SPA" hvr_cn_ [prepare]
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: "SELECT
HVR_CN_."ID",
(LTRIM(RTRIM(HVR_CN_."STRING" ,' '),' ')) AS "STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 1
"STRING"= unistr('mymy')
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: "SELECT
HVR_CN_."ID",
(LTRIM(RTRIM(HVR_CN_."STRING" ,' '),' ')) AS "STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 5
"STRING"= unistr('icey')
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: "SELECT
HVR_CN_."ID",
(LTRIM(RTRIM(HVR_CN_."STRING" ,' '),' ')) AS "STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 6
"STRING"= unistr('ic ey')
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: "SELECT
HVR_CN_."ID",
(LTRIM(RTRIM(HVR_CN_."STRING" ,' '),' ')) AS "STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 8
"STRING"= unistr('right')
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: "SELECT
HVR_CN_."ID",
(LTRIM(RTRIM(HVR_CN_."STRING" ,' '),' ')) AS "STRING"
FROM "SPA" HVR_CN_"
:
"ID"= 9
"STRING"= unistr('ri ght')
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: select
hvr_cn_."ID",
(LTRIM(RTRIM(hvr_cn_."STRING" ,' '),' ')) as "STRING"
from "SPA" hvr_cn_ [close]
mch1-cmp-ora_s-ora_t[spa-ora_s]: $HVR_SQL_TRACE[ora_s]: commit
Table ‘spa’ is identical in location ‘ora_s’ and location ‘ora_t’ (5 rows). This bulk compare took 0.009 seconds
Then does the same for table ‘spb’
Etc..