PLS-00103: Encountered the symbol "DECLARE"

Question

i am trying to execute or run the following pl/sql script:

SET serveroutput on;

CREATE OR REPLACE PROCEDURE findAvg
(p_category IN products.category_id% TYPE, c OUT NUMBER)
AS
BEGIN
SELECT NVL ((SELECT AVG(LIST_PRICE) FROM products
WHERE p_category = category_id), -1) into p_category
from dual;
END findAvg;
DECLARE
 cat  products.category_id%TYPE;
 Price   products.List_price%TYPE;
BEGIN
cat := &p_category;
findAvg (cat, price); 
if (price = -1) then
    dbms_output.put_line('Wrong Category ');
ELSE
    dbms_output.put_line('the average price for category' || cat || ' is ' || price);
 END IF;
END;
/
show errors

but when i try to run it, i get this error message (i can see it only after show errors):

       PLS-00103: Encountered the symbol "DECLARE" 

what is wrong with this declare?

Answer 1

You are missing a "/" between the creation of the procedure and the start of the anonymous block that runs it:

SET serveroutput on;

CREATE OR REPLACE PROCEDURE findAvg
(p_category IN products.category_id% TYPE, c OUT NUMBER)
AS
BEGIN
  SELECT NVL(AVG(LIST_PRICE),-1)
  INTO c
  FROM products
  WHERE p_category = category_id;
END findAvg;
/

show errors

DECLARE
 cat  products.category_id%TYPE;
 Price   products.List_price%TYPE;
BEGIN
cat := &p_category;
findAvg (cat, price); 
if (price = -1) then
    dbms_output.put_line('Wrong Category ');
ELSE
    dbms_output.put_line('the average price for category' || cat || ' is ' || price);
 END IF;
END;
/

Also, the "show errors" command should be run just after creating the procedure, as I have above, and the INTO clause should specify the OUT parameter.