Clob UDFs

These UDFs can be used to encrypt and decrypt the data stored in the CLOB data type.

pty.ins_encrypt_clob

This function is used to encrypt the data stored in a CLOB with an encryption data element.

Signature:

pty.ins_encrypt_clob(dataelement CHAR, input_data CLOB, scid INTEGER)

CAUTION: Ensure that the input data stored in the CLOB data type does not contain multibyte characters. If you pass data containing multibyte characters to the CLOB UDF, then an unexpected behaviour is observed.
For example: An error 'ORA-28579: network error during callback from external procedure agent' is returned or the input data is corrupted. For more information about CLOB data type, refer to the Oracle Help Center.

Parameters:

Returns:
This UDF returns the encrypted value as the CLOB data. >Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.ins_encrypt_clob('AES256','John',0) from dual;

Note: A maximum of 500 MB of input data can be protected using the pty.ins_encrypt_clob UDF.

pty.sel_decrypt_clob

This function is used to decrypt the encrypted data stored in a BLOB with an encryption data element.

Signature:

pty.sel_decrypt_clob(dataelement CHAR, input_data BLOB, scid INTEGER)

Parameters:

Returns:

• This UDF returns the decrypted value as the CLOB data.
• This UDF returns the decrypted value as an EMPTY_CLOB, when the user has no access to the database.

  Note: If you perform a unprotect operation with the input data as null or empty, then the output will be an EMPTY_CLOB.

Exception:
If the user does not have unprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty.sel_decrypt_clob('AES256',pty.ins_encrypt_clob('AES256','John',0),0) from dual;