Sql – ERROR PLS-00103: Encountered the symbol “DECLARE”

databaseplsqlsqlstored-procedures

I have a procedure that is bringing up the error 'PL/SQL: Statement ignored'. This message is a little vague and I cant figure out why my procedure won't compile. All the procedure should do is check if the customer's delivery date is less then the SYSDATE and if it is delete and if not print 'customer can't be deleted'.

The code for the procedure is here:

    CREATE PROCEDURE remove_customer (customer_id VARCHAR2) IS
declare
   ordersCount pls_integer;
BEGIN
   select count(*) into ordersCount 
   from placed_orders 
   where fk1_customer_id = remove_customer.customer_id
   and delivery_date < sysdate;
if ordersCount = 0 then
THEN
DELETE FROM order_line
WHERE  order_line.FK1_order_id in
(SELECT order_id FROM placed_order
 WHERE placed_order.FK1_customer_id = remove_customer.customer_id
);
DELETE FROM placed_order
WHERE placed_order.FK1_customer_id = remove_customer.customer_id;
DELETE FROM customer
WHERE customer.customer_id = remove_customer.customer_id;
total_customers := total_customers - 1;
ELSE
DBMS_OUTPUT.PUT_LINE 'Customer currently has a order been delivered';
END IF;
END;

And the error message is specifying PLS-00103: Encountered the symbol "DECLARE"

thanks for any advice.

Best Solution

This line:

IF placed_order.delivery_date < SYSDATE

doesn't make much sense - you cannot use a column like this (think about it: which of the rows in placed_order should be compared to SYSDATE? One? All?).

If you want to check whether this customer has a delivery that's already been delivered, you need an additional SELECT:

 CREATE PROCEDURE remove_customer (customer_id VARCHAR2) IS       
  ordersCount pls_integer;
 begin
   select count(*) into ordersCount 
   from placed_orders 
   where fk1_customer_id = remove_customer.customer_id
   and delivery_date < sysdate;

   if ordersCount = 0 then
    -- your code for deleting the customer here
   else
    -- raise error, show message, ...
   end if;
Related Question