This discussion is archived
5 Replies Latest reply: Dec 3, 2012 10:47 PM by BillyVerreynne RSS

Testing Procedure

977726 Newbie
Currently Being Moderated
I have created a procedure to calculate a discount based on the order total. SQL plus tells me that the procedure is created successfully, but I am not getting any output.

create or replace procedure discount is
cursor c1 is
select order_line.o_id, sum(inv_price*ol_quantity)
     from customer,inventory,order_line,orders
     where customer.c_id=orders.c_id
     and orders.o_id=order_line.o_id
     and inventory.inv_id=order_line.inv_id
     group by customer.c_id, c_first,c_mi,c_last;
     total number;
     order_id number;
     original_amt number;
     
begin
open c1;
loop

fetch c1 into order_id, original_amt;
     exit when c1%notfound;
     
     if
          original_amt between 100 and 200
     then
          total:= original_amt*.9;
     elsif
          original_amt > 200
     then
          total:= original_amt*.8;
     else
          original_amt:=original_amt;
     end if;
     dbms_output.put_line(order_id||' '||original_amt||' '||total);
     end loop;
     
end discount;
/

The output I am trying to get is:

Order Id: Original_amt: Total:
1 379.89 303.912
2 15.99 0
3 409.9 327.92
4 119.9 107.91
5 89.9 0
6 349.84 279.872

Edited by: MikeCoffey on Dec 3, 2012 2:35 PM
  • 1. Re: Testing Procedure
    Justin Cave Oracle ACE
    Currently Being Moderated
    OK, so you created the procedure. Did you execute the procedure? Did you
    SQL> set serveroutput on size unlimited;
    before executing the procedure to enable the DBMS_OUTPUT output?

    Can you copy and paste from a SQL*Plus session where you
    SQL> set serveroutput on size unlimited;
    SQL> exec discount;
    Of course, if this is real code, you would want your procedure to do something other than write output to the DBMS_OUTPUT buffer since there is no guarantee that the buffer exists or that the client application knows how to read from the buffer or that it has been instructed to read from the buffer.

    Justin
  • 2. Re: Testing Procedure
    977726 Newbie
    Currently Being Moderated
    SQL> set serveroutput on size unlimited;
    SQL> exec discount;
    BEGIN discount; END;

    *
    ERROR at line 1:
    ORA-00979: not a GROUP BY expression
    ORA-06512: at "SYSTEM.DISCOUNT", line 3
    ORA-06512: at "SYSTEM.DISCOUNT", line 16
    ORA-06512: at line 1

    I guess you could say that this is not "real" code. I am doing this for a school assignment.

    Thanks,
    Mike
  • 3. Re: Testing Procedure
    Justin Cave Oracle ACE
    Currently Being Moderated
    OK, that implies that the procedure had syntax errors when you created it. Are you sure that the procedure was created successfully? Or did you get a message after creating it that there were errors? If so, you can type
    SQL> show errors;
    in SQL*Plus to get a list of the errors.

    The query you are using for your cursor appears to be incorrect-- you cannot SELECT columns that you are not grouping by. My guess is that you really wanted the query to be
    select order_line.o_id, sum(inv_price*ol_quantity)
      from customer,inventory,order_line,orders
     where customer.c_id=orders.c_id
       and orders.o_id=order_line.o_id
       and inventory.inv_id=order_line.inv_id
     group by order_line.o_id;
    Justin
  • 4. Re: Testing Procedure
    977726 Newbie
    Currently Being Moderated
    Thanks, it is working now.
  • 5. Re: Testing Procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Mike, keep in mind that the cursor-loop-fetch approach is not optimal by any means. It is (less than affectionately) known as slow-by-slow row based processing.

    There is no need to fetch a row and use PL/SQL to process it - when SQL can do that for you. No PL/SQL in-between rows needed.

    Have a look at the CASE statement of the SQL language - and consider using it instead of IF conditions in PL/SQL.

    The basic approach to optimised and scalable Oracle development is:
    Maximise SQL. Minimise PL/SQL (or Java or .Net).

    In other words, solve database problem using the SQL language. It is extremely capable and very powerful. Use PL/SQL to deal with things like conditional processing and branching, exception handling, applying business logic and so on. It is should be pretty much an exception to pull a row into the PL/SQL engine at a time, from the SQL engine, to process using PL/SQL.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points