Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Decrypting Data

So far, I have focused on encrypting the data; let’s see how to decrypt it to get back the original value with the DES3DECRYPT function . In the following PL/SQL block, I will create an encrypted value from cleartext and then decrypt it.

    DECLARE
       l_enc_val   VARCHAR2 (2000);
       l_dec_val   VARCHAR2 (2000) := 'Clear Text Data';
       l_key       VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP';
    BEGIN
       l_enc_val := get_enc_val (l_dec_val, l_key, '12345678');
       l_dec_val :=
          DBMS_OBFUSCATION_TOOLKIT.des3decrypt
                  (input_string      => UTL_RAW.cast_to_varchar2
                                                              (HEXTORAW (l_enc_val)
                                                              ),
                   key_string        => l_key
                  );
       DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val);
    END;
    /

The output is:

    Decrypted Value = s}?2+¬xt Data

    PL/SQL procedure successfully completed.

Wait! The decrypted value is different from the input given. What went wrong?

Note the parameters to the DES3DECRYPT function. Have you supplied the IV to it? Because an IV was specified during the encryption process, it must be specified during decryption, as well. Let’s rewrite the block with the IV value of 12345678:

    DECLARE
       l_enc_val   VARCHAR2 (2000);
       l_dec_val   VARCHAR2 (2000) := 'Clear Text Data';
       l_key       VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP';
    BEGIN
       l_enc_val := get_enc_val (l_dec_val, l_key, '12345678');
       l_dec_val :=
          DBMS_OBFUSCATION_TOOLKIT.des3decrypt
                  (input_string      => UTL_RAW.cast_to_varchar2
                                                              (HEXTORAW (l_enc_val)
                                                              ),
                   key_string        => l_key,
                   iv_string         => '12345678'
                  );
       DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val);
    END;
    /

The output is as expected: ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page