Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Too many values in SQL insert statement.

Posted on 2009-07-13
9
Medium Priority
?
2,160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 59

Expert Comment

by:HainKurt
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 2000 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
Independent Software Vendors: 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 59

Expert Comment

by:HainKurt
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 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