Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

oracle error: not enough values

Tags:

sql

oracle

i have a table donor_master:

create table donor_master  
(  
donor_id number(10) primary key not null,  
dob date not null,  
age number(3) not null,  
gender char(1) not null,  
blood_group char(3),  
contact_no number(10),  
address varchar(50) not null,  
city varchar(10) not null,  
pin number(10) not null,  
state varchar(10) not null,  
branch_registration_id number(5) references branch_master(branch_id)  
);  

when i try to insert into the table in a procedure insert_donor_master, i get "not enough values" error on compilation.

this is the procedure:

create or replace procedure insert_donor_master(  
vdob donor_master.dob%type,  
vage donor_master.age%type,  
vgender donor_master.gender%type,  
vblood_group donor_master.blood_group%type,  
vcontact_no donor_master.contact_no%type,  
vaddress donor_master.address%type,  
vcity donor_master.city%type,  
vpin donor_master.pin%type,  
vstate donor_master.state%type,  
vbranch_registration_id donor_master.branch_registration_id%type  
)  
is  

begin  

    insert into donor_master values (sq_donor_master.nextval, vdob, vage, vgender, vblood_group, vcontact_no, vaddress, vcity, vpin, vstate, vbranch_registration_id);  
    commit;  

end;

What is the problem?

Thanks.

like image 450
Neal Avatar asked Oct 07 '22 06:10

Neal


1 Answers

Oracle hurls ORA-00947 when we specify an INSERT statement which doesn't have a value for every column in the table.

Now, the CREATE TABLE statement you posted shows a table with eleven columns. And the stored procedure code you posted shows an insert statement with eleven values in the VALUES (...) clause.

So, the explanations are:

  1. you have a configuration management issue, and you're running the wrong version of the stored procedure or the wrong version of the table
  2. you have a configuration management issue, and the actual structure of the table isn't what you think it is (doesn't match your CREATE TABLE script)
  3. you aren't really getting an ORA-00947 error

Note that if you don't want to populate every row you can specify a projection of the relevant columns before the VALUES clause. For instance, if you just wanted to populate the mandatory columns you would code this:

insert into  donor_master 
    (donor_id, dob, age, gender, address, city, pin, state )
   values (sq_donor_master.nextval, vdob, vage, vgender, vaddress, vcity, vpin, vstate) 

All that matters is that the number of values matches the number of columns.

The complete syntax for INSERT statements is in the documentation. enter link description hereFind out more.

like image 177
APC Avatar answered Oct 10 '22 04:10

APC