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;

thatjeffsmith · Accepted Answer · 2018-11-05 13:17:14Z

1

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

answered Nov 5, 2018 at 13:17

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

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

Comments

Ori Marko · Accepted Answer · 2018-11-07 06:28:36Z

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 7, 2018 at 6:28

answered Nov 5, 2018 at 5:29

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

6 Comments

alphaX Over a year ago

I tried this, but I still am getting the same error.

Ori Marko Over a year ago

@alphaX Add / before

alphaX Over a year ago

Add the / before what?

Ori Marko Over a year ago

@alphaX before execute, so previous statement is closed

Jeffrey Kemp Over a year ago

EXECUTE is a SQL*Plus command. EXEC is an accepted abbreviation of exactly the same thing.

|

Brave Soul · Accepted Answer · 2018-11-05 05:37:13Z

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 5, 2018 at 5:37

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

3 Comments

alphaX Over a year ago

I did this, but then I get the same error number but this time with the word Begin.

Brave Soul Over a year ago

what is the name of your package ?

alphaX Over a year ago

What do you mean the name of my package?

Kaushik Nayak · Accepted Answer · 2018-11-05 05:56:48Z

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

Collectives™ on Stack Overflow

How to execute a procedure in oracle developer?

4 Answers 4

Comments

6 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related