Thursday, January 19, 2012

Data Mover SQL Error ORA-12899: Value Too Large for Column

Error:
Data MoverSQL Error. Stmt #: 0 Error Position: 851 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_GP_FORMULA_DTL".&q uot;FM_COMMENT" (actual: 255, maximum: 254)

Data Mover Release: 8.52
Database: HRMS91 (ENG)
Input file: C:\PT8.52\data\hcengs.db (ENG)
Importing GP_FORMULA_DTL
Import GP_FORMULA_DTL 0
Updating statistics for GP_FORMULA_DTL
Records remaining: 11949
Importing GP_FORMULA_DTL
File: Data MoverSQL error. Stmt #: 0 Error Position: 851 Return: 12899 - ORA-12899: value too large for column "SYSADM"."PS_GP_FORMULA_DTL".&q uot;FM_COMMENT" (actual: 255, maximum: 254)
Failed SQL stmt:INSERT INTO PS_GP_FORMULA_DTL (PIN_NUM, EFFDT, SEQ_NUM5, FRML_FUNC_OPTN, FRML_LEFT_PAREN, ENTRY_TYPE_FRML1, PIN_FRML_FLD1_NUM, FRML_FLD1_DAT_TYP, FRML_FLD1_DEC_VAL, FRML_FLD1_DATE_VAL, FRML_FLD1_CHAR_VAL, FRML_FLD1_ATRB_IND, PIN_RND1_NUM, FRML_FLD1_USE_OPTN, FRML_OPER1, FRML_FLD1_OLDV_IND, ENTRY_TYPE_FRML2, PIN_FRML_FLD2_NUM, FRML_FLD2_DAT_TYP, FRML_FLD2_DEC_VAL, FRML_FLD2_DATE_VAL, FRML_FLD2_CHAR_VAL, FRML_FLD2_ATRB_IND, PIN_RND2_NUM, FRML_FLD2_USE_OPTN, FRML_FLD2_OLDV_IND, FRML_RIGHT_PAREN, ENTRY_TYPE_FRML3, PIN_FRML_FLD3_NUM, FRML_FLD3_DAT_TYP, FRML_FLD3_ATRB_IND, PIN_RND3_NUM, FRML_FLD3_USE_OPTN, FM_COMMENT) VALUES (:1, TO_DATE(:2,'YYYY-MM-DD'), :3, :4, :5, :6, :7, :8, :9, TO_DATE(:10,'YYYY-MM-DD'), :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, TO_DATE(:21,'YYYY-MM-DD'), :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :
Error: Unable to insert row 5091
Error: SQL execute error for GP_FORMULA_DTL
Unsuccessful completion

Solution:
Changes need to done before starting of installation.
Change NLS_LENGTH_SEMANTICS to CHAR which is default as BYTE.
Below are the steps:
SQL> alter system set NLS_LENGTH_SEMANTICS=char scope=both;   
System altered.   
SQL> select value from v$parameter where upper(name)='NLS_LENGTH_SEMANTICS';   
VALUE
-------------------------------------------------------------------------------
CHAR   SQL> select value from v$nls_parameters where parameter='NLS_LENGTH_SEMANTICS';   
VALUE
----------------------------------------------------------------
BYTE   
SQL> startup force
ORACLE instance started.   
Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             381684836 bytes
Database Buffers          146800640 bytes
Redo Buffers                5828608 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where upper(name)='NLS_LENGTH_SEMANTICS';   
VALUE
-------------------------------------------------------------------------------
CHAR   
SQL> select value from v$nls_parameters where parameter='NLS_LENGTH_SEMANTICS';   
VALUE
----------------------------------------------------------------
CHAR   
SQL>

3 comments:

Jagdish said...

Above solution didn't worked for me: But what i did is:
ALTER TABLE PS_GP_FORMULA_DTL MODIFY FM_COMMENT VARCHAR2(260);

This worked for me!!!!

Pawan Mundhra said...

Thanks Jagdish for posting your solution on this.
I do agree it won't work until u drop and re-create them or alter the column. I believe you are making it to varchar2(may have limitations) but peoplesoft is making it to CLOB.

If you have many tables giving similar error while installing new PS instance then above steps more helpful instead of modifying one one tables.
And you need to drop the table and recreate them(Or run datamover script to create tables and loading data).

Small modification may help: Instead of IMPORT *;
You can use REPLACE_ALL *;

Jeff C. McAbee said...

I know your expertise on this. I must say we should have an online discussion on this. Writing only comments will close the discussion straight away! And will restrict the benefits from this information. movers west hollywood

Post a Comment