For purpose of data Encryption or Decryption create a package ENCRYPTDECRYPT Spec and Body.
In working examples below you will see how to Encrypt and Decrypt encrypted data. Take a close look on line of code lr_key RAW(255) := UTL_RAW.cast_to_raw(‘starpass’); instead of ‘starpass’ you should write your own password.
Examples:
|
1 2 3 4 5 6 7 8 9 10 11 |
--Encrypt: SELECT ENCRYPTDECRYPT.ENCRYPT('452345234423') FROM dual; --Result: 6F64A297CF96EA8849BEEBE8FF3E2EEB --Decrypt: SELECT ENCRYPTDECRYPT.DECRYPT('60D40B040A13579B2DEEC27354864D91') FROM dual; --Result: '452345234423' |
ENCRYPTDECRYPT Spec:
|
1 2 3 4 5 6 7 |
CREATE OR REPLACE PACKAGE ENCRYPTDECRYPT AS FUNCTION encrypt (p_text IN VARCHAR2) RETURN RAW; FUNCTION decrypt (p_raw IN RAW) RETURN VARCHAR2; END ENCRYPTDECRYPT; |
ENCRYPTDECRYPT Body:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE OR REPLACE PACKAGE BODY ENCRYPTDECRYPT AS lr_key RAW(255) := UTL_RAW.cast_to_raw('starpass'); FUNCTION encrypt (p_text IN VARCHAR2) RETURN RAW IS lc_text VARCHAR2(32767) := p_text; lt_enc_text RAW(32767); BEGIN lc_text := RPAD( lc_text, (TRUNC(LENGTH(lc_text)/8)+1)*8, CHR(0) ); DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(lc_text), key => lr_key, encrypted_data => lt_enc_text); RETURN lt_enc_text; END; FUNCTION decrypt (p_raw IN RAW) RETURN VARCHAR2 IS lc_decrypted VARCHAR2(32767); lc_return_dec VARCHAR2(32767); BEGIN DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw, key => lr_key, decrypted_data => lc_decrypted); lc_return_dec := UTL_RAW.cast_to_varchar2(lc_decrypted); RETURN RTRIM( lc_return_dec, CHR(0) ); END; END ENCRYPTDECRYPT; |