Whats going on inside that cursor FOR loop

Question:
I have created a procedure that uses a cursor FOR loop. This procedure works fine until I try to use an exception handler to trap and display a no data found message. Realizing that WHEN NO_DATA_FOUND doesn't work with the FOR loop, I declared an exception and put an IF THEN structure as the first statement in the FOR loop—trying both cursor_name%NOTFOUND and cursor_name%ROWCOUNT = 0 to raise the exception. I also tried it everywhere else in the code block, but this is the only place where it doesn't interfere with execution when data is found.
Here is my code:
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;

customerrow order_cursor%ROWTYPE;
e_no_data EXCEPTION;
BEGIN
FOR customerrow IN order_cursor
LOOP
IF order_cursor%NOTFOUND
THEN
RAISE e_no_data;
END IF;

DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;
EXCEPTION
WHEN e_no_data
THEN
raise_application_error (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || SQLERRM);
END;
So my question is: how can I loop through all of the rows identified by my SELECT statement and still detect when I haven't fetched anything at all?
Answer:
Jim, you are closer to achieving your objective than you might think. Let's review some of the basics of the cursor FOR loop:
• It does lots of work for you, namely opening, fetching from, and closing the cursor. That's nice, except you lose some control and you can also "experience technical difficulties" when it comes to getting information about what happened in your cursor FOR loop, when the loop as finished executing (such as "How many rows did I fetch? In fact, did I fetch any at all?").
• A cursor FOR loop never raises NO_DATA_FOUND and the reference to the cursor attribute %NOTFOUND within a cursor FOR loop will never return TRUE. After all, if you are executing the code inside the loop body, you are doing so precisely because a row was fetched and found!
With those points in mind, I expect the flaw in your logic above is clear. You will find below a modification of your program, with new lines bolded. By keeping track of the number of rows fetched inside the cursor FOR loop, I can now detect a "no rows found" condition after the loop is complete.
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;

l_rowcount PLS_INTEGER DEFAULT 0;
e_no_data EXCEPTION;
BEGIN
FOR customerrow IN order_cursor
LOOP
l_rowcount := order_cursor%ROWCOUNT;

DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;

IF l_rowcount = 0 THEN RAISE e_no_data; END IF;

EXCEPTION
WHEN e_no_data
THEN
raise_application_error (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
Note that I use DBMS_UTILITY.FORMAT_ERROR_STACK instead of SQLERRM. They return the same information, but the latter doesn't suffer from the former's size limit of 255 characters.
I could also, by the way, switch from a cursor FOR loop to a simple loop, and then simply check my cur%ROWCOUNT attribute after I am done looping, but before I close the cursor. This approach (shown below) would save me from having to declare a local variable to hold the count, though I would now need to declare a record to hold the data retrieved by the cursor (the cursor FOR loop does that for me implicitly).
CREATE OR REPLACE PROCEDURE customer_orders (custid CHAR)
AS
CURSOR order_cursor
IS
SELECT order_number, order_date, order_total
FROM orders
WHERE customer_number = custid;

customerrow order_cursor%ROWTYPE;
e_no_data EXCEPTION;
BEGIN
OPEN order_cursor;
LOOP
FETCH order_cursor INTO customerrow;
EXIT WHEN order_cursor%NOTFOUND;

DBMS_OUTPUT.put_line ( 'Order: '
|| customerrow.order_number
|| ' was placed on '
|| customerrow.order_date
|| ' for '
|| customerrow.order_total
);
END LOOP;

IF order_cursor%ROWCOUNT = 0 THEN RAISE e_no_data; END IF;

CLOSE order_cursor;

EXCEPTION
WHEN e_no_data
THEN
RAISE_APPLICATION_ERROR (-20101, 'This is not a valid Customer');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error- ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
Side note: you might want to review these concepts in more detail with a trip to the "Using Cursor Attributes" section of Chapter 6 in the PL/SQL User's Guide and Reference.
We could stop there, but I also had the sense, looking at your code, that you probably wrote this program following the traditional "quick and dirty" method: sitting down and typing!
I strongly encourage you to always take at least a few minutes to reflect on the "big picture" of what you are about to write—and then use that picture to guide your coding. For example, after reviewing your code, I "reverse engineered" it into the following brief narrative:
• Overview: display the orders for a customer ID.
• Validations: make sure that the customer ID is valid. If invalid print out a message informing the user of the problem.
• Basic algorithm: If customer number is valid, display any orders found in the orders table for the customer.
I then crafted a program that is structured as follows:
1 CREATE OR REPLACE PROCEDURE display_orders_for_cust (
2 customer_number_in IN orders.customer_number%TYPE
3 )
4 IS
5 l_at_least_one BOOLEAN;
6
7 CURSOR order_cur (cn IN orders.customer_number%TYPE)
8 IS
9 SELECT order_number
10 ,order_date
11 ,order_total
12 FROM orders
13 WHERE customer_number = cn;
14
15 PROCEDURE validate_customer (cn IN orders.customer_number%TYPE)
16 IS
17 BEGIN
18 -- Insert check against customer table to make sure
19 -- that this customer number if valid, which is a
20 -- separate check from whether or not it has any
21 -- orders. You will probably want to raise an
22 -- exception if it is invalid.
23 NULL;
24 END validate_customer;
25
26 PROCEDURE display_order (order_in IN order_cur%ROWTYPE)
27 IS
28 BEGIN
29 DBMS_OUTPUT.put_line ( 'Order: '
30 || order_in.order_number
31 || ' was placed on '
32 || order_in.order_date
33 || ' for '
34 || order_in.order_total
35 );
36 END display_order;
37
38 PROCEDURE display_no_orders (cn IN orders.customer_number%TYPE)
39 IS
40 BEGIN
41 DBMS_OUTPUT.put_line ( 'No orders found for customer ID '
42 || cn
43 );
44 END display_no_orders;
45 BEGIN
46 validate_customer (customer_number_in);
47
48 FOR order_rec IN order_cur (customer_number_in)
49 LOOP
50 display_order (order_rec);
51 l_at_least_one := TRUE;
52 END LOOP;
53
54 IF NOT l_at_least_one
55 THEN
56 display_no_orders (customer_number_in);
57 END IF;
58* END display_orders_for_cust;
Notice that the main body of this program (lines 46 through 57) hides the complexity and details behind local procedures. By replacing multiple lines of code with a named program unit, I essentially let my code "speak for itself." Of course, there isn't much complexity to hide in this case—but then most of our application code is considerably more complicated than this!
You can download the code for this program, with all the DDL needed to get it to compile, here.
By the way, I didn't have access to the DDL for your orders table, but is the custID column really of type CHAR? You should avoid declaring columns, variables—just about anything—of type CHAR, because it is fixed-length and can cause unexpected results when performing comparisons.
Comment:
from Ben Griffiths, Brisbane, Australia
Steven,
With the 2000 byte return value of DBMS_UTILITY.FORMAT_ERROR_STACK inside the 255 byte DBMS_OUTPUT.PUT_LINE, don't you run the risk of creating an exception within your exception handler?