Decrypt with password in PL/SQL

This article describes how to decrypt password encrypted OpenPGP data inside the Oracle(c) DB with the help of OraPGP PL/SQL package.

We will need the encryption password in order to decrypt the data.

1. Decrypting CLOB
2. Decrypting BLOB
3. Exception handling

1

Decrypting CLOB

The example below decrypts a previously PGP encrypted with a password CLOB field. The decrypted data is returned as a CLOB field too.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DECLARE
  MESSAGE CLOB;
  PASSWORD VARCHAR2(200);
  v_Return CLOB;
BEGIN
  MESSAGE := NULL;
  PASSWORD := 'my password';
 
  SELECT encrypted_data_field INTO MESSAGE
  FROM my_data_table
  WHERE my_id = 1000;
 
  v_Return := ORA_PGP.DECRYPT_CLOB_PASSWORD(
    DATA => MESSAGE,
    password => PASSWORD
  );
END;

Decrypting BLOB

Decrypting a BLOB field, which was PGP encrypted with a password,  is very similar to decrypting a VARCHAR2 field:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE
  MESSAGE BLOB;
  PASSWORD VARCHAR2(200);
  v_Return BLOB;
BEGIN
  MESSAGE := NULL;
  KEY_PASSWORD := 'key password';
 
  SELECT encrypted_data_field INTO MESSAGE
  FROM my_data_table
  WHERE my_id = 1000;
 
  v_Return := ORA_PGP.DECRYPT_BLOB(
    DATA => MESSAGE,
    password => PASSWORD
  );
 
  -- v_Return now contains the decrypted data
END;

After the decryption, if we know that the BLOB contains textual data, we have to convert it into CLOB respecting the encoding of the data. A demo conversions routine from BLOB to CLOB can be found on GitHub.

1

Exception handling

For simplicity exception handling is not included in the above example. For production systems you shall apply PL/SQL exception handling code as described.

Summary

This article illustrated how to decrypt previously encrypted with a password PGP data inside the Oracle(c) database with PL/SQL.

You may also check how to verify if the data is indeed encrypted with a password before performing any operations.

List of methods used: