SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (4,'Murdy','Jill', null,'930 Eady St','New York','NY','45452','6458','212', '634-7733','Wilton Company');
1 row created.
SQL>
SQL> ALTER TABLE emp
2 ADD ccn VARCHAR2(2048);
Table altered.
SQL>
SQL> CREATE TABLE emp_id_key_table(emp_id NUMBER PRIMARY KEY,key_value VARCHAR2(80));
Table created.
SQL>
SQL>
SQL> CREATE or REPLACE FUNCTION ccn_decrypt(v_emp_id IN NUMBER, v_ccn IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 v_key VARCHAR2(80);
5 v_decrypted_string VARCHAR2(2048);
6 BEGIN
7 SELECT key_value INTO v_key FROM emp_id_key_table WHERE emp_id = v_emp_id;
8
9 dbms_obfuscation_toolkit.DES3Decrypt(input_string => v_ccn, key_string => v_key, decrypted_string => v_decrypted_string);
10 RETURN (v_decrypted_string);
11 END;
12 /
Function created.
SQL>
SQL> select emp_id, ccn_decrypt(emp_id, ccn) from emp where ccn is not null;
no rows selected
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table emp_id_key_table;
Table dropped.