Oracle PL/SQL for DBAs

Encrypting RAW Data

We’ve talked a bit about the use of RAW data. Here we’ll explore how you can encrypt data whose datatype is RAW by taking advantage of the fact that within the DBMS_OBFUSCATION_TOOLKIT package, the DES3ENCRYPT and DES3DECRYPT programs are overloaded. That means that they have several variants. Each has a procedure format in which exactly the same parameters are passed as input parameters and the return value is passed back to the user using an OUT parameter named either encrypted_string or decrypted_string (depending on whether you are encrypting or decrypting). The functions and procedures are also overloaded to accommodate the RAW datatype for the parameters. You will use these variants if you need to manipulate raw data such as large objects (LOBs).

It is certainly possible to convert RAW values as shown here when doing encryption and decryption:

    /* File on web: enc_raw.sql */

    CREATE OR REPLACE FUNCTION enc_raw (
       p_in_val   IN   VARCHAR2,
       p_key      IN   VARCHAR2,
       p_iv       IN   VARCHAR2
    )
       RETURN VARCHAR2
    IS
       l_enc_val   RAW (200);
       l_in_val    RAW (200);
       l_iv        RAW (200);
    BEGIN
       l_in_val :=
          UTL_RAW.cast_to_raw (RPAD (p_in_val,
                                     (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8
                                     )
                                    )
                              );
       l_iv :=
           UTL_RAW.cast_to_raw (RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8)));
       l_enc_val :=
          DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input      => l_in_val,
                                                KEY        => p_key,
                                                iv         => l_iv
                                               );
       RETURN RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val));
    END;
    /

However, the additional processing required for conversion between the RAW ...