DECRYPT_CHAR Function

The DECRYPT_CHAR function accepts as its first argument an encrypted_data character string that can have any character type (CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR). You must specify a password as its second argument, unless the SET ENCRYPTION statement has specified for this session the same session password by which the first argument was encrypted.

The DECRYPT_CHAR function also accepts as its first argument an encrypted_data large object of type BLOB or CLOB. You must specify a password as its second argument, unless the SET ENCRYPTION statement has specified as the default for this session the same password by which the first argument was encrypted. If the call to DECRYPT_CHAR is successful, it returns a CLOB large object that contains the plain text version of the encrypted_data argument.

If the call to DECRYPT_CHAR with an encrypted string argument is successful, it returns a character string that contains the plain text version of the encrypted_data argument. The following example returns a character string containing a decrypted value from the ssid column of the engineers table for the row whose empno value is 287:

SELECT DECRYPT_CHAR (ssid) FROM engineers WHERE empno = 287;

If the first argument to DECRYPT_CHAR is not an encrypted value, or if the second argument (or the default password specified by SET ENCRYPTION) is not the password that was used when the first argument was encrypted, HCL OneDB™ issues an error, and the call to DECRYPT_CHAR fails. (See the description of the GETHINT Function for one possible action to take when you cannot remember the password that was used for encryption.)

Do not use DECRYPT_CHAR (or any other decryption function) to create a functional index on an encrypted column. This would store the decrypted values as plain text data in the database, defeating the purpose of encryption.

For additional information about using data encryption in column values of HCL OneDB databases, see Encryption and decryption functions, and SET ENCRYPTION PASSWORD statement.