Backend Profile Option Update

Here's a little bit pulled down from the net on how to update profile options via TOAD or SQL developer or heck anything for that matter i.e. java etc.

I locked myself out of Oracle by choosing some debug option in MO and used this stuff to get me back in.

The table fnd_profile_options_tl, profile options names are kept.  Now find the corresponding the Profile option name for which you need to update from backend. For this example I took my favorite “ORG_ID”

SELECT profile_option_name
  FROM fnd_profile_options_tl
 WHERE user_profile_option_name LIKE 'MO%'

It returns more than one row but i can make out that "ORG_ID" is the PROFILE_OPTION_NAME for MO: Operating Unit. Now I need to know the Org_ID of the Org whose value is to be set in MO: Operating Unit. SO I use the simple select as below

SELECT organization_id, NAME
  FROM hr_all_organization_units;

From the organization name I find the one which will be the default Operating Unit, and I note the ID. In my case the ID for my default Operating Unit is 286. Now with the code below I set the profile option value using fnd_profile.save.

DECLARE
   stat   BOOLEAN;
BEGIN
   DBMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.ENABLE (100000);
   stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');
   IF stat
   THEN
      DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
   ELSE
      DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
   END IF;
   COMMIT;
END;