Execute and test stored procedure in sql developer

Question

I don't know how to execute or test this procedure. I'm new in that area. Thanks in advance.

Table Prices has id,price, datePrice and productId. Table Products has id, currentprice and name

        create or replace PROCEDURE "currentprice"
        (idproduct IN NUMBER) AS
        currentprice products.current_price%type;
        BEGIN
         SELECT price INTO currentprice 
         FROM prices
         WHERE productId=idprice AND datePrice=(select max(datePrice) FROM prices WHERE 
          productId=idprice AND datePrice<=SYSDATE);

          UPDATE products
          SET currentprice = currentprice 
          WHERE id=idproduct;
           END;

Does this answer your question? How to execute an oracle stored procedure? — Alexander Santos
– Alexander Santos, Commented Dec 21, 2019 at 17:54
Oracle errors usually start with a code like ORA, PLS or SP2. How did you call it exactly, and what was the whole error message? The query of prices might fail with no data found if there was no price stored for the specified idproduct (or productId - was that a typo?) — William Robertson
– William Robertson, Commented Dec 22, 2019 at 16:06
OK. I figured out somehow where is my mistake. I get PL/SQL procedure successfully completed. Now I need to test this. — JohnnyJP
– JohnnyJP, Commented Dec 22, 2019 at 17:14

thatjeffsmith · Accepted Answer · 2019-12-21 20:06:38Z

1

Begin
 "currentprice"(1);
End;
/

Commit;

Execute with F5 in SQL Developer, then query your table data to see the new prices.

I recommend you rename your procedure to currentprice - no quotes...you'll save yourself a lot of trouble.

You can also browse to the procedure in SQL Developer and open it, then use the Execute button.

edited Dec 21, 2019 at 20:06

answered Dec 21, 2019 at 18:44

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

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

Comments

JohnnyJP · Accepted Answer · 2019-12-22 12:03:44Z

0

I renamed procedure - no quotes. No data found again.

I have trigger which call that procedure

     create or replace TRIGGER currentprice2
       AFTER INSERT OR UPDATE OR DELETE ON Prices
       DECLARE
       s NUMBER:=priceproduct.id;
       BEGIN
        currentprice(s);
       END;

Also, I have package. Maybe I made mistake with package parameter id.

      create or replace PACKAGE priceproduct IS
      id products.id%TYPE;
      END priceproduct;

answered Dec 22, 2019 at 12:03

JohnnyJP

75 bronze badges

Collectives™ on Stack Overflow

Execute and test stored procedure in sql developer

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related