I developed a stored procedure that send notification emails once so that I do not get 100,000 separate emails from my explicit cursors, "crs". The stored procedure name is, "a_test_email". I created an explicit cursors named, "crs". The cursor queries the user_objects to check if any objects are INVALID in the database. I am having issues with how "a_test_email" stored procedure loops to send emails. The "bulk collect" gets all records in the cursor "crs" then sends one email that show all rows (i.e. records) together in one email. However, I am continuously getting errors ORA-0065550 PLS-00306. I'm not sure what I'm doing wrong. Please help me to correct this issue with "a_test_email" stored procedure.
create or replace procedure a_test_email is
TYPE user_obj_tbl IS TABLE OF user_objects%ROWTYPE;
messages user_obj_tbl;
v_email_body varchar2(32767) := 'Oracle found ';
cursor crs is
select *
from user_objects
where status = 'INVALID';
Begin
open crs;
loop
fetch crs bulk collect into messages limit 100;
exit when messages.count = 0;
for indx in 1 .. messages.COUNT
loop
v_email_body := v_email_body||', '||messages(indx);
end loop;
end loop;
v_email_body := v_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 '||messages.COUNT,
p_message => v_email_body,
p_smpt_host => '11.11.111.11'
);
close crs;
end;
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;
I think problem is here.
v_email_body := v_email_body||', '||messages(indx);
Oracle can't find function to concatenate string with rowtype. messages(indx) is user_object%rowtype.
Try this.
v_email_body := v_email_body||', '||messages(indx).OBJECT_NAME;