I developed a stored procedure that send notification emails. The stored procedure name is, "a_test_email". I created an explicit cursors named, "crs" and the cursor queries the user_objects to check if any objects are INVALID. I am having issues with how "a_test_email" stored procedure loops to send emails. The "a_test_email" stored procedure loop - sends an email for each row (i.e. record), instead of looping through the cursor "crs" to get all records that have an INVALID status and then sends one email the show all rows (i.e. records) together in one email. I would like to know how to correct the "a_test_email" stored procedure, so that I do not get 100,000 separate emails from my explicit cursors, "crs". Thank you. The sample is listed below....

F.Y.I. - I attempted to use LiveSQL, but it was confusing when navigating through it.

create or replace procedure a_test_email is
v_cnt int;
v_object_name varchar2;
v_object_type varchar2;
v_status varchar2;

cursor crs_total is
select count(*)
from user_objects
where status = 'INVALID';

cursor crs is
select object_name, object_type, status
from user_objects
where status = 'INVALID';

BEGIN
open crs_total;
fetch crs_total into v_cnt;

open crs;
LOOP
fetch crs into v_object_name, v_object_type, v_status;

email_format_test(p_to => 'test_email_1@testing.com',
p_cc => 'test_email_2@testing.com',
p_from => 'test_email_3@testing.com',
p_subject => 'INVALID OBJECTS '||v_cnt,
p_message => 'Oracle found '||v_object_name||' '||v_object_type||' as an '||v_status||' object in your database. Please troubleshoot issue. Thank you.',
p_smpt_host => '11.11.111.11');

EXIT WHEN crs%NOTFOUND;
END LOOP;

CLOSE crs_total;
CLOSE crs;
END;

--------------------------------------------------------
---Below is the Stored Procedure that send the emails---
--------------------------------------------------------
--------------------------------------------------------
create or replace procedure email_format_test(p_to => IN varchar2,
p_cc => IN varchar2,
p_from => IN varchar2,
p_subject => IN varchar2,
p_message => IN varchar2,
p_smpt_host => IN varchar2,
p_smpt_port => IN number default 25)
as
v_mail_conn utl_smtp.connection;
BEGIN
v_mail_conn := utl_smtp.open_connection(p_smtp_host, p_smtp_port);
utl_smtp.helo(v_mail_conn, p_smtp_host);
utl_smtp.mail(v_mail_conn, p_from);
utl_smtp.rcpt(v_mail_conn, p_to);

utl_smtp.open_data(v_mail_conn);

utl_smtp.write_data(v_mail_conn, 'Date: '||TO_CHAR(sysdate, 'DD-MON-YYYY HH12:MI:SS')||utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'To: '||p_to||utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'From: '||p_from||utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'Subject: '||p_subject||utl_tcp.crlf);
utl_smtp.write_data(v_mail_conn, 'Reply To: '||p_from||utl_tcp.crlf||utl_tcp.crlf);

utl_smtp.write_data(v_mail_conn, p_message ||utl_tcp.crlf||utl_tcp.crlf);
utl_smtp.close_data(v_mail_conn);

utl_smtp.quit(v_mail_conn);
END;

declare
  email_body varchar2(32767) := 'Oracle found ';
  messages   dbms_sql.varchar2_table;
  cursor crs is
    select v_object_name||' '||v_object_type||' with status '||v_status msg
    bulk   collect into messages
    from   user_objects
    where  status = 'INVALID';

begin

  open crs;
  
  loop
    fetch crs bulk collect into messages limit 100;
    exit when messages.count = 0;
    
    for i in messages.first .. messages.last loop
      email_body := email_body  || ', ' || messages(i);
    end loop;
  end loop;
  
  email_body := email_body || ' object in your database. Please troubleshoot issue. Thank you.';
  
  email_format_test(p_to => 'test_email_1@testing.com',
    p_cc => 'test_email_2@testing.com',
    p_from => 'test_email_3@testing.com',
    p_subject => 'INVALID OBJECTS '||v_cnt,
    p_message => email_body,
    p_smpt_host => '11.11.111.11'
  );

end;