Solved

Too many values in SQL insert statement.

Posted on 2009-07-13
9
2,103 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:ba_trainer
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24839734
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 24839735
check the number of parameters in your insert statement... they should match the number of columns in the definition...
0
 
LVL 48

Accepted Solution

by:
schwertner earned 500 total points
ID: 24839802
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 24839820
whats the exact error message?
0
 

Author Comment

by:ba_trainer
ID: 24839892
No, I don't have a trigger on packlist...hmmm.
The exact error is 913 too many values.

Stumped..
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24839932
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24839961
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24840131
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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Oracle IF statment 5 35
SQL Query Syntax error after > 11 39
SQL Query help 3 24
sql update 2 36
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question