alter table dept add(mask_field varchar2(100),mask_value varchar2(100));

update dept set mask_field='ename,job,',mask_value=q'[ename||x,job||y,]' where deptno=10;
update dept set mask_field='sal',mask_value=q'[xxxx]' where deptno=20;

select empno,decode('sal',mask_field,mask_value,sal) sal,
ename,job,dept.deptno from emp,dept
where emp.deptno=dept.deptno;

with temp as
(select 'xxxMANUxxx,BATHAM' mask_field, 'MANU' ename from dual)
select decode(instr(substr(mask_field,1,instr(mask_field,',')),ename),0,'NOT FOUND','FOUND') from temp;

create or replace function test_p(p_str in varchar2) return varchar2 is
  l_str varchar2(100);
begin
  select ename into l_str from test_emp;
   return l_str || 'EXTRA';
  
  --return p_str||'EXTRA';

end;

create table test_emp(ename varchar2(20)); insert into test_emp(ename) values('ename'); commit;

SELECT test_p(ENAME) FROM EMP;

alter table dept add(mask_field varchar2(100),mask_value varchar2(100));

update DEPT set MASK_FIELD='ename,job,',MASK_VALUE=q'[ename||x,job||y,]' where DEPTNO=10;
update DEPT set MASK_FIELD='sal,',MASK_VALUE=q'[sal||xxxx,]' where DEPTNO=20;

COMMIT;
CREATE OR replace FUNCTION Test_pp(p_str VARCHAR2, p_col VARCHAR2, p_val VARCHAR2) 
RETURN VARCHAR2 
IS 
  l_str    VARCHAR2(100) := p_str; 
  l_val    VARCHAR2(100) := p_val; 
  l_col    VARCHAR2(100) := p_col; 
  l_found  VARCHAR2(100); 
  l_fndval VARCHAR2(100); 
  l_cnt    NUMBER := 0; 
  l_st     NUMBER := 0; 
BEGIN 
    WHILE( Instr(l_str, ',') > 0 ) LOOP 
        l_cnt := l_cnt + 1; 
        l_found := Substr(l_str, 1, Instr(l_str, ',') - 1); 
        l_fndval := Substr(l_val, 1, Instr(l_val, ',') - 1); 
        IF l_found = l_col THEN 
          l_st := 1; 
          exit; 
        END IF; 
        l_str := Substr(l_str, Instr(l_str, ',') + 1); 
        l_val := Substr(l_val, Instr(l_val, ',') + 1); 
    END LOOP; 

    IF l_st = 0 THEN 
      RETURN NULL; 
    ELSE       
      RETURN l_fndval; 
    end if; 
end;  

select NVL(TEST_PP(MASK_FIELD,'sal',MASK_VALUE),SAL) SAL ,
NVL(TEST_PP(MASK_FIELD,'ename',MASK_VALUE),ENAME)ENAME, 
NVL(test_pp(mask_field,'job',mask_value),job)job
from emp,dept
where emp.deptno=dept.deptno;

alter table emp add ssn varchar2(15);

update emp set ssn=	'101-64-9265'	 where empno=	7369	;
update emp set ssn=	'106-41-6883'	 where empno=	7499	;
update emp set ssn=	'115-93-9248'	 where empno=	7521	;
update emp set ssn=	'130-56-7205'	 where empno=	7566	;
update emp set ssn=	'162-28-7445'	 where empno=	7654	;
update emp set ssn=	'163-79-5574'	 where empno=	7698	;
update emp set ssn=	'165-01-9076'	 where empno=	7782	;
update emp set ssn=	'175-58-2875'	 where empno=	7788	;
update emp set ssn=	'187-16-8016'	 where empno=	7839	;
update emp set ssn=	'192-10-5480'	 where empno=	7844	;
update emp set ssn=	'302-07-7274'	 where empno=	7876	;
update emp set ssn=	'327-85-5856'	 where empno=	7900	;
update EMP set SSN=	'826-73-4221'	 where EMPNO=	7902	;
update emp set ssn=	'626-73-4298'	 where empno=	7934	;

update dept set mask_field=	'ssn~sal~'	,mask_value	='REGEXP_REPLACE(ssn,''(\-[[:digit:]]{4})'',''-XXXX'')~REGEXP_REPLACE(sal,''([[:digit:]](*))'',''X'')~'	 where deptno=10;
update dept set mask_field=	'ssn~ename~'	,mask_value='REGEXP_REPLACE(ssn,''(\-[[:digit:]]{2}\-)'',''-XX-'')~REGEXP_REPLACE(ename,''([[:alnum:]](*))'',''X'') ~'	 where deptno=20;
update dept set mask_field=	'ssn~comm~'	,mask_value='REGEXP_REPLACE(ssn,''([[:digit:]]{3}\-)'',''XXX-'')~REGEXP_REPLACE(comm,''([[:digit:]](*))'',''X'')~'	 where deptno=30;

commit;

CREATE OR replace FUNCTION get_value(p_str IN VARCHAR2) 
RETURN VARCHAR2 
IS 
  L_STR      varchar2(100); 
  L_QUERY    varchar2(4000); 
  c1         SYS_REFCURSOR; 
BEGIN 
    IF p_str IS NULL THEN 
      RETURN NULL; 
    END IF; 

    L_QUERY := 'select ' || p_str || ' from dual '; 

    OPEN c1 FOR L_QUERY; 
    LOOP 
        FETCH c1 INTO l_str; 
        exit WHEN c1%NOTFOUND; 
    END LOOP; 

    return L_STR; 
END;  

--test_pp is same as the above procedure but replace , with ~


SELECT dept.deptno,empno,hiredate,job, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'ename', mask_value), 'ename', '''' ||ename ||'''') ), ename) ENAME, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'ssn', mask_value), 'ssn', '''' || ssn || '''')), ssn)SSN, 
       NVL(GET_VALUE(replace(TEST_PP(MASK_FIELD, 'sal', MASK_VALUE), 'sal', '''' || SAL || '''')), SAL) SAL, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'comm', mask_value), 'comm', '''' ||comm ||'''')), comm) comm 
from   EMP, DEPT 
WHERE  emp.deptno = dept.deptno;