How do I incorporate NEXTVAL in my procedure?

Question

I have the following procedure

create or replace 
procedure prod_add_sp
    (p_idproduct in bb_product.idproduct%type,
    p_prodname in bb_product.productname%type, 
    p_descrip in bb_product.description%type,
    p_prodimage in bb_product.productimage%type,
    p_prodprice in bb_product.price%type,
    p_prodactive in bb_product.active%type)
is
begin
   insert into bb_product(idproduct,productname,description,productimage,price,active)
   values (p_idproduct,p_prodname,p_descrip,p_prodimage,p_prodprice,p_prodactive);

commit;
end;

How do I modify the above with the seq.nextval portion so when executed a new row is inserted with a unique primary key? IDPRODUCT is a primary key so it is required.

Lokesh · Accepted Answer · 2013-04-28T02:41:44.343

Create a sequence with any name say nextID.
Now use below code:

Create a sequence with any name say nextID. Now use below code:

create or replace procedure prod_add_sp (p_idproduct in bb_product.idproduct%type, p_prodname in bb_product.productname%type, p_descrip in bb_product.description%type, p_prodimage in bb_product.productimage%type, p_prodprice in bb_product.price%type, p_prodactive in bb_product.active%type) is
begin 
insert into bb_product(idproduct,productname,description,
productimage,price,active)
values (nextID.nextVal,p_prodname,p_descrip,
p_prodimage,p_prodprice,p_prodactive);

commit; 
end;

am still new here so really don't know how to do that – Ruth Daniel Apr 28 '13 at 06:01 — Ruth Daniel, Apr 28 '13 at 06:01

score 1 · Answer 2 · answered Apr 28 '13 at 02:32

You need to create a sequence first i.e.:

CREATE SEQUENCE productsID_seq
 START WITH     0
 INCREMENT BY   1
 NOMAXVALUE;

And then in the values (... line:

insert into bb_product(idproduct,productname,description,productimage,price,active)
   values (productsID_seq.nextval,...

Here's some good info from the Oracle DB Docs

score 0 · Answer 3 · answered Apr 28 '13 at 02:30

0

You will have to create SEQUENCE first. Then you can use sequencename.nextval in the idproduct column.

answered Apr 28 '13 at 02:30

Noel

10,152
30
45
67

I already had one on file..just had to go back & check my objects.Thanks – Ruth Daniel Apr 28 '13 at 05:27

How do I incorporate NEXTVAL in my procedure?

3 Answers3