CODEWITHSUNDEEP
sqloracle-database
srinu jasti
srinu jasti

Reputation: 1

Automatic sequence in oracle

I am trying to cretae a automatic sequence number genaration using trigger but it is giving me following wrror while inserting the values.

ALTER TABLE sppinv_tblinventory_ex ADD (
  CONSTRAINT sppinv_tblinventory_PK PRIMARY KEY (uniqueid));

create  sequence row_seq ;

create or replace trigger row_count before insert on sppinv_tblinventory_ex
for each row

begin
 select row_seq.nextval into : new.uniqueid from dual;
end;

if I am excuting below then I am able to insert values

insert into sppinv_tblinventory_ex
select  row_seq.nextval,
  b.member_id,b.src_claim_nbr,b.client_nbr,b.src_platform_cd,
  b.suspense_date,b.batch_gen_key,b.bucket_name,b.grouper_rule,
  b.event_number,b.case_stat,b.case_stat_dt,b.assigned_to,
  b.assigned_on,b.followup_dt,b.release_ind,b.release_dt,
  b.viewtype
from sppinv_tblinventory b

When I am inserting the values with out uniqueID I am getting error like below

insert into sppinv_tblinventory_ex
select  b.member_id,b.src_claim_nbr,b.client_nbr,b.src_platform_cd,
  b.suspense_date,b.batch_gen_key,b.bucket_name,b.grouper_rule,
  b.event_number,b.case_stat,b.case_stat_dt,b.assigned_to,
  b.assigned_on,b.followup_dt,b.release_ind,b.release_dt,
  b.viewtype
from sppinv_tblinventory b

ORA-00947: not enough values

Note : I dont want to disable the trigger

Upvotes: 0

Views: 109

Answers (2)

Shankar
Shankar

Reputation: 879

ORA-00947: not enough values - means you have n number of columns in the table but you are only supplying values for (n-m) number of fields.

In your case, if you dont want to insert unique id, then you may have to do 

Insert into sppinv_tblinventory_ex (col1, col2, col3.. coln) select (val1, val2, val3 .. valn)

Upvotes: 4

Romain A.
Romain A.

Reputation: 1

There are so many answers for this on internet...
Try removing the space before new.uniqueid ; and add a IF test like this :

create or replace trigger row_count
before insert on sppinv_tblinventory_ex
for each row
begin
    IF :new.uniqueid IS NULL THEN
        select row_seq.nextval into :new.uniqueid from dual;
    END IF;
end;

Now if you put null in the corresponding value field in your insert, it should work

Upvotes: -1

Related Questions