How to display no records found in output on cursor

Toad for Oracle
1

Hi Experts,

I wrote a query as below

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_inv
IS
SELECT api.invoice_num,api.creation_date,api.invoice_id
,api.last_updated_by,api.last_update_login,api.gl_date
FROM AP_INVOICES_ALL api
,AP_BATCHES_ALL apb
WHERE 1=1
AND apb.batch_id=api.batch_id
AND apb.batch_name=‘5Feb2018-Btch1’
AND api.cancelled_date IS NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE (‘Start of Block’);
FOR c_inv_r IN c_inv
LOOP
DBMS_OUTPUT.PUT_LINE('Invoice# '||c_inv_r.invoice_num);
END LOOP;
DBMS_OUTPUT.PUT_LINE (‘End of Block’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
END;

I need to display output as ‘No invoices found for given batch’ if that batch has no invoices. Can any one please help me on how to achieve this using FOR LOOP.

2

This forum is really for Toad specific questions, but since this is a simple question, here you go…simply add a counter and then check the counter after the loop - if it’s zero, then the cursor query didn’t grab anything.

SET SERVEROUTPUT ON;

DECLARE
l_counter number :=0;
CURSOR c_inv
IS
SELECT api.invoice_num,api.creation_date,api.invoice_id
,api.last_updated_by,api.last_update_login,api.gl_date
FROM AP_INVOICES_ALL api
,AP_BATCHES_ALL apb
WHERE 1=1
AND apb.batch_id=api.batch_id
AND apb.batch_name=‘5Feb2018-Btch1’
AND api.cancelled_date IS NULL;

BEGIN
DBMS_OUTPUT.PUT_LINE (‘Start of Block’);
FOR c_inv_r IN c_inv
LOOP
l_counter := l_counter + 1;
DBMS_OUTPUT.PUT_LINE('Invoice# '||c_inv_r.invoice_num);
END LOOP;
if l_counter = 0 then
dbms_output.put_line(‘No invoices found for given batch’);
end if;
DBMS_OUTPUT.PUT_LINE (‘End of Block’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
END;