Password reset procedure oracle

Deleting a password from history table in oracle and resetting the password

CREATE OR REPLACE PROCEDURE password_reset( p_user_name IN VARCHAR2,
                                       p_password  IN VARCHAR2) is
  ln_user_id   NUMBER;
  lc_alter     VARCHAR2(100);
BEGIN
   ln_user_id := NULL;
   lc_alter   := NULL;
   SELECT user_id
     INTO ln_user_id
  FROM dba_users
 WHERE username = p_user_name;
   dbms_output.put_line('User id is :'||ln_user_id);
 
   DELETE FROM  user_history$
    WHERE user#=ln_user_id;
   dbms_output.put_line(SQL%ROWCOUNT||' rows deleted for the User id :'||ln_user_id);
   COMMIT;
 
   lc_alter := 'alter user '||p_user_name||' identified by '||p_password||' account unlock';
   EXECUTE IMMEDIATE lc_alter;
 
EXCEPTION
WHEN OTHERS
THEN
   dbms_output.put_line('Exception Message is :'||SQLERRM);
END;


grant execute on sys.password_reset to user;



Execute it using the following

DECLARE
  P_USER_NAME VARCHAR2(32767);
  P_PASSWORD VARCHAR2(32767);

BEGIN
  P_USER_NAME := 'xxx';
  P_PASSWORD := 'Passwrd#1';

  SYS.PASSWORD_RESET ( P_USER_NAME, P_PASSWORD );
  COMMIT;
END;