UTL_FILE_DIR issue after applying patch 5985992 'TXK AUTOCONFIG RUP Q'

Do you have more than 240 characters in your utl_file_dir? if so, you should read this before applying patch 5985992 'TXK AUTOCONFIG RUP Q (Jul/Aug 2007)'. After applying this patch, AutoConfig on database tier failed with the following error: [PROFILE PHASE]   AutoConfig could not successfully execute the following scripts:     Directory: [RDBMS_ORACLE_HOME]/appsutil/install/[context_name]        afdbprf.sh              INSTE8_PRF         1 AutoConfig is exiting with status 1 It wasn't clear why afdbprf.sh failed so I ran it manually from ssh terminal and I got this: $ [RDBMS_ORACLE_HOME]/appsutil/install/[context_name]/afdbprf.sh afdbprf.sh started at Tue Jan 29 17:43:21 IST 2008 The environment settings are as follows ...        ORACLE_HOME : ....        .........        ......... Application Id : 0 Profile Name : BIS_DEBUG_LOG_DIRECTORY Level Id : 10001 New Value : /usr/tmp Old Value : /usr/tmp declare * ERROR at line 1: ORA-12899: value too large for column "APPLSYS"."FND_PROFILE_OPTION_VALUES"."PROFILE_OPTION_VALUE" (actual: 486, maximum: 240) ORA-06512: at line 44 ORA-06512: at line 139 Looking into afdbprf.sql  (executed by afdbprf.sh) reveals the problem: ....... --Setting  BIS_DEBUG_LOG_DIRECTORY            set_profile(0, 'BIS_DEBUG_LOG_DIRECTORY',                        10001, 0,                        '&BIS_DEBUG_LOG_DIR',                        NULL); -- This profile option was earlier set in addbprf.sql via bug 2843457, Now moved here for bug 5722442 -- -- Set up UTL_FILE_LOG profile option --           set_profile(1, 'UTL_FILE_LOG',                       10001, 0, '/usr/tmp,........[more than 240 characters..... :-) ]',                       NULL); In order to fix bug 5722442, the UTL_FILE_LOG updated with the value of s_db_util_filedir from the database context file and a new bug was created (not a bug according to Bug 6404909). If the s_db_util_filedir contains more than 240 characters it can't be stored in a profile since the PROFILE_OPTION_VALUE column defined as varchar2(240). The s_db_util_filedir initialized when creating the database context file by adbldxml.pl, and set up with the value of utl_file_dir database parameter. I'm not sure why it should be updated with all directories within utl_file_dir and not with the relevant directories only...?!  The UTL_FILE_LOG profile (or "Stored procedure log directory") wasn't updated with AutoConfig before applying this patch, so I'm not sure regarding the purpose of this profile. Before applying this patch I have this profile set to a directory that doesn't exist... The solution according to Note:458511.1 - "After patch 5985992 AutoConfig On Database Tier Fails with script afdbprf.sh" is to change the value of s_db_util_filedir in the database context file to a value less the 240 characters and run AutoConfig again. And it works, of course... What should I do if I my utl_file_dir contains more than 240 characters?! Actually I don't have a good answer to this question but I will try to locate all the places on which this issue might affect when s_db_util_filedir will be updated with a value less than 240 chars. AutoConfig on the DB Tier creates the [SID]_APPS_BASE.ora file under $RDBMS_ORACLE_HOME/dbs (if it's not already exist), which contains the utl_file_dir database parameter generated respectively to the s_db_util_filedir from the database context file. So if you plan to rebuild your init.ora with AutoConfig you will need to update the utl_file_dir manually. When executing adbldxml.pl on DB Tier to rebuild the database context file, the s_db_util_filedir will be filled with the utl_file_dir database parameter - that might contain more than 240 chars. Therefore, before executing AutoConfig you should edit the new context file and shorten the value of s_db_util_filedir. The UTL_FILE_LOG profile ("Stored procedure log directory")  will be updated with s_db_util_filedir shortened value. You are welcome to leave a comment or update with additional information regarding this issue. Aviad