How to mask the table data using that column name while exporting the table [using EXPDP]

Question

I want to mask a table data while exporting using expdp.

Table "TEST_MASK" has two columns: 'address'(varchar(100)) & 'balance'(varchar(50))
Here we need to mask address column using this column name and balance column value.

Test_mask table:

address                    |            balance
------------------------------------------------  
b-9,6th yrd, germany       |           557586  
4th main,hoysang-09832     |           75846  
karola,digha europe        |           89657 

create table test_mask(address varchar2(100),balance varchar2(100));
insert command is:
insert into test_mask values ('b-9,6th yrd, germany','557586');
insert into test_mask values ('4th main,hoysang-09832','75846');
insert into test_mask values ('karola,digha europe','89657');

Masking logic:

1.

'column_name'||REGEXP_REPLACE(substr(COLUMN,1,5),'[[alpha]]','X')
---------- eg:addressX-9,6 (first row is masked)
---- column_name: which is passed to function.

2. Take first 4 digit from balance and append with column name (address)
-----eg:5575address (don't append just 'address' string because i have to use this for many tables and columns, take column name which is passed to function in REMAP_DATA )

I tried with below pl/sql function to mask the data using masking logic-1 given below:

 create or replace package test_pcg as
function test_func(p_in in varchar2) return varchar2;
  end ;
     create or replace package body test_pcg as
    function test_func(p_in in varchar2) return varchar2
  as
    column_name varchar2(100):=p_in;
   begin 
 if p_in is not null then
   return 
          column_name||REGEXP_REPLACE(substr(p_in,1,5),'[[alpha]]','X'); 
   else
  return p_in;
 end if;
end;
end;
/

EXPDP command is given below:

expdp SYSTEM/****** schemas=schema_name directory=DATA_PUMP_DIR dumpfile=DATA_PUMP.dmp tables=Test_mask logfile= DATA_PUMP.log 
REMAP_DATA=schema_name.Test_mask.address:schema_name.test_pcg.test_func;

Here I am not getting column_name('address') instead of column_name, I am getting column value.

column_name||REGEXP_REPLACE(substr(p_in,1,5),'[[alpha]]','X')=addressX-9,6 (expected)
--------------------------- b-9,6th yrd, germanyX-9,6 (actual)

Please guide me to solve this and how can i proceed with masking logic 2. Thank You.

How to mask the table data using that column name while exporting the table [using EXPDP]

0 Answers0