Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Unanswered: ORA-01401: inserted value too large for column

    I've got an insert of record into a table (Table exists on database A and database B as same version oracle and same NLS character set and exact same table definition on both databases

    Could any explain as to why the insert completes on database a but I get the following error on database B ?

    SQL> @do
    'AMQ PIUSALL G!Cb',
    *
    ERROR at line 7:
    ORA-01401: inserted value too large for column


    Here is the insert statement

    Code:
    INSERT INTO ALLOC_UPDATES ( ID, UPDATEDATA, STATUS, MSGID,
    ENTRYDATETIME ) VALUES (
    12,
    '<?xml version=''1.0'' encoding=''UTF-8'' ?><MasterFilesMessages><MasterFilesMessage><AccountRecord><AccountDetails><Action>DEL</Action><Consol>10002</Consol><ConsolParent>620811</ConsolParent><ParentCode>TEST1</ParentCode><CustomerNumber>TEST1</CustomerNumber><AlertAccess>Added Alert</AlertAccess><ADPAccount></ADPAccount><Active>1</Active><AbbreviatedName>Test Account2</AbbreviatedName><SubAccountName>SUB</SubAccountName><RegRep>000</RegRep><DTCId>-1</DTCId><ISMAccount>0011234</ISMAccount><BOLSAAccount></BOLSAAccount><ConvertableBondIndicator>0</ConvertableBondIndicator></AccountDetails></AccountRecord></MasterFilesMessage></MasterFilesMessages>'
    ,
    0,
    'AMQ PIUSALL     G!Cb',
    TO_Date( '12/06/2007 10:49:54 PM', 'MM/DD/YYYY HH:MI:SS AM'));
    
    SQL> desc ALLOC_UPDATES;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER(9)
     UPDATEDATA                                NOT NULL VARCHAR2(4000)
     STATUS                                    NOT NULL NUMBER(9)
     MSGID                                     NOT NULL CHAR(24)
     ENTRYDATETIME                             NOT NULL DATE

  2. #2
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    resend

    sorry as the backslash get interpreted

    here is the real insert statement with backslash

    the err is on this line (7) which is char(24)

    'AMQ PIUSALL G^P\305\332!Cb^A',

    Code:
    INSERT INTO ALLOC_UPDATES ( ID, UPDATEDATA, STATUS, MSGID,
    ENTRYDATETIME ) VALUES (
    12,
    '<?xml version=''1.0'' encoding=''UTF-8'' ?><MasterFilesMessages><MasterFilesMessage><AccountRecord><AccountDetails><Action>DEL</Actio
    n><Consol>10002</Consol><ConsolParent>620811</ConsolParent><ParentCode>TEST1</ParentCode><CustomerNumber>TEST1</CustomerNumber><AlertA
    ccess>Added Alert</AlertAccess><ADPAccount></ADPAccount><Active>1</Active><AbbreviatedName>Test Account2</AbbreviatedName><SubAccountN
    ame>SUB</SubAccountName><RegRep>000</RegRep><DTCId>-1</DTCId><ISMAccount>0011234</ISMAccount><BOLSAAccount></BOLSAAccount><Convertable
    BondIndicator>0</ConvertableBondIndicator></AccountDetails></AccountRecord></MasterFilesMessage></MasterFilesMessages>'
    ,
    0,
    'AMQ PIUSALL     G^P\305\332!Cb^A',
    TO_Date( '12/06/2007 10:49:54 PM', 'MM/DD/YYYY HH:MI:SS AM'));

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what do you get from both databases from the following query?
    select name, value from v$parameter where name like '%nls%';
    Last edited by anacedent; 12-13-07 at 13:22.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    fixed

    Thanks Anacedent

    I had originally posted that the NLS settings were the same on both databases. I was wrong -- One was UTF and the other was WE8ISO8859P1

    That was the issue

    Regards

    Brendan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •