sql plus stops responding after calling a stored procedure

Question

create or replace procedure pizza_qty(order_num IN order_list.order_no%type)  is
    cursor c1 is 
       select order_no,qty from order_list; 
    temp1 order_list.order_no%type;
    pizzacount order_list.qty%type;
    temp2 order_list.qty%type;
begin
    open c1;
    loop
        fetch c1 into temp1,temp2;
        if order_num=temp1 then
            pizzacount := pizzacount+1;
            --exit;
        elsif c1%notfound and pizzacount=0 then
            dbms_output.put_line('the order '||order_num||' is invalid..');
            exit;
        end if;
    end loop;
    dbms_output.put_line('the order '||order_num||' has '||pizzacount||' pizza(s) to be delivered');
    close c1;
end;
/

Answer 1

Code you wrote is ... well, rather strange. It presumes that ALL orders in that table should contain nothing but pizzas. Though, that's not the reason for your problems.

Declaring a variable, pizzacount, sets it to null. Anything + null is still null so it is never set to anything else, so your loop never exits.

Moreover, you don't need cursor; a simple select is enough. If it returns something for that order number, then those must be pizzas. Otherwise, it won't return anything at all so order number is invalid.

Something like this: test case first:

SQL> create table order_list (order_no number, qty number);

Table created.

SQL> insert into order_list (order_no, qty) values (1, 5);

1 row created.

SQL>

Procedure:

SQL> create or replace procedure pizza_qty (par_order_num in order_list.order_no%type)
  2  is
  3    l_qty order_list.qty%type;
  4  begin
  5    select qty
  6      into l_qty
  7      from order_list
  8      where order_no = par_order_num;
  9
 10    dbms_output.put_line('Order ' || par_order_num || ' has ' || l_qty ||
 11                         ' pizza(s) to be delivered');
 12  exception
 13    when no_data_found then
 14      dbms_output.put_line('Order ' || par_order_num || ' is invalid');
 15  end;
 16  /

Procedure created.

SQL>

Testing:

SQL> set serveroutput on;
SQL> exec pizza_qty(1);
Order 1 has 5 pizza(s) to be delivered

PL/SQL procedure successfully completed.

SQL> exec pizza_qty(2);
Order 2 is invalid

PL/SQL procedure successfully completed.

SQL>