[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1406
  • Last Modified:

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?
0
cblaese
Asked:
cblaese
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now