Query to change Oracle Applications password for a user

In case you ever need to change password for your Oracle Applications, this following query uses fnd_user_pkg.ChangePassword to let you change your password from the back-end.

-------------------------------------------------------------------------------

-- Query to change Oracle Applications password for a user

-------------------------------------------------------------------------------

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;