How to use DBMS_CRYPTO package

• You can encrypt and decrypt string with  this package. Example: Created p_encrypt package, and encrypt and decrypt string with p_encrypt package.

CREATE OR REPLACE PACKAGE MURATK_DBA.p_encrypt
AS
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW;
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2;
END p_encrypt;
/

CREATE OR REPLACE PACKAGE BODY MURATK_DBA.p_encrypt
AS
--DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
--IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
--THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
  G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
  G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
  G_KEY RAW(250) := utl_i18n.string_to_raw
                      ( data => G_STRING,
                        dst_charset => G_CHARACTER_SET );
  G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256 
                                    + dbms_crypto.chain_cbc 
                                    + dbms_crypto.pad_pkcs5;
  
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
  IS
    l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, G_CHARACTER_SET );
    l_encrypted RAW(32);
  BEGIN
    l_ssn := utl_i18n.string_to_raw
              ( data => p_ssn,
                dst_charset => G_CHARACTER_SET );

    l_encrypted := dbms_crypto.encrypt
                   ( src => l_ssn,
                     typ => G_ENCRYPTION_TYPE,
                     key => G_KEY );
                     
    RETURN l_encrypted;
  END encrypt_ssn;
  
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
  IS
    l_decrypted RAW(32);
    l_decrypted_string VARCHAR2(32);
  BEGIN
    l_decrypted := dbms_crypto.decrypt
                    ( src => p_ssn,
                      typ => G_ENCRYPTION_TYPE,
                      key => G_KEY );

    l_decrypted_string := utl_i18n.raw_to_char
                            ( data => l_decrypted,
                              src_charset => G_CHARACTER_SET );
    RETURN l_decrypted_string;
  END decrypt_ssn;
  
END p_encrypt;
/

• Connect to database

#sqlplus / as sysdba

SQL> select muratk_dba.p_encrypt.encrypt_ssn('şifrelenecek metin') encrypt_data from dual;

Encrypt_data

--------------------------------------------------------------------------------

991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2

SQL> select muratk_dba.p_encrypt.decrypt_ssn('991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2')  DECRYPT_DATA  from dual;

DECRYPT_DATA

--------------------------------------------------------------------------------

şifrelenecek metin