How to Change Profile Option Value Without Forms?

How to Change Profile Option Value Without Forms?

Applies to:

Oracle Application Object Library - Version: 11.5.10.0 to 11.5.10.2 - Release: 11.5 to 11.5
Information in this document applies to any platform.
Goal

How to update a Profile Option using SQL (if Forms login is not possible)?

Solution

Please use the API:  FND_PROFILE
The package FND_PROFILE  can be found in file AFPFPROS.pls


FND_PROFILE.SAVE - sets the value of a profile option permanently to the database,
at any level. This routine can be used at runtime or during patching.
This routine will not actually commit the changes; the caller must commit.
The levels are: 'SITE', 'APPL', 'RESP', or 'USER'.

Examples of use:
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);

returns: TRUE if successful, FALSE if failure.


To update a Profile Option value at Site level, you need to run the SQL Script below:
Declare
value Boolean;
Begin
value := fnd_profile.save('APPS_DATABASE_ID','','SITE');
End;


Example:
===========
Sample Code
===========
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', '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;
===============
End of Sample Code
===============


Description of the FND_PROFILE.SAVE function parameters:
Function SAVE(
X_NAME in varchar2,             /* Profile name you are setting */
X_VALUE in varchar2,            /* Profile value you are setting */
X_LEVEL_NAME in varchar2,       /* Level that you're setting at:
                                   'SITE','APPL','RESP','USER', etc. */
X_LEVEL_VALUE in varchar2 default NULL,
                                /* Level value that you are setting at,
                                   e.g. user id for 'USER' level.
                                   X_LEVEL_VALUE is not used at site level. */
X_LEVEL_VALUE_APP_ID in varchar2 default NULL,
                                /* Used for 'RESP' and 'SERVRESP' level;
                                   Resp Application_Id. */
X_LEVEL_VALUE2 in varchar2 default NULL
                                /* 2nd Level value that you are setting at.
                                   This is for the 'SERVRESP' hierarchy. */
) return boolean;