Hi,
I have a created the DBMS_OBFUSCATION procedure to encrypt all Sensitive existing data in the table.

Following is the procedure for


CREATE OR REPLACE PROCEDURE "DESENCRYPT_ORACLE" IS
input_string CHAR(16);
key_string VARCHAR2(8):= '123ABC67';
pid NUMBER (11);
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);

error_in_input_buffer_length EXCEPTION;

PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';



CURSOR c1 IS SELECT ssn, person_id FROM CPP_PERSON_TEST WHERE ssn IS NOT NULL AND ssn !='--' AND LENGTH(ssn)=11;

BEGIN

OPEN c1;
LOOP
FETCH c1 INTO input_string,pid;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Input string: '||input_string || 'Key string is ' || key_string || 'Encrypted string is ' || encrypted_string);

dbms_obfuscation_toolkit.Desencrypt(input_string => input_string ,key_string => key_string,encrypted_string => encrypted_string );

UPDATE CPP_PERSON_TEST
SET new_ssn=encrypted_string
WHERE person_id=pid;

END LOOP;
CLOSE c1;

EXCEPTION
WHEN error_in_input_buffer_length THEN
DBMS_OUTPUT.PUT_LINE('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);

END;
/

Now, I am trying to create a procedure/trigger to encrypt any new incoming data. The procedure, should accept the value, encrypt it and insert the encrypted value in the table. Please advice.

Thanks