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:
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:
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!!!!
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 *;
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