Solved

Too many values in SQL insert statement.

Posted on 2009-07-13
9
2,077 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 73

Expert Comment

by:sdstuber
Comment Utility
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:HainKurt
Comment Utility
check the number of parameters in your insert statement... they should match the number of columns in the definition...
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
whats the exact error message?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Stumped..
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now