CODEWITHSUNDEEP
sqloracle-databaseplsql
hawk
hawk

Reputation: 39

PL SQL procedure not raising exception no_data_found

I have two tables as customers and orders as follows: Customers:

+----+------+------------+
| ID | Name | Contact_no |
+----+------+------------+
|  1 | Matt | 9435112340 |
|  2 | John | 6654342312 |
|  3 | Jill | 6654342312 |
+----+------+------------+

Orders:

+----------+------------+----------+------+
| Order_id | Order_date | Quantity | C_id |
+----------+------------+----------+------+
|     1011 | 09-jan-18  |       30 |    1 |
|     1012 | 09-feb-18  |      300 |    1 |
|     1013 | 09-feb-18  |      200 |    3 |
|     1111 | 09-feb-18  |      100 |    1 |
|     1016 | 09-feb-18  |       20 |    2 |
+----------+------------+----------+------+

I am writing a PL/SQL procedure which lists the id, name and contact_no of those who have the order quantity greater than 200. I am able to achieve this. But if no records exists, then i want to print a message "No Records Found". I know that if there are no records, then oracle throws no_data_found exception. Thus, I have written my procedure according to that. But, though there are not records, oracle is not throwing exception and my Exception block is not executing and nothing is printing as output. Can anyone please help what is going wrong and how can i achieve the required results? Below is my procedure.

DECLARE
    CURSOR cust_cur IS
    SELECT * FROM customers WHERE customers.id IN (SELECT c_id FROM orders WHERE orders.quantity>300 group by c_id);
BEGIN
    FOR cust IN cust_cur LOOP
        dbms_output.put_line('Customer_id Customer_Name Customer_Phone');
        dbms_output.put_line(cust.id||' '||cust.name||' '||cust.contact_no);
    END LOOP;
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('No data found');
    WHEN OTHERS THEN
    dbms_output.put_line('Error Due To -->'|| SQLCODE||SQLERRM);
END;

I have changed the condition of quantity in SQL statement from 200 to 300, as it will return no rows.

Upvotes: 0

Views: 645

Answers (2)

mustafa yılmaz
mustafa yılmaz

Reputation: 96

You may control data count in loop manually. Like below.

declare
   cursor cust_cur
   is
      select *
        from customers
       where customers.id in (  select c_id
                                  from orders
                                 where orders.quantity > 300
                              group by c_id);

   l_count   number := 0;
begin
   for cust in cust_cur
   loop
      DBMS_OUTPUT.put_line ('Customer_id Customer_Name Customer_Phone');
      DBMS_OUTPUT.put_line (
         cust.id || ' ' || cust.name || ' ' || cust.contact_no);
      l_count := 1;
   end loop;

   if l_count = 0
   then
      raise NO_DATA_FOUND;
   end if;
exception
   when NO_DATA_FOUND
   then
      DBMS_OUTPUT.put_line ('No data found');
   when others
   then
      DBMS_OUTPUT.put_line ('Error Due To -->' || SQLCODE || SQLERRM);
end;

Upvotes: 1

djanoti
djanoti

Reputation: 303

no_data_dound is raised only by select..into..from clause This "FOR cust IN cust_cur LOOP" is a loop and not a select statement.. loops like these are treated as bulk collect statements by oracle internally and default limit to which is 100.

Upvotes: 1

Related Questions