How to execute a procedure in oracle developer?

Question

I am trying to execute the procedure that I created, however whenever I do EXEC ProductLineSale; it tells me Error(456,1): PLS-00103: Encountered the symbol "EXEC"

ALTER TABLE Product

ADD SalePrice DECIMAL(6,2); 

CREATE OR REPLACE PROCEDURE ProductLineSale 

AS 

BEGIN 

UPDATE Product
SET SalePrice = 0.90 * ProductStandardPrice
WHERE ProductStandardPrice >= 400;

 UPDATE Product
SET SalePrice = 0.85 * ProductStandardPrice
WHERE ProductStandardPrice < 400;

END;

EXEC ProductLineSale;

select *
FROM Product;

score 1 · Answer 1 · answered Nov 05 '18 at 13:17

You're missing a '/' - this tells SQL Developer that your stored procedure declaration is finished and you're ready to compile it.

Add a '/' after the END; and before the EXECUTE.

--create table product (id integer,
--                      productstandardprice number(7,2));
--                      
--insert into product values (1, 19.95, 0);
--insert into product values (2, 7995.99);
alter table PRODUCT add SALEPRICE decimal(6, 2);

create or replace procedure PRODUCTLINESALE as
begin
  update PRODUCT
     set
    SALEPRICE = 0.90 * PRODUCTSTANDARDPRICE
   where PRODUCTSTANDARDPRICE >= 400;

  update PRODUCT
     set
    SALEPRICE = 0.85 * PRODUCTSTANDARDPRICE
   where PRODUCTSTANDARDPRICE < 400;

end;
/

exec PRODUCTLINESALE;

select *
  from PRODUCT;

I selected the create and everything after with my cursor, and then hit F5

Ori Marko · Accepted Answer · 2018-11-07T06:28:36.137

0

You are missing / before EXEC to close PROCEDURE

See why we need forward slash:

For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.

edited Nov 07 '18 at 06:28

answered Nov 05 '18 at 05:29

Ori Marko

56,308
23
131
233

I tried this, but I still am getting the same error. – alphaX Nov 05 '18 at 05:33
@alphaX Add `/` before – Ori Marko Nov 05 '18 at 05:37
Add the `/` before what? – alphaX Nov 05 '18 at 05:42
@alphaX before execute, so previous statement is closed – Ori Marko Nov 05 '18 at 05:50
`EXECUTE` is a SQL*Plus command. `EXEC` is an accepted abbreviation of exactly the same thing. – Jeffrey Kemp Nov 07 '18 at 06:17
@JeffreyKemp Thank you, I updated my answer to reflect valid solution, please consider undo down vote – Ori Marko Nov 07 '18 at 06:19

score 0 · Answer 3 · answered Nov 05 '18 at 05:37

0

You need to enclose it with Begin and End

ALTER TABLE Product

ADD SalePrice DECIMAL(6,2); 

CREATE OR REPLACE PROCEDURE ProductLineSale 

AS 

BEGIN 

UPDATE Product
SET SalePrice = 0.90 * ProductStandardPrice
WHERE ProductStandardPrice >= 400;

 UPDATE Product
SET SalePrice = 0.85 * ProductStandardPrice
WHERE ProductStandardPrice < 400;

END;

BEGIN
EXECUTE ProductLineSale;
END;

select *
FROM Product

answered Nov 05 '18 at 05:37

Ameya Deshpande

3,580
4
30
46

I did this, but then I get the same error number but this time with the word Begin. – alphaX Nov 05 '18 at 05:40
what is the name of your package ? – Ameya Deshpande Nov 05 '18 at 05:41
What do you mean the name of my package? – alphaX Nov 05 '18 at 05:42

Kaushik Nayak · Answer 4 · 2018-11-05T05:56:48.417

On a different note, you may have a single update statement which would be more efficient than two statements.

UPDATE product
SET saleprice =
          CASE
               WHEN productstandardprice >= 400 THEN 0.90 * productstandardprice
               WHEN productstandardprice < 400  THEN 0.85 * productstandardprice
          END;

Furthermore, It is advisable not to have another column in the table if it can be generated easily from existing columns. You should prefer VIRTUAL COLUMNS

So, you may redefine the saleprice column

ALTER TABLE product ADD (
      SalePrice    DECIMAL(6,2)  GENERATED ALWAYS AS (
               CASE
                    WHEN productstandardprice >= 400 THEN 0.90 * productstandardprice
                    WHEN productstandardprice < 400  THEN 0.85 * productstandardprice
               END
          )
);

Now, the column will automatically display the computed value when you run a select from the table

How to execute a procedure in oracle developer?

4 Answers4