Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_x (account_no, clmn_x) as
select 1, 10 from dual union all
select 2, 20 from dual
2 rows affected

create table table_y (account_no, clmn_y, ref_id) as
select 1, 11, 101 from dual union all
select 2, 21, 201 from dual union all
select 2, 22, 201 from dual
3 rows affected

create table table_z (sub_no, subscr_no_resets, status, ext_id) as
select 10, 11, 1, cast(null as varchar2(30)) from dual union all
select 10, 11, 2, cast(null as varchar2(30)) from dual union all
select 20, 21, 1, cast(null as varchar2(30)) from dual union all
select 20, 21, 1, cast(null as varchar2(30)) from dual union all
select 20, 22, 2, cast(null as varchar2(30)) from dual union all
select 20, 22, 1, cast(null as varchar2(30)) from dual union all
select 20, 22, 1, cast(null as varchar2(30)) from dual union all
select 20, 22, 2, cast(null as varchar2(30)) from dual union all
select 40, 41, 4, cast(null as varchar2(30)) from dual
9 rows affected

-- original code, expanded to run
DECLARE
   dynamic_sql_query varchar2(4000) := '
-- missing declare block...
DECLARE
   type t_subscr_record is record (clmn_x number, clmn_y number);
   type t_subscr_table is table of t_subscr_record;
   l_subscr_data t_subscr_table;
BEGIN
   
   SELECT clmn_x, clmn_y
     BULK COLLECT INTO l_subscr_data
     FROM table_x e, table_y c
    WHERE c.ref_id = :account_no AND e.account_no = c.account_no;


   FORALL indx IN 1 .. l_subscr_data.COUNT
      UPDATE table_z ciem                --this update will update multiple records for each account
         SET ciem.ext_id = ciem.sub_no || ROWID
       WHERE ciem.sub_no = l_subscr_data (indx).clmn_x
             AND ciem.subscr_no_resets =   l_subscr_data (indx).clmn_y
             AND ciem.status  IN (1,2);
             
END;
';

  l_account_data sys.odcinumberlist := new sys.odcinumberlist(101, 201, 301);
BEGIN
   FORALL indx IN 1 .. l_account_data.COUNT  --assume 10 as count
      SAVE EXCEPTIONS
      EXECUTE IMMEDIATE dynamic_sql_query USING l_account_data (indx);
   DBMS_OUTPUT.put_line ('Successful UPDATE of '|| TO_CHAR (SQL%ROWCOUNT) || ' record');
--   COMMIT;
END;
/
1 rows affected

dbms_output:
Successful UPDATE of 3 record

rollback
-- modified to use SQL%BULK_COUNT within dynamic SQL... but doesn't work properly with FORALL
DECLARE
   dynamic_sql_query varchar2(4000) := '
DECLARE
   type t_subscr_record is record (clmn_x number, clmn_y number);
   type t_subscr_table is table of t_subscr_record;
   l_subscr_data t_subscr_table;
BEGIN
   
   SELECT clmn_x, clmn_y
     BULK COLLECT INTO l_subscr_data
     FROM table_x e, table_y c
    WHERE c.ref_id = :account_no AND e.account_no = c.account_no;


   FORALL indx IN 1 .. l_subscr_data.COUNT
      UPDATE table_z ciem                --this update will update multiple records for each account
         SET ciem.ext_id = ciem.sub_no || ROWID
       WHERE ciem.sub_no = l_subscr_data (indx).clmn_x
             AND ciem.subscr_no_resets =   l_subscr_data (indx).clmn_y
             AND ciem.status  IN (1,2);

   FOR indx IN 1 .. l_subscr_data.COUNT LOOP
      :total_count := :total_count + coalesce(SQL%BULK_ROWCOUNT(indx), 0);
   END LOOP;
END;
';

  l_account_data sys.odcinumberlist := new sys.odcinumberlist(101, 201, 301);
  l_total_count number := 0;
BEGIN
   FORALL indx IN 1 .. l_account_data.COUNT
      SAVE EXCEPTIONS
      EXECUTE IMMEDIATE dynamic_sql_query
         USING l_account_data (indx), in out l_total_count;
   -- in this scenario l_total_count reflects the count after the first indx,
   -- even though internally later dynamic calls set it correctly; which isn't helpful...
   DBMS_OUTPUT.put_line ('Successful UPDATE of '|| TO_CHAR (l_total_count) || ' record');
--   COMMIT;
END;
/
1 rows affected

dbms_output:
Successful UPDATE of 2 record

rollback
-- modified to use SQL%BULK_COUNT within dynamic SQL and FOR LOOP instead of FORALL
DECLARE
   dynamic_sql_query varchar2(4000) := '
-- missing declare block...
DECLARE
   type t_subscr_record is record (clmn_x number, clmn_y number);
   type t_subscr_table is table of t_subscr_record;
   l_subscr_data t_subscr_table;
BEGIN
   
   SELECT clmn_x, clmn_y
     BULK COLLECT INTO l_subscr_data
     FROM table_x e, table_y c
    WHERE c.ref_id = :account_no AND e.account_no = c.account_no;


   FORALL indx IN 1 .. l_subscr_data.COUNT
      UPDATE table_z ciem                --this update will update multiple records for each account
         SET ciem.ext_id = ciem.sub_no || ROWID
       WHERE ciem.sub_no = l_subscr_data (indx).clmn_x
             AND ciem.subscr_no_resets =   l_subscr_data (indx).clmn_y
             AND ciem.status  IN (1,2);

   FOR indx IN 1 .. l_subscr_data.COUNT LOOP
      :total_count := :total_count + coalesce(SQL%BULK_ROWCOUNT(indx), 0);
   END LOOP;
END;
';

   l_account_data sys.odcinumberlist := new sys.odcinumberlist(201, 101, 301);
   l_total_count number := 0;
BEGIN
   FOR indx IN 1 .. l_account_data.COUNT LOOP
      EXECUTE IMMEDIATE dynamic_sql_query
         USING l_account_data (indx), in out l_total_count;
   END LOOP;
   DBMS_OUTPUT.put_line ('Successful UPDATE of '|| TO_CHAR (l_total_count) || ' record');
--   COMMIT;
END;
/
1 rows affected

dbms_output:
Successful UPDATE of 8 record

select * from table_z


SUB_NO
SUBSCR_NO_RESETS
STATUS
EXT_ID


10
11
1
10AAAqDhAAMAAAC7TAAA

10
11
2
10AAAqDhAAMAAAC7TAAB

20
21
1
20AAAqDhAAMAAAC7TAAC

20
21
1
20AAAqDhAAMAAAC7TAAD

20
22
2
20AAAqDhAAMAAAC7TAAE

20
22
1
20AAAqDhAAMAAAC7TAAF

20
22
1
20AAAqDhAAMAAAC7TAAG

20
22
2
20AAAqDhAAMAAAC7TAAH

40
41
4
null
SUB_NO	SUBSCR_NO_RESETS	STATUS	EXT_ID
10	11	1	10AAAqDhAAMAAAC7TAAA
10	11	2	10AAAqDhAAMAAAC7TAAB
20	21	1	20AAAqDhAAMAAAC7TAAC
20	21	1	20AAAqDhAAMAAAC7TAAD
20	22	2	20AAAqDhAAMAAAC7TAAE
20	22	1	20AAAqDhAAMAAAC7TAAF
20	22	1	20AAAqDhAAMAAAC7TAAG
20	22	2	20AAAqDhAAMAAAC7TAAH
40	41	4	null