Bulk Collect - NO_DATA_FOUND Exception Handling

When we use BULK COLLECT, if a query does not fetch any records, it does not throw NO_DATA_FOUND exception.  So, we need to check whether the collection variable has any elements or not. 

Example:

1. SQL> declare
2.  type emp_tab is table of emp%rowtype;
3.  t_emp emp_tab;
4.  begin
5.  select * bulk collect into t_emp from emp;
6.  IF t_emp.count = 0 THEN
7.   dbms_output.put_line(‘Bulk Collect: No Records in the Table’);
8.  ELSE
9.   dbms_output.put_line(t_emp.count)
10.  END IF;
11.  end;
12.  / 

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical