PolarDB for PostgreSQL(Compatible with Oracle) supports the MD5 functions and stored procedures from the DBMS_OBFUSCATION_TOOLKIT package, providing Oracle-compatible MD5 hashing for data integrity verification.
Prerequisites
Before you begin, make sure that the DBMS_OBFUSCATION_TOOLKIT plug-in is available in your database:
Clusters created on or after March 30, 2020: The plug-in is created automatically when you create a database. No additional steps are required.
Clusters created before March 30, 2020: Run the following statement after creating the database to install the plug-in manually:
CREATE EXTENSION polar_dbms_obfuscation_toolkit;
For the full Oracle reference, see DBMS_OBFUSCATION_TOOLKIT.
Supported subprograms
The following overloads of dbms_obfuscation_toolkit.md5 are supported:
| Name | Type | Input parameter | Output parameter |
|---|---|---|---|
| dbms_obfuscation_toolkit.md5 | Function | BYTEA | RAW_CHECKSUM |
| dbms_obfuscation_toolkit.md5 | Stored procedure | BYTEA | RAW_CHECKSUM |
| dbms_obfuscation_toolkit.md5 | Function | CHARACTER VARYING | CHARACTER VARYING |
| dbms_obfuscation_toolkit.md5 | Stored procedure | CHARACTER VARYING | CHARACTER VARYING |
Parameters
Each overload accepts the following parameters:
RAW overload (BYTEA input)
| Parameter | Direction | Type | Description |
|---|---|---|---|
input | IN | BYTEA | Binary data to hash. |
checksum | OUT | RAW_CHECKSUM | 128-bit MD5 digest of the input (stored procedure only). |
VARCHAR2 overload (CHARACTER VARYING input)
| Parameter | Direction | Type | Description |
|---|---|---|---|
input_string | IN | CHARACTER VARYING | String data to hash. |
checksum_string | OUT | CHARACTER VARYING | 128-bit MD5 digest of the input (stored procedure only). |
Examples
Call the MD5 functions
Use the function form to get the MD5 hash as a return value:
-- Hash a VARCHAR2 value
select dbms_obfuscation_toolkit.md5('a'::varchar2);
-- Hash a RAW value
select dbms_obfuscation_toolkit.md5('a'::raw);Call the MD5 stored procedures
Use the procedure form to receive the hash in an output parameter. The following example hashes the same input in both overloads and prints the results:
declare
input varchar2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
checksum1 raw(100);
checksum2 varchar2(100);
begin
-- RAW overload: pass input as RAW, receive checksum as RAW_CHECKSUM
dbms_obfuscation_toolkit.md5(input=>utl_raw.cast_to_raw(input), checksum=>checksum1);
-- VARCHAR2 overload: pass input as string, receive checksum as VARCHAR2
dbms_obfuscation_toolkit.md5(input_string=>input, checksum_string=>checksum2);
dbms_output.put_line(checksum1);
dbms_output.put_line(utl_raw.cast_to_raw(checksum2));
end;