Clicky

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?

asked 10/24/2011 09:09

cblaese's gravatar image

cblaese ♦♦


11 Answers:
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);
...
link

answered

slightwv's gravatar image

slightwv

what is the error?
link

answered 2011-10-24 at 17:16:52

sdstuber's gravatar image

sdstuber

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

answered 2011-10-24 at 17:17:19

sdstuber's gravatar image

sdstuber


Here's my test case

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
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);


Here are my results....

1:
2:
3:
4:
Sequence created.
Table created.
Procedure created.
PL/SQL procedure successfully completed.
link

answered 2011-10-24 at 17:21:27

sdstuber's gravatar image

sdstuber

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
link

answered 2011-10-24 at 17:25:58

cblaese's gravatar image

cblaese

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

answered 2011-10-24 at 17:26:52

slightwv's gravatar image

slightwv

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
link

answered 2011-10-24 at 17:29:29

sdstuber's gravatar image

sdstuber

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

answered 2011-10-24 at 17:31:58

slightwv's gravatar image

slightwv

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

answered 2011-10-24 at 17:32:29

ebradshaw's gravatar image

ebradshaw

ebradshaw,

 I believe that was already mentioned in http:#a37021812.
link

answered 2011-11-27 at 10:05:16

slightwv's gravatar image

slightwv

Figured out that the problem was due to a total unrelated issue.

link

answered 2011-11-28 at 07:20:22

cblaese's gravatar image

cblaese

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 10/24/2011 09:09

Seen: 314 times

Last updated: 12/16/2011 05:20