CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle-sqldeveloper
alphaX
alphaX

Reputation: 41

How to execute a procedure in oracle developer?

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;

Upvotes: 1

Views: 1147

Answers (4)

Ori Marko
Ori Marko

Reputation: 58892

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.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22467

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

enter image description here

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

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

Upvotes: 0

Brave Soul
Brave Soul

Reputation: 3620

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

Upvotes: 0

Related Questions