PL/SQL: Row isn't inserting

Question

I'm learning PL/SQL and I've created this procedure, which compiles; I'm not experiences enough with PL/SQL to spot where I went wrong:

Just to be clear, my question is, 'what did I do wrong?'

I'm using Datagrip and port forwarding to an OracleVM; I doubt there's a problem there, because I've inserted rows before, but this just isn't working.

CREATE OR REPLACE PROCEDURE basket_add_sp
  (
    p_basketid IN bb_basketitem.idbasket%TYPE,
    p_prodid IN bb_basketitem.idproduct%TYPE,
    p_qty IN bb_basketitem.quantity%TYPE,
    p_price IN bb_basketitem.price%TYPE,
    p_size IN bb_basketitem.option1%TYPE,
    p_form IN bb_basketitem.option2%TYPE
  )
  IS
  BEGIN
  INSERT INTO BB_BASKETITEM(idbasketitem, idproduct, quantity, price, idbasket, option1, option2)
    VALUES (bb_idBasketitem_seq.NEXTVAL, p_prodid, p_qty, p_price, p_basketid, p_size, p_form);
  COMMIT;
END;

Which completes.

[2016-07-21 21:18:28] completed in 18ms

I run the procedure:

BEGIN
  basket_add_sp(14,8,1,10.80,2,4);
END;

Also completes.

[2016-07-21 21:18:39] completed in 14ms

But when I check the table to verify, no rows are returned.

SELECT * FROM BB_BASKETITEM WHERE IDBASKETITEM = 14;

Returns this:

However, when I check the table, there are other rows there.

SELECT * FROM BB_BASKETITEM WHERE IDBASKETITEM = 20;

Returns this:

Peter M. · Accepted Answer · 2016-07-22 02:58:17Z

2

SELECT * FROM BB_BASKETITEM WHERE IDPRODUCT = 8;

IDBASKETITEM is a unique ID that Oracle generates by sequence when you do the insert.

answered Jul 22, 2016 at 2:58

Peter M.

7131 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NonCreature0714 Over a year ago

That does show all IDPRODUCT which are 8, but why won't IDBASKETITEM 14 display? I did insert a new row with it as PK, didn't I? Why isn't that working? Sorry, I'm just very confused why I don't get a row with 14 as IDBASKETITEM.

Peter M. Over a year ago

Yes, you passed in p_basketid as a parameter to the function.. but the first value in the insert statement doesn't use p_basketid.. instead it uses a value that it gets from the Oracle sequence bb_idBasketitem_seq.NEXTVAL

NonCreature0714 Over a year ago

Oh, okay, I see it now, can't believe I missed it - thanks a ton!

Peter M. Over a year ago

Sure thing. Best of luck with the rest!

Collectives™ on Stack Overflow

PL/SQL: Row isn't inserting

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related