Leading/trailing spaces amd invalid characters in the relevant tables (FND_USER in your case) may cause FRM-40654.
Please do your checks accordingly.
Also, one of the common causes of FRM-40654 is a truncation problem. This is usually caused by the difference between the format of a field in the form and a column in the database.
Your date format etc.. may be causing this as well..
Read this as well -> Troubleshooting FRM-40654 (Doc ID 1003629.6)
Following is a script for fixing these kinds of issue, but you need to get Oracle Support permission's to run this.!!!!Take a backup of the relevant table before executing this.
""If you are planning to run this script, the risk is yours"""
*********************************************************************************************************************************************
REM +=======================================================================+
REM | Copyright (c) 1992 Oracle Corporation, Redwood Shores, CA, USA |
REM | All rights reserved. |
REM +=======================================================================+
REM | FILENAME
REM | afshrchk.sql
REM | USAGE
REM | sqlplus applsys/pwd @afchrchk
<column>
REM | where and <column> are optional 'like'-style clauses
REM | DESCRIPTION
REM | Check requested table.columns for invalid trailing spaces and
REM | and control characters.
REM | These characters cause 'FRM-40654: Record has been updated.'
REM | errors when column is queried in a form.
REM +=======================================================================+
REM $Header: afchrchk.sql 120.3.12010000.1 2009/03/16 10:17:54 absandhw noship $
REM dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
set serveroutput on
exec dbms_output.enable(1000000)
set verify off
prompt *** INSTRUCTIONS ***
prompt Enter the table and column names to check for leading or trailing
prompt spaces and control characters. Like-style expressions are ok.
prompt Leave either table or column blank to check table/column.
prompt
prompt Table name (blank for all):
define tblarg = '&1'
prompt Column name (blank for all):
define colarg = '&2'
prompt
prompt Newline characters are acceptable in columns never queried in
prompt Forms, on only queried in multi-line items.
prompt Enter Y to also look for newline characters.
prompt Check for newline characters (Y/N)?
define newlinearg = '&3'
prompt
prompt Enter Y to automatically strip all leading/trailing spaces
prompt and control characters found.
prompt *** WARNING ***
prompt It is highly recommended to run first without stripping
prompt to be sure all detected values really should be fixed!
prompt
prompt Automatically fix all errors found (Y/N)?
define fixarg = '&4'
declare
badchars varchar2(80);
-- Selecvt all columns to check
cursor colcurs is
select C.TABLE_NAME, C.COLUMN_NAME
from SYS.ALL_TAB_COLUMNS C, SYS.ALL_TABLES T
where T.TABLE_NAME = C.TABLE_NAME
and T.OWNER = C.OWNER
and C.DATA_TYPE = 'VARCHAR2'
and C.TABLE_NAME like nvl(upper('&tblarg'), '%')
and C.COLUMN_NAME like nvl(upper('&colarg'), '%')
order by 1, 2;
curs integer; -- Cursor
sqlbuf varchar2(2000); -- Sql stmt
ignore integer; -- Retcode
value varchar2(2000); -- Value with bad chars selected from table
newlineflag boolean; -- Check for newlines
fixflag boolean; -- Strip trailing spaces
badvalfound boolean; -- Bad value detected for this column
lang varchar2(255);
begin
-- Initialize badchars to non-printable chars (except newline)
-- Instead of using fnd_global.local_chr at all the following places, the
-- logic invloved in fnd_global.local_chr is added after building the
-- complete string of badchars. To exempt GSCC check 'File.Sql.10' on this
-- file, a bug # 4383895 is raised and got approved.
badchars :=
chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||
chr(9)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||
chr(16)||chr(17)||chr(18)||chr(19)||chr(20)||chr(21)||chr(22)||
chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||
chr(30)||chr(31)||chr(127);
lang := userenv('LANGUAGE');
badchars := convert(badchars,
substr(lang, instr(lang,'.') + 1), 'US7ASCII');
newlineflag := (upper('&newlinearg') = 'Y');
fixflag := (upper('&fixarg') = 'Y');
if (newlineflag) then
-- Add in newline
badchars := badchars||chr(10);
end if;
-- Convert to local codeset
badchars := convert(badchars,
substr(userenv('LANGUAGE'), instr(userenv('LANGUAGE'),'.') +1),
'US7ASCII');
for col in colcurs loop
begin
dbms_output.put_line('Checking '||col.table_name||'.'||col.column_name);
badvalfound := FALSE;
-- Open cursor to check single table.column
curs := dbms_sql.open_cursor;
sqlbuf := 'SELECT '||col.column_name||' value '||
' FROM '||col.table_name||
' WHERE '||col.column_name||' != LTRIM(RTRIM(TRANSLATE('||
col.column_name||', '''||badchars||''', '' '')))';
dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
dbms_sql.define_column(curs, 1, value, 2000);
ignore := dbms_sql.execute(curs);
-- Select all values with bad chars
loop
if (dbms_sql.fetch_rows(curs) = 0) then
exit;
end if;
badvalfound := TRUE;
dbms_sql.column_value(curs, 1, value);
-- Print value selected
if (length(value) < 250) then
dbms_output.put_line('### '''||value||'''');
else
dbms_output.put_line('### (TRUNCATED) '''||
substr(value, 1, 235)||'''');
end if;
end loop;
dbms_sql.close_cursor(curs);
if (badvalfound and fixflag) then
-- Strip spaces and control characters
dbms_output.put_line('Fixing '||col.table_name||'.'||col.column_name);
curs := dbms_sql.open_cursor;
sqlbuf := 'UPDATE '||col.table_name||' SET '||
col.column_name||' = LTRIM(RTRIM(TRANSLATE('||
col.column_name||', '''||badchars||''', '||
'rpad('' '', length('''||badchars||''')))))';
dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
ignore := dbms_sql.execute(curs);
dbms_sql.close_cursor(curs);
end if;
exception
when others then
dbms_output.put_line('***'||sqlerrm); -- Continue if errors
end;
end loop;
exception
when others then
if (dbms_sql.is_open(curs)) then
dbms_sql.close_cursor(curs);
end if;
raise;
end;
/
|
|
Thanks for pointing us to right direction
|
Locked
