Using sequence.nextval in stored procedure

I am creating a stored procedure to insert new rows into a table.  The ID column is the primary key which is generated by a sequence.nextval.  The procedure I have created allows for input parameters.  Then I use an insert statement to add those parameters to the table. When executed, the procedure is created successfully.  Procedure:

Create Or Replace Procedure Prod_Add_sp
(p_Name In bb_Product.ProductName%Type,
p_Description In bb_Product.Description%Type,
p_Image In bb_Product.ProductImage%Type,
p_Price In bb_Product.Price%Type,
p_Status In bb_Product.Active%Type)
Is
Begin
Insert Into bb_Product (IDProduct, ProductName, Description, ProductImage, Price, Active)
Values (bb_ProdId_seq.NextVal, Prod_add_sp.p_Name, Prod_add_sp.p_Description, p_Image, p_price, p_status);
Commit;
End;

However, when I try to execute the procedure to test it using:
Execute Prod_Add_sp
( 'Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg', 9.50, 1);

 I get an error message:
Execute Prod_Add_sp
(bb_ProdId_seq.NextVal, 'Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg', 9.50, 1);

What am I doing wrong?
cblaeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
On mobile but try to remove the procedure on the values clause.

What is the error message?


...
Begin
Insert Into bb_Product (IDProduct, ProductName, Description, ProductImage, Price, Active)
Values (bb_ProdId_seq.NextVal, p_Name, p_Description, p_Image, p_price, p_status);
...
0
sdstuberCommented:
what is the error?
0
sdstuberCommented:
the procedure syntax itself is fine.

perhaps there is a problem in the invocation

'Roasted Blend',    ---- is product name as string type? does it have a length of 13 or more?
'Well-balanced mix of roasted beans, a medium body',    ---- is description as string type? does it have a length of 49 or more?  
'roasted.jpg',  ---- is image as string type? does it have a length of 11 or more?
9.50,  ---- is price numeric?
1   ---- is active numeric?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:

Here's my test case

CREATE SEQUENCE bb_prodid_seq;

CREATE TABLE bb_product
(
    idproduct    NUMBER,
    productname  VARCHAR2(20),
    description  VARCHAR2(100),
    productimage VARCHAR2(20),
    price        NUMBER,
    active       NUMBER
);

CREATE OR REPLACE PROCEDURE prod_add_sp(
    p_name          IN bb_product.productname%TYPE,
    p_description   IN bb_product.description%TYPE,
    p_image         IN bb_product.productimage%TYPE,
    p_price         IN bb_product.price%TYPE,
    p_status        IN bb_product.active%TYPE
)
IS
BEGIN
    INSERT INTO bb_product(
                    idproduct,
                    productname,
                    description,
                    productimage,
                    price,
                    active
                )
    VALUES (
               bb_prodid_seq.NEXTVAL,
               prod_add_sp.p_name,
               prod_add_sp.p_description,
               p_image,
               p_price,
               p_status
           );

    COMMIT;
END;

EXECUTE Prod_Add_sp( 'Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg', 9.50, 1);

Open in new window


Here are my results....

Sequence created.
Table created.
Procedure created.
PL/SQL procedure successfully completed.

Open in new window

0
cblaeseAuthor Commented:
Oops!  error message is:
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROD_ADD_SP'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

( 'Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg', 9.50, 1)
  *

ERROR at line 1:
ORA-00928: missing SELECT keyword

Also, in response to questions asked by sdstuber:
Product name, description and image are all string type and not too long  -- price and active are numeric
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Are you sure you are using 11g?

Before that you had to select the nextval into a variable from dual then use the variable in the insert.
0
sdstuberCommented:
what is your table definition?


describe  bb_product



your first error looks like an invocation error because of bad types  (compare your table to the one I created to test with)

your second error looks like it could be the sequence problem slightwv mentioned
0
slightwv (䄆 Netminder) Commented:
Missed this in the first post.

The second sql shows nextval in the execute call:

Execute Prod_Add_sp
(bb_ProdId_seq.NextVal, ...

Do not add that on the call if that is in the procedure.
0
ebradshawCommented:
In your second call to the PL/SQL procedure, you included the NEXTVAL call in the procedure call.  That isn't part of your procedure specification and thus will result in an error.
0
slightwv (䄆 Netminder) Commented:
ebradshaw,

 I believe that was already mentioned in http:#a37021812.
0
cblaeseAuthor Commented:
Figured out that the problem was due to a total unrelated issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.