ORA-01401 : inserted value too large for column

Let me add in more details to my issue, i checked the value that i insert, it is not bigger than the size that i define for the column. And this issue happens once or twice a month. And it will be fine only if i restart the database server..if the value is larger the problem should be persistent and should not be resolved by just restarting the server..

Any advise please...

>This issue occurs frequently on my web application, i will always need to restart my server when this happens.
you mean, restart the web server?
as I said, this error will just reject your INSERT/UPDATE, but not make the oracle server fail...
you should check the php code if it handles the error correctly...

you say you checked the value that you insert, can you post the SQL that you insert?
can you reproduce the error?
what  is the table definition?

Hi All below are the sql statement, error and table structure:
Error:Warning : ociexecute(): OCIStmtExecute: ORA-01401: inserted value too large for column in /usr/apache/htdocs/dgs/TPM/inc/reel_trans_func.php

SQL statement:
insert into REELTRANSACTION (REELID, FEEDERID, PROCESSID, LOGDATE, LOGTIME, CHILDSERIALNUM, QTY, USERID, PRODUCTIONORDERID, TRANSACTIONTYPE, STATIONID, ALTGRP) values ('$find_reelrow[REELID]','$find_reelrow[FEEDERID]','$processid', to_date('$date','DD-MM-YY HH:MI:SS AM'),to_date('$time','DD-MM-YY HH:MI:SS AM'),'$chldsernum','$rowqty','$userid','$prod_ordr_id','CONSUMPTION', '$staid', '$rowaltgrp')

Structure:
CREATE TABLE "DGS_USER"."REELTRANSACTION" ("TRANSACTIONID"
    NUMBER(28) DEFAULT NULL NOT NULL, "REELID" VARCHAR2(20 byte)
    DEFAULT NULL, "FEEDERID" VARCHAR2(30 byte) DEFAULT NULL,
    "PROCESSID" NUMBER(28) DEFAULT NULL, "LOGDATE" DATE DEFAULT
    NULL, "LOGTIME" DATE DEFAULT NULL, "CHILDSERIALNUM"
    VARCHAR2(128 byte) DEFAULT NULL, "QTY" NUMBER(10) DEFAULT
    NULL, "USERID" VARCHAR2(30 byte) DEFAULT NULL,
    "PRODUCTIONORDERID" VARCHAR2(128 byte) NOT NULL,
    "TRANSACTIONTYPE" VARCHAR2(30 byte) DEFAULT NULL, "STATIONID"
    NUMBER(28) DEFAULT 0 NOT NULL, "ALTGRP" VARCHAR2(8 byte))  
    TABLESPACE "DGSUSER" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 148592K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0)
    LOGGING

If it is and NLS_LANG problem what should i set. I am running my database and apache on Linux. I did not set NLS in the environment.

Please advise...Thanks

you posted the sql statement as in your program, but I need the sql as submitted, ie with the values when it fails.

insert into REELTRANSACTION (REELID, FEEDERID, PROCESSID, LOGDATE, LOGTIME, CHILDSERIALNUM, QTY, USERID, PRODUCTIONORDERID, TRANSACTIONTYPE, STATIONID, ALTGRP) values ('RL-1207-11111','0','242', to_date('15-JUL-07 01:11:38 PM','DD-MM-YY HH:MI:SS AM'),to_date('15-JUL-07 01:11:38 PM','DD-MM-YY HH:MI:SS AM'),'84932235','1','RON','PO_111','CONSUMPTION', '111', '10')

there is 1 thing that is "not good", which is the quotes (') around the fields that are numerical data type, ie QTY, StationID, etc. remove those:

insert into REELTRANSACTION (REELID, FEEDERID, PROCESSID, LOGDATE, LOGTIME, CHILDSERIALNUM, QTY, USERID, PRODUCTIONORDERID, TRANSACTIONTYPE, STATIONID, ALTGRP) values ('$find_reelrow[REELID]','$find_reelrow[FEEDERID]','$processid', to_date('$date','DD-MM-YY HH:MI:SS AM'),to_date('$time','DD-MM-YY HH:MI:SS AM'),'$chldsernum', 0$rowqty,'$userid','$prod_ordr_id','CONSUMPTION', 0$staid, '$rowaltgrp')

for the rest, does exactly that SQL with those values fail?
if yes, you MUST have a trigger on the table!

Hi,

Can't really catch what you meant.
you mean the quotes for the numerical data is causing this problem that i face.
This table previously has a trigger now it has been removed. The trigger is no  longer in use.

Please advise....Thank you

>you mean the quotes for the numerical data is causing this problem that i face.
no, but the quotes should not be used anyhow.

>This table previously has a trigger now it has been removed. The trigger is no  longer in use.
100% sure? you would not be the first one to say "uuups" after all.
for example, you might have looked with an login that does not have the permissions to view the triggers, or on another schema, or on a wrong database altogether?

The trigger has already been deleted. I suppose this is confirm that the trigger is not in use...and in addition there is only one database and one schema residing on the server.

Please advise...

>The trigger has already been deleted. I suppose this is confirm that the trigger is not in use
yes, that is true.

ok, what exact version of Oracle are you using?
select * from v$version;

Let's hold off on the NLS paramaters for the moment.

The insert statement you posted will never work.  the TRANSACTIONID field in the table is declared NOT NULL and the insert statement posted doesn't have it.  My guess is that angelIII is on the right track.  Something is still out there at least adding the ID for you.

Once I added that, I was able to issue the insert statement provided into the table using SQL*Plus with version 10.2.0.3.

the insert statement works ..my application has been runnin for 2 years and million of records has been added ...only recently this issue keep recurring. I do not have this issue previously.

If you see carefully i indicated columns to be inserted..so the statement should not have any problems.

the transaction id is declared as NULL. If something is out there...may i know what is that something.??

Please advise..

>may i know what is that something.??
I only know triggers that could fill them...

This is the version I am using.
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Aug 1 13:30:19 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

>the transaction id is declared as NULL.
it is definitively declared NOT NULL, with default value of NULL.
but again, I only know triggers as possible source of filling in such a column, which could explain at the same time the error you get...

This is then the correct definition please ignore the previous, it is not created as NOT NULLS, in fact if it is NULLS it should have already stopped all transaction and not only til today then it stop me..:

CREATE TABLE REELTRANSACTION ("TRANSACTIONID"
    NUMBER(28), "REELID" VARCHAR2(20 byte), "FEEDERID" VARCHAR2(2
    byte), "PROCESSID" NUMBER(28), "LOGDATE" DATE, "LOGTIME" DATE,
    "CHILDSERIALNUM" VARCHAR2(128 byte), "QTY" NUMBER(10),
    "USERID" VARCHAR2(30 byte), "PRODUCTIONORDERID" VARCHAR2(128
    byte), "TRANSACTIONTYPE" VARCHAR2(30 byte), "STATIONID"
    VARCHAR2(30 byte), "ALTGRP" VARCHAR2(8 byte))  
    TABLESPACE "DGS" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0)
    LOGGING

Please advise....

>>only recently this issue keep recurring

What changed?  Did any new servers get installed?  and new Oracle client installs?

Something had to have changed recently.