DECLARE
v_user_name VARCHAR2(30) := UPPER ('&USER_NAME'); -- change it
v_new_password VARCHAR2(30) := '&NEW_PASSWORD'; -- change it
v_exists PLS_INTEGER;
v_status BOOLEAN;
e_user EXCEPTION;
e_pswd EXCEPTION;
BEGIN
-- Check if user exists
BEGIN
SELECT 1
INTO v_exists
FROM fnd_user u
WHERE 1=1
AND u.user_name = v_user_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE e_user;
END;
-- Validate password
IF (
-- if password is less than 8 characters
(LENGTH (v_new_password) < 8)
OR
-- if password does not contain any number
(NOT REGEXP_LIKE (v_new_password, '[[:digit:]]'))
)
THEN
RAISE e_pswd;
END IF;
-- Use API to change password
v_status := fnd_user_pkg.ChangePassword
(
username => v_user_name,
newpassword => v_new_password
);
IF v_status = TRUE THEN
DBMS_OUTPUT.PUT_LINE ('The password has been successfully reset for ' ||
v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('Unable to reset password due to ' ||
SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END IF;
EXCEPTION
WHEN e_user THEN
DBMS_OUTPUT.PUT_LINE ('User ' || v_user_name || ' could not be found');
WHEN e_pswd THEN
DBMS_OUTPUT.PUT_LINE ('The password provided could not be validated');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('SQLERRM: ' || SQLERRM);
END;
