Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    227

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

    hi,
    When I try to insert on my table I get always this error

    Here there is my script:
    SQL> INSERT INTO wr

    2 (prob_type, requestor, wo_id, date_requested, time_requested,status, pms_id,

    3 pmp_id, bl_id, fl_id, rm_id, tr_id, site_id, date_assigned, eq_id, description )
    4 VALUES ('MANUT PREVENTIVA',NULL,27,
    5 TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 00:00:00','YYYY-MM-DD HH24:MIS'),
    6 TO_DATE('1899-12-30 17:16:24','YYYY-MM-DD HH24:MIS'),'I',3114,
    7 'ADE SETTIMANALE','H-AC','P14','L003','ACEL','OM',
    8 TO_DATE('2003-03-20 00:00:00','YYYY-MM-DD HH24:MIS'),
    9 'ADEH-ACP14L003',
    10 '- Controllo pressione alimentazione
    11 - Controllo assenza perdite
    12 - Rabbocco delle resine scambiatrici
    13 - Controllo del funzionamento idroelettrico di tutti gli impianti
    14 - Controllo dei cicli di rigenerazione degli addolcitori
    15 - Controllo del ciclo di lavaggio filtri
    16 - Controllo livello del sale ed eventuale reintegro (compresa fornitura)
    17 Se necessario:
    18 - Pulizia filtri (compresa fornitura) - Rigenerazioni delle bombole (compresa fornitura)
    19 - Analisi dell*acqua in uscita dopo la rigenerazione manuale e rilievo dei seguenti valori:
    20 durezza, presenza di salamoia');

    INSERT INTO wr

    *

    ERROR at line 1:

    ORA-01401: inserted value too large for column

    I think that error is on col description but is VARCHAR2(2000)

    How can I resolve this problem??

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    Are you sure it is that column? Try just changing that column value to 'X' and leave everything else as it is - if the error stops, it was that column. If you still get the error - it was not.

    If it is that column, then I suspect there must be a lot of trailing spaces on each line so that you are actually inserting more like:

    10 '- Controllo pressione alimentazione###############
    11 - Controllo assenza perdite#####################
    12 - Rabbocco delle resine scambiatrici################
    ...

    where # represents a space.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    here is the desc of table:

    AC_ID VARCHAR2(32)
    ACT_LABOR_HOURS NOT NULL NUMBER(8,2)
    BL_ID VARCHAR2(8)
    CAUSE_TYPE VARCHAR2(16)
    CF_NOTES VARCHAR2(2000)
    COMPLETED_BY VARCHAR2(35)
    COST_EST_LABOR NOT NULL NUMBER(10,2)
    COST_EST_OTHER NOT NULL NUMBER(10,2)
    COST_EST_PARTS NOT NULL NUMBER(10,2)
    COST_EST_TOOLS NOT NULL NUMBER(10,2)
    COST_EST_TOTAL NOT NULL NUMBER(10,2)
    COST_LABOR NOT NULL NUMBER(10,2)
    COST_OTHER NOT NULL NUMBER(10,2)
    COST_PARTS NOT NULL NUMBER(10,2)
    COST_TOOLS NOT NULL NUMBER(10,2)
    COST_TOTAL NOT NULL NUMBER(10,2)
    CURR_METER_VAL NUMBER(16,2)
    DATE_ASSIGNED DATE
    DATE_COMPLETED DATE
    DATE_EST_COMPLETION DATE
    DATE_REQUESTED DATE
    DATE_STAT_CHG DATE
    DESC_OTHER_COSTS VARCHAR2(2000)
    DESCRIPTION VARCHAR2(2000)
    DOWN_TIME NUMBER(8,2)
    DP_ID VARCHAR2(16)
    DV_ID VARCHAR2(16)
    EQ_ID VARCHAR2(20)
    EST_LABOR_HOURS NOT NULL NUMBER(8,2)
    FL_ID VARCHAR2(4)
    LOCATION VARCHAR2(50)
    MSG_DELIVERY_STATUS VARCHAR2(8)
    OPTION1 VARCHAR2(50)
    OPTION2 VARCHAR2(16)
    PHONE VARCHAR2(20)
    PMP_ID VARCHAR2(16)
    PMS_ID NUMBER(38)
    PRIORITY NOT NULL NUMBER(38)
    PROB_TYPE VARCHAR2(16)
    REPAIR_TYPE VARCHAR2(16)
    REQUESTOR VARCHAR2(35)
    RM_ID VARCHAR2(8)
    SATISFACTION NUMBER(38)
    SATISFACTION_NOTES VARCHAR2(500)
    STATUS NOT NULL VARCHAR2(20)
    TIME_ASSIGNED DATE
    TIME_COMPLETED DATE
    TIME_REQUESTED DATE
    TIME_STAT_CHG DATE
    TR_ID VARCHAR2(16)
    WO_ID NUMBER(38)
    WR_ID NOT NULL NUMBER(38)
    CF_ID VARCHAR2(35)
    COMPLETED_BY_CF VARCHAR2(35)
    PROB_TYPE_1 VARCHAR2(16)
    WORKDESC VARCHAR2(2000)
    NOTE VARCHAR2(2000)
    ORA_INVIO DATE
    DATE_2 DATE
    DATE_3 DATE
    TIME_FINE_1 DATE
    TIME_FINE_2 DATE
    TIME_FINE_3 DATE
    TIME_INIZIO_1 DATE
    TIME_INIZIO_2 DATE
    TIME_INIZIO_3 DATE
    DATA_CLO DATE
    TIME_CLO DATE
    NAME VARCHAR2(32)
    SITE_ID VARCHAR2(16)

    The version Oracle client is 8.0.5
    The vesrion Oracle server is 8.1.7

    Raf

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So what happened when you tried my suggestions above?

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    SAME ERROR

    Raf

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by raf
    SAME ERROR

    Raf
    Please clarify - do you mean that when you change the description value to 'X' you get the same error?

    In that case, the problem is with a different column. So repeat the exercise, one column at a time, until it works. Then you have found the column. It's a pain, but since Oracle does not tell you which column is at fault, and since it's not obvious by eyeballing the statement what's wrong with it, there's little else you can do.

    As an aside, did you realise that this:

    TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 00:00:00','YYYY-MM-DD HH24:MIS')

    is just a very long-winded way of saying this:

    SYSDATE

    They are IDENTICAL.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, I created your table:

    create tabl wr
    (AC_ID VARCHAR2(32)
    ,ACT_LABOR_HOURS NUMBER(8,2)
    ,BL_ID VARCHAR2(8)
    ,CAUSE_TYPE VARCHAR2(16)
    ,CF_NOTES VARCHAR2(2000)
    ,COMPLETED_BY VARCHAR2(35)
    ,COST_EST_LABOR NUMBER(10,2)
    ,COST_EST_OTHER NUMBER(10,2)
    ,COST_EST_PARTS NUMBER(10,2)
    ,COST_EST_TOOLS NUMBER(10,2)
    ,COST_EST_TOTAL NUMBER(10,2)
    ,COST_LABOR NUMBER(10,2)
    ,COST_OTHER NUMBER(10,2)
    ,COST_PARTS NUMBER(10,2)
    ,COST_TOOLS NUMBER(10,2)
    ,COST_TOTAL NUMBER(10,2)
    ,CURR_METER_VAL NUMBER(16,2)
    ,DATE_ASSIGNED DATE
    ,DATE_COMPLETED DATE
    ,DATE_EST_COMPLETION DATE
    ,DATE_REQUESTED DATE
    ,DATE_STAT_CHG DATE
    ,DESC_OTHER_COSTS VARCHAR2(2000)
    ,DESCRIPTION VARCHAR2(2000)
    ,DOWN_TIME NUMBER(8,2)
    ,DP_ID VARCHAR2(16)
    ,DV_ID VARCHAR2(16)
    ,EQ_ID VARCHAR2(20)
    ,EST_LABOR_HOURS NUMBER(8,2)
    ,FL_ID VARCHAR2(4)
    ,LOCATION VARCHAR2(50)
    ,MSG_DELIVERY_STATUS VARCHAR2(8)
    ,OPTION1 VARCHAR2(50)
    ,OPTION2 VARCHAR2(16)
    ,PHONE VARCHAR2(20)
    ,PMP_ID VARCHAR2(16)
    ,PMS_ID NUMBER(38)
    ,PRIORITY NUMBER(38)
    ,PROB_TYPE VARCHAR2(16)
    ,REPAIR_TYPE VARCHAR2(16)
    ,REQUESTOR VARCHAR2(35)
    ,RM_ID VARCHAR2(8)
    ,SATISFACTION NUMBER(38)
    ,SATISFACTION_NOTES VARCHAR2(500)
    ,STATUS VARCHAR2(20)
    ,TIME_ASSIGNED DATE
    ,TIME_COMPLETED DATE
    ,TIME_REQUESTED DATE
    ,TIME_STAT_CHG DATE
    ,TR_ID VARCHAR2(16)
    ,WO_ID NUMBER(38)
    ,WR_ID NUMBER(38)
    ,CF_ID VARCHAR2(35)
    ,COMPLETED_BY_CF VARCHAR2(35)
    ,PROB_TYPE_1 VARCHAR2(16)
    ,WORKDESC VARCHAR2(2000)
    ,NOTE VARCHAR2(2000)
    ,ORA_INVIO DATE
    ,DATE_2 DATE
    ,DATE_3 DATE
    ,TIME_FINE_1 DATE
    ,TIME_FINE_2 DATE
    ,TIME_FINE_3 DATE
    ,TIME_INIZIO_1 DATE
    ,TIME_INIZIO_2 DATE
    ,TIME_INIZIO_3 DATE
    ,DATA_CLO DATE
    ,TIME_CLO DATE
    ,NAME VARCHAR2(32)
    ,SITE_ID VARCHAR2(16)
    );

    And I ran your insert:

    INSERT INTO wr
    (prob_type, requestor, wo_id, date_requested, time_requested,status, pms_id,
    pmp_id, bl_id, fl_id, rm_id, tr_id, site_id, date_assigned, eq_id, description )
    VALUES ('MANUT PREVENTIVA',NULL,27,
    TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD')||' 00:00:00','YYYY-MM-DD HH24:MIS'),
    TO_DATE('1899-12-30 17:16:24','YYYY-MM-DD HH24:MIS'),'I',3114,
    'ADE SETTIMANALE','H-AC','P14','L003','ACEL','OM',
    TO_DATE('2003-03-20 00:00:00','YYYY-MM-DD HH24:MIS'),
    'ADEH-ACP14L003',
    '- Controllo pressione alimentazione
    - Controllo assenza perdite
    - Rabbocco delle resine scambiatrici
    - Controllo del funzionamento idroelettrico di tutti gli impianti
    - Controllo dei cicli di rigenerazione degli addolcitori
    - Controllo del ciclo di lavaggio filtri
    - Controllo livello del sale ed eventuale reintegro (compresa fornitura)
    Se necessario:
    - Pulizia filtri (compresa fornitura) - Rigenerazioni delle bombole (compresa fornitura)
    - Analisi dell*acqua in uscita dopo la rigenerazione manuale e rilievo dei seguenti valori:
    durezza, presenza di salamoia');

    It worked!

    SQL> select description from wr;

    DESCRIPTION
    --------------------------------------------------------------------------------
    - Controllo pressione alimentazione
    - Controllo assenza perdite
    - Rabbocco delle resine scambiatrici
    - Controllo del funzionamento idroelettrico di tutti gli impianti
    - Controllo dei cicli di rigenerazione degli addolcitori
    - Controllo del ciclo di lavaggio filtri
    - Controllo livello del sale ed eventuale reintegro (compresa fornitura)
    Se necessario:
    - Pulizia filtri (compresa fornitura) - Rigenerazioni delle bombole (compresa fo
    rnitura)
    - Analisi dell*acqua in uscita dopo la rigenerazione manuale e rilievo dei segue
    nti valori:
    durezza, presenza di salamoia


    This makes me VERY SURE that your multiple lines for description have a LOT of trailing blanks in them. To be sure, you would need to edit the script with an editor capable of displaying blanks in a visible manner - or at least one that made it clear where the end of each line physically is.

Posting Permissions

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