We are trying to mask a column which is has a number and the length is 15 (normally) however for some data, the length is 14 or 13 as the Zeros at the start are omitted in the DB column.
So we want to mask the number in specific format as:
1.keep the first 6 digit as it is
2. Mask the next 7 digit with formatencrypt function
3. keep the last 2 digit as it is
Now the 7 digit we are masking here with FORMATENCRYPT is also present in another column ( actually that is parent column).
As we expect that if FORMATENCRYPT is applied on two columns in any table and if the source number is same then the generated number will also be same.
However here the new no generated is different if
1. the length is not 13 (normally it is 15)
2. if we change the conditions to ignore the digits while masking.
Portal Version we are using -Version: "220.127.116.11"
DB- Oracle Database 19c Enterprise Edition Release 18.104.22.168.0 - Production
Regarding FORMATENCRYPT issue, FORMATENCRYPT function doesn't encrypt the first digit for numeric types, while there is no such limitation for string/text types and that's why you have to use start index increased by 1. This is documented in product documentation: