Too many values in SQL insert statement.

I am getting error, too many values, but I do not see the problem.
Can someone see why I am getting error?
I included table code and insert code...
Thanks! Beth
CREATE TABLE PACKLIST
  ( PACKLIST_NO NUMBER(9,0) NOT NULL    
  , DRAWING VARCHAR2(10) 
  , REVISION_LEVEL VARCHAR2(5)
  , CUSTOMER_NO NUMBER(8,0) NOT NULL    
  , CUSTOMER_PART_NO VARCHAR2(10)
  , CUSTOMER_PURCHASE_NO VARCHAR2(10)
  , SCHEDULED_SHIP_DATE DATE
  , ORIGINAL_SHIP_DATE DATE 
  , ACTUAL_SHIPPED_DATE DATE
  , ORDER_ACK_DATE DATE
  , UNITS NUMBER(5,0)
  , PRICE_PER_UNIT NUMBER(5,2)
  , GROUP_BY VARCHAR2(30)
  , QUANTITY_SHIPPED NUMBER(5,0)
  , CUSTOMER_SHIP_ID NUMBER(8,0) NOT NULL  
  , SATISFIED CHAR(1)
  , CONSTRAINT SATISFY_YN CHECK(SATISFIED IN ('Y', 'N'))
  , CONSTRAINT PACKLIST_PK PRIMARY KEY(PACKLIST_NO)
  , CONSTRAINT CUSTOMER_SHIP_ID_FK FOREIGN KEY(CUSTOMER_SHIP_ID)  
    REFERENCES SHIPPING(CUSTOMER_SHIP_ID));
 
 
INSERT INTO PACKLIST
VALUES(4000,'HV111','V4',123,'PT456','HV200', to_date('07/01/2009', 'mm-dd-yyyy'),to_date('07/01/2009', 'mm-dd-yyyy'),to_date('07/01/2009', 'mm-dd-yyyy'),to_date('06/01/2009', 'mm-dd-yyyy'),20,12.5,NULL,20,678,'Y');

Open in new window

ba_trainerAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
You use bad practice(using implicit list of columns)
Try this instead providing value for every column.

INSERT INTO PACKLIST(PACKLIST_NO,DRAWING,.....)
VALUES(4000,'HV111','V4',123,'PT456','HV200', to_date('07/01/2009', 'mm-dd-yyyy'),
to_date('07/01/2009', 'mm-dd-yyyy'),to_date('07/01/2009', 'mm-dd-yyyy'),to_date('06/01/2009', 'mm-dd-yyyy'),20,12.5,NULL,20,678,'Y');
0
 
sdstuberCommented:
Do you have a trigger on PACKLIST?

I ran your create table and your insert,  they work fine.

So, the error may be coming from a trigger raised on insert
0
 
HainKurtSr. System AnalystCommented:
check the number of parameters in your insert statement... they should match the number of columns in the definition...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HainKurtSr. System AnalystCommented:
whats the exact error message?
0
 
ba_trainerAuthor Commented:
No, I don't have a trigger on packlist...hmmm.
The exact error is 913 too many values.

Stumped..
0
 
schwertnerCommented:
Types, value length and number of columns and values should be coordinated.
'Too many values" means that the list after VALUE keyword comprases more entries as the list of the columns.
0
 
sdstuberCommented:
the code works "as is",  I'll agree it's bad practice to leave out the columns in your inserts,  but this statement, as shown, is syntactically correct.

you can run the dll and insert provided,
remove the fk constraint unless you happen to have the SHIPPING table
and the insert runs just fine.

so, there must be something not published that is the problem,
the only thing that can produce an   ORA-00913: too many values
from the above would be another sql being issued in the background
which would imply a trigger.

If you can't see it, maybe the trigger is owned by another schema.
or maybe there are FGA or FGAC  policies firing.  Those policies aren't technically "triggers" per se, but could generate errors.

It's also possible, if you are running the above insert inside an application that your app's error handling is capturing an error from a different SQL but reporting it for this one.
0
 
sdstuberCommented:
ba_trainer,

what was of use to you in the accepted post 24839802?

Nothing in that post addressed your error.
schwertner was simply commenting on your programmatic style.
Not that he was wrong, but I fail to see how anything in that comment could be taken as a solution to your answer.
0
All Courses

From novice to tech pro — start learning today.