ORA-01401 inserted value too large for column tips Oracle Error Tips by Burleson Consulting

 

Oracle docs note this about ORA-01401:

 

Oracle MOSC offers information regarding ORA-01401, as it pertains to bugs.  Bugs 3420679 and 5117292 in Oracle Self-Service Web Applications in version 11.5.9 are known to throw ORA-01401 and ORA-06512. 

 

While attempting to open the IE, CRM, BSC, Advances pricing screens, while using OA.JSP base with an OA framework, you will see the following error containing ORA-01401:

oracle.apps.fnd.framework.OAException: java.sql.SQLException: ORA-1401:
inserted value too large for column
ORA-6512: at "APPS.ICX_SEC", line 2985

This error message signifying ORA-01401 occurs because of the SQL:

INSERT into ICX_SESSION_ATTRIBUTES
(SESSION_ID,NAME,VALUE)
values
(:b3,:b2,:b1)
SQL> desc ICX_SESSION_ATTRIBUTES
Name Null? Type
----------------------------------------- --------
----------------------------
SESSION_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
VALUE VARCHAR2(4000)
If icx_transactis_s value less than 9999999, there is no problem.
If the icx_transactis_s value exceeds 10000000, ORA-1401 is reported.

Solving this problem with ORA-01401 consists of implementing a few patches:

1. Apply Patch 3420679 and Patch 3422514.

3420679: Fixes sequence icx_transactions_s. The cycle flag will now be set. After reaching the max sequence it will now rollover.

3422514: Fixes logic to insert into ICX_SESSION_ATTRIBUTE.NAME in case icx_transactions_s over 9999999.

2. Apply patch 5117292. This alternative way for customer who cannot apply OA Framework 5.7H as pre-req patch of Patch 3422514.

Before applying this patch please check the value of ICX_TRANSACTIONS_S, if it is greater than 9999999 it needs to be reset to 1 before patch application or you will get AD Worker error on the included file icxwseq.odf.

3. Alternate solution (Preferred solution): Issue is most safely fixed by the FND patchset 11i.FND.H (3262159).

---

In Oracle Forums, a user encounters ORA-01401 with 9i.

 

Question:

I have a table with 118 columns and keep receiving ORA-01401 when I attempt to put the data in a table with a cursor.  Is there any way I can figure out which column is throwing the ORA-01401 error?

Answer:
You should note that in 10g, the error has been updated and includes further information, making it easier to resolve ORA-01401.  Otherwise, you call always test your data using PL/SQL.  Or, you can always try making an exception with PRAGMA EXCEPTION_INIT :

DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception, -1401);
BEGIN
insert into table () values ..............
EXCEPTION
WHEN my_exception THEN dbms_output.put_line('Column value too long');
END;