oracle-databaseplsqlbulk-collect

PL/SQL: Getting Error ORA-006550 PLS-00306 in stored procedure


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;

---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; 

Solution

  • 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;