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;