GoldenGate numeric data validation with REGEXP

Here is a simple validation mapping to verify if the value in the trail file is numeric.

I had to use this where the source was a varchar2 and the target was a numeric field.

The requirement is if the value in the trail file is not numeric just add a “zero” in the field.

MAP SOURCE.T1, TARGET TARGET.T1, &
SQLEXEC (ID lookup_st, &
QUERY " select REGEXP_INSTR(:vac_num, 'a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z', 1, 1, 0, 'i') as ac_num_true from dual ", &
PARAMS(vac_num = num)), &
COLMAP ( num = @if (lookup_st.ac_num_true = 0,num,0), text = text ));