How to Change Profile Option Value Without Forms? (Doc ID 943710.1)

In this Document

Goal

Solution

---

APPLIES TO:

Oracle Application Object Library - Version 11.5.10.0 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 12-April-2013***

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', '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','<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:

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;

Reference: https://docs.oracle.com/cd/E26401_01/doc.122/e20927.pdf