FORALL SELECT getting NO DATA FOUND

Question

Similar to a question posted (PL/SQL No Data found error on forall loop), I recently just found out that i can use FORALL with SELECT. so i tried the below code in a PL/SQL block:

FORALL i in p_eit_hdr_rec.first..p_eit_hdr_rec.last
select  count(*)
into    l_exists
from    TABLE_X x1
    ,   TABLE_Y x2
    ,   TABLE_Z x3
where   x2.batch_name = p_eit_hdr_rec(i).batch_name
and     x1.person_id  = x2.person_id
and     x2.status     = 'New'
and     x3.information_type = x1.information_type
and     x3.EIT_INT_HDR_ID   = x2.EIT_INT_HDR_ID;

Where p_eit_hdr_rec is an Associative Array, p_eit_hdr_rec(i).batch_name contains the string 'hire_20170711_200005.csv' and l_exists is a NUMBER variable defaulted to 0. I get the NO_DATA_FOUND exception even if i confirmed that there is data being fetched when i query it as a normal SQL:

select  count(*)
into    l_exists
from    TABLE_X x1
    ,   TABLE_Y x2
    ,   TABLE_Z x3
where   x2.batch_name = 'hire_20170711_200005.csv'
and     x1.person_id  = x2.person_id
and     x2.status     = 'New'
and     x3.information_type = x1.information_type
and     x3.EIT_INT_HDR_ID   = x2.EIT_INT_HDR_ID;

COUNT(*)
--------
2

Why am i Gettting the Exception? Is FORALL SELECT a valid syntax but not valid for checking data?

Answer 1

I recently just found out that i can use FORALL with SELECT.

No, you can't.

Reading the fine manual about FORALL Statement:

dml_statement

A static or dynamic INSERT, UPDATE, DELETE, or MERGE statement that references at least one collection in its VALUES or WHERE clause. Performance benefits apply only to collection references that use index as an index.

So, one can't use forall with select. The corresponding bulk SQL operation for select is SELECT INTO Statement with BULK COLLECT Clause.

Happy reading !