Masking Data based on Column Value Oracle

user_6500987 0
Viewed 147

I am creating a stored procedure in Oracle where I am required to mask employee ID in the SELECT statement.

I would like that the same masking number be applied to the all the rows with the same Employee ID.

EMP_ID     MASK_ID
------     -------
212        USER9293
443        USER6474
212        USER9293

Currently in my MSSQL SELECT I accomplish this by:

CONCAT(‘USER’, CONVERT(NVARCHAR(6), (1.0 + FLOOR(250000 * RAND(CONVERT(VARBINARY, EMP_ID))))))

I would like to accomplish a similar solution in Oracle. I believe a SEED using DBMS_RANDOM would probably get me close but I am not sure how to pass the column in as it return an error every time.

Any tips?

plsql oracle sql
2 Answers

Often people ask for "random" when they don't actually need that. It seems that "hash" should work fine for your problem.

If you want to append a four-digit number (between 1 and 9999, perhaps with leading zeros) to 'USER', you could do something like what I show below. If you want numbers between 1000 and 9999, you can adjust the math yourself.

Note that you may still give a seed (as the third argument to ora_hash) if you need that for some reason. The default seed is 0, so this is deterministic even if you don't specify the seed.

with
  inputs (emp_id) as (
    select 212 from dual union all
    select 443 from dual union all
    select 212 from dual union all
    select 400 from dual
  )
select emp_id,
       'USER' || to_char(1 + ora_hash(emp_id, 9998), 'fm0000') as mask_id
from   inputs
;

EMP_ID  MASK_ID  
------  ---------
   212  USER8297 
   443  USER5176 
   212  USER8297 
   400  USER2606
Admin1

1. Use utl_raw.cast_to_raw to convert EMP_ID to binary

2. You must use the plsql block to use DBMS_RANDOM.SEED.

add function my_random_emplID to generate a random number with binary type

create function my_random_emplID(p_seed in number default null)
return number is
begin
  if p_seed is not null then
    dbms_random.seed(utl_raw.cast_to_raw(p_seed));
  end if;
  return dbms_random.value;
end;
/

get mask_id

SELECT
  CONCAT('USER',CAST(1.0 + FLOOR(250000 * my_random_emplID(1452)) as varchar(6)))
FROM
  dual;

demo in db<>fiddle

user_150195360
Related