[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1008
  • Last Modified:

ORA-01401 : inserted value too large for column

The exact error message: Warning : ociexecute(): OCIStmtExecute: ORA-01401: inserted value too large for column in /usr/apache/htdocs/dgs/TPM/inc/reel_trans_func.php
I am using php and OCI8 to connect to Oracle 9i
This issue occurs frequently on my web application, i will always need to restart my server when this happens. Kindly please advise what could be wrong..
The existing solution available on the Expert Exchange Site does not help.
0
ronniedemarco
Asked:
ronniedemarco
  • 8
  • 8
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this error means that a string value that you are trying to insert is longer than the column definition where you try to put it in.
check the insert/update/delete statement on which table(s) it works, and the triggers of those tables also.
0
 
ronniedemarcoAuthor Commented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
slightwv (䄆 Netminder) Commented:
I'm wondering if this is an NLS issue.  If your database is set up for a multi-byte character set and the client isn't aware of it (NLS_LANG isn't set up properly), you may not be able to insert 10 characters into a varchar2(10) field.
0
 
ronniedemarcoAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you posted the sql statement as in your program, but I need the sql as submitted, ie with the values when it fails.
0
 
ronniedemarcoAuthor Commented:
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')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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!
0
 
ronniedemarcoAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
 
ronniedemarcoAuthor Commented:
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...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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;
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
ronniedemarcoAuthor Commented:
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..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>may i know what is that something.??
I only know triggers that could fill them...
0
 
ronniedemarcoAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
 
ronniedemarcoAuthor Commented:
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....
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now