Problem Summary:  How to remove whitespace characters before & after from character types on Oracle sources

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #17790
    Donna Zehl
    Keymaster

    Problem Summary:  How to remove whitespace characters before & after from character types on Oracle sources

     

    Description:

    When you have an Oracle database on 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 select statement

    During replication, use /IntegrateExpression instead to include expression in the insert statement

     

    A basic test of this is to configure 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 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

     

    The 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 rirst selects (in to 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 (in to 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..

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us