Re: ORA - 1401: inserted value too large for columm

evoradba_at_yahoo.ca (Maria) wrote in message news:<351fd9d6.0307141229.4615259c_at_posting.google.com>...
> Hi all
>
> I have an insert that tries to insert a 347 character string
> (attached) into a VARCHAR2(1024) file but fails with -
>
> ERROR at line 1:
> ORA-01401: inserted value too large for column,
>
>
> Is there a limitation which is causing this? This is oracle 8170 on
> solaris?This is the job
>
> "insert into Test
> (ACCOUNT_FIRST_NAME
> ,ACCOUNT_ID
> ,ACCOUNT_LAST_NAME
> ,ACCOUNT_RR_CODE
> ,ACCOUNT_TYPE
> ,APPROVAL_RR_CODE
> ,CHANNEL_ID
> ,CLIENT_BIRTH_DATE
> ,CLIENT_PX
> ,CLIENT_SHORT_NAME
> ,COMM_RATE
> ,COMM_TYPE
> ,COST_TO_CLIENT
> ,CREATE_DATE
> ,CREATE_TIME
> ,ENTRY_USER_ID
> ,EWC00
> ,EWC01
> ,EWC02
> ,EWC03
> ,EWC04
> ,FX_RATE
> ,INSTRUMENT_TRANSFER_PX
> ,LANGUAGE
> ,MAX_COMMISSION
> ,MID_TIER_EDIT_TEXT
> ,MIN_COMMISSION
> ,NET_TRADE_AMOUNT
> ,NOTE
> ,ORDER_GROSS_AMOUNT
> ,ORDER_ID
> ,ORDER_MATURITY
> ,ORDER_QTY
> ,ORDER_SIN
> ,ORDER_STATUS
> ,ORDER_TERM
> ,ORDER_TERM_UNIT
> ,ORDER_TI
> ,ORDER_TRADE_DATE
> ,ORDER_TYPE,PACKAGE_ID,PACKAGE_TYPE,PARENT_ORDER_ID,PHONE_NUMBER,POOL_FACTOR,POSITION_QTY,PRICE_RANGE_2_MAX,PRICE_RANGE_2_MIN,REGION,REGISTRATION_FLAG,REPRESENTATIVE,SETTLEMENT_CURRENCY,SETTLEMENT_DATE,SETTLEMENT_OPTIONS,SIDE,SOLICITED,SUGGESTED_COMM_RATE,TOTAL_ACCRUED_INTEREST,TOTAL_CASH,TOTAL_COMMISSION,TRAILER00,TRAILER01,TRAILER02,TRAILER03,TRAILER04,TRAILER05,TRAILER06,TRAILER07,TRAILER08,T
A
> LER09,TRANSFER_COST,TRANSFER_PX,TRANSFER_YIELD,UPDATE_DATE,UPDATE_TIME,UPDATE_USER_ID,YIELD1,YIELD2,YIELD3,YIELD_TO_CLIENT,YI
> ELD_TO_CLIENT_TYPE,YIELD_TYPE1,YIELD_TYPE2,YIELD_TYPE3)
> values
> ('LEONARD'
> ,'5700146912'
> ,'MONKMAN'
> ,'Y02'
> ,'1'
> ,''
> ,7
> ,'19290303'
> ,92.04686391
> ,'MONKMAN L & E'
> ,0.16000000
> ,'P'
> ,138070.30
> ,20030714
> ,140137
> ,'EXT-GORDON'
> ,-1
> ,-1
> ,-1
> ,-1
> ,-1
> ,1.0000000
> ,91.88686391
> ,'e'
> ,10.00
> ,'STPBN0006 Sorry there was an Internal System Problem. Investor Edge
> clients call 1-800-567-3343. Imperial Investor Service clients call
> 1-800-661-7494SFCBN0006 The amount of the transaction currently
> exceeds the funds available in the account. Please ensure that the
> transaction is funded by the settlement date of the trade.'
> ,0.00
> ,0.00
> ,''
> ,138070.30
> ,498080
> ,20051219
> ,150000.0000
> ,0
> ,'WFA'
> ,-1
> ,''
> ,104878,20030714
> ,''
> ,0
> ,''
> ,0
> ,'9054791810'
> ,-1.00000000
> ,0.0000
> ,-1.00000000
> ,-1.00000000
> ,'ON'
> ,'N'
> ,'Y02'
> ,'CAD'
> ,20030717
> ,'bnkuf'
> ,'B'
> ,'N'
> ,0.16000000
> ,0.0000
> ,552.50
> ,240.00
> ,100
> ,300
> ,-1
> ,-1
> ,-1
> ,-1
> ,-1
> ,-1
> ,-1
> ,-1
> ,137830.30
> ,91.88686391
> ,3.52197395
> ,20030714
> ,140137
> ,'EXT-GORDON'
> ,3.4489
> ,3.4787
> ,0.0000
> ,3.4489
> ,2
> ,2
> ,1
> ,-1
> )"
>
>
> Thank you Maria

Maria, the problem could be any character or numeric value where the input data has one or more characters/digits than the corresponding table definition. You should not assume it is the 347 character field unless you have other information not provided to us. Here is one way to narrow down the error. After comparing the table column definitions to the value clause data and not finding the problem then try modifying the values clause data, several fields at a time, to be 1 character shorter then re-try the insert. When it works you know the problem is one of the fields just changed.

Also is "104878,20030714" two columns listed on one line or should the comma be a decimal point. I did not bother to compare your fields verse column value count, but making a mistake in both can get you a value error rather than a syntax error.

HTH -- Mark D Powell -- Received on Mon Jul 14 2003 - 21:44:35 CDT