Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
How can I fix the code below so that I won't get ERROR OTHERS ORA-01403: no data found at the end of the returned rows? It shouldn't be there If I am returning records?

Stanwood Consulting 6099 15-DEC-95 $22.50
Stanwood Consulting 6099 15-DEC-95 $7.50
ERROR OTHERS ORA-01403: no data found

SQL
SET SERVEROUTPUT ON
    DECLARE
        vcustid        CUSTOMERS.custid%type;
        vCname         CUSTOMERS.cname%type;
        vcustid_2      ORDERS.custid%type;
        vOrderid       ORDERS.orderid%type;
        vSalesDate     ORDERS.salesdate%type;
        vTotalValue    varchar2(100); 
        vNR_OF_ORDERS  pls_integer;
    
   CURSOR  Customers_cur

   IS
  
     SELECT C.custid,C.cname,O.orderid,O.salesdate,
           to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99') AS vTotalValue, 
           COUNT(O.orderid) NR_OF_ORDERS--COUNT(OI.orderid) NR_OF_ORDERS
     FROM CUSTOMERS C
           LEFT OUTER JOIN ORDERS O
            ON O.custid = C.custid
            
            LEFT OUTER JOIN ORDERITEMS OI
            ON O.orderid = OI.orderid
            
            LEFT OUTER JOIN INVENTORY I
            ON OI.partid = I.partid    
                             
   WHERE C.custid = &custid    
   GROUP BY C.custid,C.cname,O.orderid,O.salesdate,OI.qty,I.price
   ORDER BY vTotalValue DESC; 
 
   Customer_Check   Customers_cur%ROWTYPE;
   BEGIN
   
    OPEN Customers_cur;
   --to show column names.
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
      DBMS_OUTPUT.PUT_LINE('CustomerName           OrderId     SaleDdate         TotalValue'); 
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
   LOOP

     FETCH Customers_cur  INTO vCustid,vCname,vOrderid,vSalesDate,vTotalValue,vNR_OF_ORDERS;
     
     IF Customers_cur%NOTFOUND
     THEN
     RAISE NO_DATA_FOUND;
     END IF;
     
      DBMS_OUTPUT.put_line( (vCname) 
                       || '  '  || (vOrderid) 
                       || '  '  || (vSalesDate)
                       || '  '  || (vTotalValue ));
    
     IF vNR_OF_ORDERS = 0
     THEN
      DBMS_OUTPUT.PUT_LINE(  'custid :' 
                        || '  '  || vcustid
                        || '  '  ||  ' has no orders');                
    
    END IF; 

    END LOOP;  
 
     CLOSE Customers_cur;

  EXCEPTION                    
    WHEN NO_DATA_FOUND
    THEN
    DBMS_OUTPUT.put_line('ERROR OTHERS  ' || SQLERRM);              
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.put_line('ERROR OTHERS  ' || SQLERRM);
  END;
Posted
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900