You may come across a situation where a developer or designer needs some referential data from a data but it would be inappropriate to send them the real information. Here’s where the TRANSLATE and DBMS_RANDOM packages can help.
TRANSLATE changes a character to another one chosen from a string. It matches in order so if the first character matches then it will substitute it with the first character from the given string.
DBMS_RANDOM can generate random numbers.
Using these in combinations allows you to substitute a number with a randomly generated one.
Here’s an example to randomise a credit card number:
select translate(CARD_NUMBER,'0123456789',round(dbms_random.value(0000000000000000,99999999999999999)) as HIDDEN_CARD_NUMBER from CARDS;
And one for IP addresses, the value here is that the context remains so the field lengths are maintained as are the . separators:
select translate(IP_ADDRESS,'0123456789',round(dbms_random.value(000000000000,9999999999999))) as HIDDEN_IP_ADDRESS from IPS;
Playing around with these gives a powerful tool to avoid exposing your data to unauthorised parties whilst allowing them to see enough of the data context to do their job. Hope this is useful.