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

I had to use this solution to validate the data where the source database was a varchar2 data type, and the target was a numeric data type.

The requirement was if the value in the trail file was 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 ));