Script to change FND_USER password

This is a script to change an Oracle Enterprise Business Suite user’s password. Many thanks to the author of of the myoracleapps blog. Source is in the article Reset FND User Password.


To run this script you need to be connected via sqlplus as the APPS schema user. Execute with the normal @scriptname.sql syntax. The script will prompt for the user name and desired password (in clear text).

/* This is sample script to re-set an FND User password from database.
Need to connect as an APPS schema user. */
set serverout on;
set verify off;
declare
a varchar2(100):='&UserName';
b varchar2(100):='&password';
BEGIN
if a is not null and b is not null then
IF FND_USER_PKG.ispasswordchangeable(a) THEN
DBMS_OUTPUT.put_line('Setting user '||a||' with password '||b);
IF FND_USER_PKG.changePassword(a,b) THEN
DBMS_OUTPUT.put_line('Sucess');
ELSE
DBMS_OUTPUT.put_line('Failed');
END IF;
ELSE
DBMS_OUTPUT.put_line('The user password cannot be re-set as its unchangeable');
END IF;
END IF;
END;
/

Here is a slightly different take on the same operation from Dibyajyoti Koch (in an article entitled API’s to Create User,Reset Password and Add Responsibility).

01 declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
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 reset successfully for the User:'||v_user_name);
commit;
else
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
rollback;
END if;
end;