CODEWITHSUNDEEP
oracle-databaseplsql
Chris
Chris

Reputation: 93

Executing the PL/SQL block gives an error which is not understandable

The question given was to write a PL/SQL block to print the details of the customers whose total order quantity is greater than 200 where the Customer table had ID number(5) primary key, Name varchar2(20), Contact_No varchar2(10) and the Order table had Order_Id number(5) primary Key, Quantity number(4) not null, C_id number(5) references Customer(ID).

If no record is found "No Records Found" is to be printed out.

This is the Code I wrote: 

SET SERVEROUTPUT ON;

begin
  dbms_output.put_line('Customer_Id ' || 'Customer_Name '|| 'Customer_Phone');
  for cur_d in (select o.C_ID,total AS sum(o.QUANTITY) from Orders o group by o.C_ID) loop
  from Customers c
  where c.ID = cur_d.C_ID and cur_d.total > 200;
  dbms_output.put_line(c.ID || c.Name || c.Contact_No);
  end loop;
end;
/

The error I faced was - 

for cur_d in (select o.C_ID,total AS sum(o.QUANTITY) from Orders o group by o.C_ID) loop 

ERROR at line 2: 
ORA-06550: line 2, column 41: 
PL/SQL: ORA-00923: FROM keyword not found where expected 
ORA-06550: line 2, column 15: 
PL/SQL: SQL Statement ignored 
ORA-06550: line 3, column 1: 
PLS-00103: Encountered the symbol "FROM" when expecting one of the following: 
( begin case declare exit for goto if loop mod null pragma 
raise return select update while with '<'an identifier'>'
'<'a double-quoted delimited-identifier'> ')

Upvotes: 0

Views: 861

Answers (3)

William Robertson
William Robertson

Reputation: 15991

This is the kind of issue where formatting your code will make the problem obvious. If you always start a new line and indent after for xxx in ( and also place the closing bracket on its own line, and include some gaps between commands, you'll get this, which is clearly wrong:

begin
    dbms_output.put_line('Customer_Id ' || 'Customer_Name '|| 'Customer_Phone');

    for cur_d in (
        select o.c_id, total as sum(o.quantity)
        from   orders o
        group by o.c_id
    )
    loop
        from   customers c
        where  c.id = cur_d.c_id
        and    cur_d.total > 200;

        dbms_output.put_line(c.id || c.name || c.contact_no);
    end loop;
end;

The first statement inside the loop seems to be missing something, as ekochergin mentioned.

total as sum(o.quantity) is backwards as Turo mentioned.

If you want id, name and contact_no to be printed in columns, you should look at lpad and rpad for formatting them. Just concatenating them together will produce something unreadable.

The dbms_output inside the loop refers to c.id, c.name and c.contact_no, but the record is called cur_d, not c.

Also cur_d is a slightly confusing name for a record (it's not a cursor). I always use r for cursor records unless there is some other r involved that it could be confused with.

Upvotes: 0

Belayer
Belayer

Reputation: 14861

Even after you correct the substantial that have been pointed out your procedure will still fail. You indicate that if no record is found printing a message to that effect. That in itself is ambiguous. Does that mean for the no records in the data table or no records for a given customer? Either way you have no code to produce such a message. How do you expect it to be written. Finally, SQL is set based processing so you need to start thinking in terms of sets instead of loops. The following reduces the db access to a single query; the loop is only to print results. 

begin
    dbms_output.put_line('Customer_Id ' || 'Customer_Name '|| 'Customer_Phone' || 'Total Orders');

    for cur_d in (
        with order_totals as 
             ( select c_id,  sum (quantity) order_total
                 from orders
                group by c_id
               having sum(quantity) > 200
             ) 
        select c.id, c.name, c.contact_no
             , case when o.c_id is null 
                    then 'No Records Found'
                    else to_char(o.order_total)
                end order_total
          from customers c
          left join order_totals o 
               on c.id = o.c_id  
         order by c.id
        )
    loop 
       dbms_output.put_line(cur_d.ID || cur_d.Name || cur_d.Contact_No || cur_d.order_total);
    end loop;
end;

The results are jammed together just as you initially had them. You need to workout their presentation.

Upvotes: 1

ekochergin
ekochergin

Reputation: 4129

There is a "select into" part missing between "for .. loop" and "from" parts. It has to be like this in order to work

for ..... loop
  select some_column -- <-- this line is missing
    into some_variable -- <-- this line is missing too
    from ..........

Upvotes: 1

Related Questions