PLSQL - Procedure for Password Encryption

Question

I searched on the internet a precedent for encrypt the users password, however I only found functions.

There is how to generate a Procedure to carry out this process?

CREATE OR REPLACE FUNCTION MD5(VALOR VARCHAR) 
    RETURN VARCHAR2 
IS 
    V_INPUT VARCHAR2(2000) := VALOR; 
    HEXKEY  VARCHAR2(32)   := NULL; 
BEGIN 
    HEXKEY := RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => UTL_RAW.CAST_TO_RAW(V_INPUT))); 
    RETURN NVL(HEXKEY, ''); 
END;

Answer 1

You can change the function and turn it into a Procedure, but I recommend you use the DBMS_CRYPTO package, it replaces the DBMS_OBFUSCATION_TOOLKIT and provides a more complete list of encryption algorithms.

One point of attention is that the DBMS_CRYPTO package does not work with VARCHAR2 directly, but it is possible to convert a VARCHAR2 to RAW using utl_i18n.string_to_raw.

I set the following example, I hope it meets your need.

CREATE OR REPLACE PROCEDURE md5 (
   p_txt    IN VARCHAR2,
   p_hash   OUT VARCHAR2
) AS
   v_hash_raw   RAW(32);
BEGIN
   --Calcular o hash usando o algoritmo MD5
   v_hash_raw := dbms_crypto.hash(src => utl_i18n.string_to_raw(p_txt,'AL32UTF8'),
                                  typ => dbms_crypto.hash_md5);

   --Converter RAW para exadecimal
   p_hash := rawtohex(v_hash_raw);
END md5;