transform data while extracting

User_WA691

dear all,

have requirement, to decrypt the encrypted data while performing integrated extract itself.

With initial extract it works fine, am able to decrypt data using function calls have written to decrypt but for CDC (integrated extract) is not decrypting.

Below is extract param:

  extract ICE1
  useridalias srcdev domain srcdev
  exttrail x0
  ALLOWDUPTARGETMAP
  SOURCECATALOG pdb1
  TABLE sch1.tab1, TARGET pdb1.sch1.tab1, 
  SQLEXEC (ID lookup1, 
	  QUERY 'select sch1.test_fun2( :p1 ) as resultcol from dual', 
	  PARAMS (p1 = c1)), 
  COLMAP (USEDEFAULTS, c1= @GETVAL(lookup1.resultcol)), 
  TOKENS (tk-commit-ts = @GETENV ('GGHEADER','COMMITTIMESTAMP'),TKN-CSCN = @GETENV('TRANSACTION','CSN'));

-----

• test_func2 from above param file has all the details to decrypt the data.
• all columns enabled for trandata
• when record not at target and update happens at source - record should come as insert - achived by having INSERTMISSINGUPDATES in replicats
• source, target table structres are same

Problem: If i insert then it works fine, but for update some column in source(not c1 which is used in params from above parameter content) whose record doesn't exists at target is inserting but always shows NULL for c1 at target(whose value is not null at source); Further observed that: function call itself is not happening in this case.

If i update same c1 column(used in params above) when that record doesn't exists at target then it works fine - it comes as INSERT along with value decrypted for c1 at target.

Not sure what am i missing. Would like to check if i'm using proper parameter design or any other way to achieve same - your inputs are welcome and thanks in advance.

--

Regards,

KK