APPLIES TO:
Oracle Application Object Library - Version 11.5.10.0 to 12.2.10 [Release 11.5 to 12.2]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
Note: FND_PROFILE is not public interface. Use of this API is considered a customization and should be tested in a test environment.
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', '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:
value Boolean;
Begin
value := fnd_profile.save('APPS_DATABASE_ID','<new_value>','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:
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;
Reference: https://docs.oracle.com/cd/E26401_01/doc.122/e20927.pdf
