golendgate(34)--COLMAP source expression

COLMAP source expression

源端表结构：

SQL> desc gis_test.baobao14;

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

PXID     NUMBER                                

FANG     VARCHAR2(10) Y                        

TDD      VARCHAR2(20) Y                        

AWR      VARCHAR2(15) Y                        

TNAME    VARCHAR2(20) Y                        

PHONE_NO NUMBER       Y  

 

目标端表结构：

SQL> desc gis_test.baobao14;

Name         Type         Nullable Default Comments

------------ ------------ -------- ------- --------

PXID         NUMBER                                

FANG         VARCHAR2(10) Y                        

DNAME        VARCHAR2(20) Y                        

AREA_CODE    NUMBER       Y                         

PHONE_PREFIX NUMBER       Y                        

PHONE_NUMBER NUMBER       Y 

 

目标端表列DNAME映射源端表列TNAME,

目标端表列AREA_CODE,PHONE_PREFIX, PHONE_NUMBER映射源端表列PHONE_NO

操作步骤：

1.使用defgen生成源端数据定义文件

略，见本博客另外一篇专门写defgen的文档.

2.使用COLMAP映射列

3.使用@STREXT函数分解列值

 

举例：

抽取进程配置：

EXTRACT s_ex_mid

-- SETENV (NLS_LANG = CHINESE_CHINA.ZHS16GBK)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

SETENV (ORACLE_SID = "dbking")

USERID ggs@dbking, PASSWORD ggs

TRANLOGOPTIONS ARCHIVEDLOGONLY

-- TRANLOGOPTIONS ASMUSERsys@asm, ASMPASSWORD grid sysdba

TRANLOGOPTIONS DBLOGREADER

TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE dbking /arch/archivelog

TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576

EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/cc

TABLE gis_test.baobao14, COLSEXCEPT (tdd,awr);

 

投递进程配置：

EXTRACT pump_un

USERID ggs@dbking, PASSWORD ggs

RMTHOST 10.23.5.71, MGRPORT 7809

RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/pp

PASSTHRU

TABLE gis_test.baobao14;

 

 

复制进程配置：

REPLICAT rep_li

USERID ggs, PASSWORD ggs

ASSUMETARGETDEFS

DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND

SOURCEDEFS /u01/app/oracle/ggs/dirdef/baobao14.def

-- MAP gis_test.test1, TARGET gis_test.test1 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 9116587);

MAP gis_test.baobao14, TARGET gis_test.baobao14,

colmap (USEDEFAULTS, dname = tname,

AREA_CODE =

@STREXT (PHONE_NO, 1, 3),

PHONE_PREFIX =

@STREXT (PHONE_NO, 4, 6),

PHONE_NUMBER =

@STREXT (PHONE_NO, 7, 10))

;

-- MAP gis_test.baobao14, TARGET gis_test.baobao14,

-- colmap (

-- pxid=pxid, fang=fang)

-- ;

 

测试数据

源端查看：

 

select * from gis_test.baobao14;

         PXID     FANG   TDD     AWR    TNAME            PHONE_NO

1          18000  sss        mmm  nnn      wangtian         1234567890

2          19000  sss        mmm  nnn      wangtian         1002003000

3          11000  nn        aa        hh                   

4          12000  bb        cc         hh                   

5          13000  bb        cc         hh                   

6          14000  bb        cc         hh                   

7          15000  bb        cc         hh        xiangyang       

8          16000  bb        cc         hh        xiangyang       

9          17000  bb        cc         hh        lixia    

10        1000    a          b          c                     

11        2000    b          c          e                     

12        3000    c          d          f                      

13        4000    d          e          g                     

14        5000    e          e          g                     

15        6000    g          e          g                     

16        7000    f           e          g                     

17        8000    kk         yy         mm                 

18        9000    xx         jj          hh                   

19        10000  uu        jj          hh                   

 

 

目标端查看：

         PXID     FANG   DNAME           AREA_CODE    PHONE_PREFIX           PHONE_NUMBER

1          13000  bb                                           

2          14000  bb                                           

3          17000  bb        lixia                            

4          18000  sss        wangtian         123           456                   7890

5          19000  sss        wangtian         100           200                   3000

6          1000    a                                             

7          2000    b                                             

8          3000    c                                             

9          4000    d                                             

10        5000    e                                             

11        6000    g                                             

12        7000    f                                              

13        8000    kk                                            

14        9000    xx                                            

15        10000  uu                                           

16        11000  nn                                           

17        12000  bb                                           

18        15000  bb        xiangyang                               

19        16000  bb        xiangyang       

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21266384/viewspace-766977/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21266384/viewspace-766977/