Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Too many values in SQL insert statement.

Posted on 2009-07-13
9
Medium Priority
?
2,204 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
8 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 61

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 61

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

773 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