Oracle and sending email stored procedure help

[LadyDi02]

Hello experts,

This is my second post to the forum and need expert help.
I have the following Stored procedure that fetches data from a database and uses the UTL_SMTP package and sends mail. However, for some reason I want it to go through all recipents from the table and fire off an email to all the recipients in the table. You can see what I mean by the code below.

CREATE OR REPLACE PROCEDURE InfoCollection
IS
cursor get_data is
Select * from Table_X;
v_data get_data%ROWTYPE;
mailhost VARCHAR2(30) := 'mailhost';
mail_conn utl_smtp.connection;
message varchar2(2000);

BEGIN

open get_data;
Loop
fetch get_data into v_data;
END LOOP
close get_data;

message := 'Hello '||v_data.D_Name||',<BR>
<BR>
You can find your information at the following URL:<BR><BR>'||
'<a href='||v_data.r_link||'>'||v_data.r_link||'</a>';

mail_conn := utl_smtp.open_connection(mailhost, 25);
UTL_SMTP.HELO(mail_conn, mailhost);
UTL_SMTP.MAIL(mail_conn, 'xxx@XXX.XXX');
UTL_SMTP.RCPT(mail_conn, v_data.d_email);
utl_smtp.rcpt(mail_conn, v_data.v_email);
utl_smtp.data(mail_conn, 'SUBJECT: Link to info'||utl_tcp.crlf||
'Content-Type: text/html;'||utl_tcp.crlf||message);

EXCEPTION

WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
UTL_SMTP.QUIT(mail_conn);
raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
END;

So, looking at the code it should send mail to d_email and v_email. It should then loop through the rest of the rows(data) in the table and do the same for all d_email and v_email fields. It is not doing this. I currently have 2 rows in the table with 4 email address. 2 for d_email(both different) and 2 for v-email(both different). It is only firing off to 1 email address. Any ideas. Using oracle 9.
I have removed the real mailhost and email address for security reasons. Thanks.

 

[Thargy]

Ladydi,

you appear to have no means of stepping through v_data, and sequentially reading each row into current variables.

Can you tell me where this takes place in code. I understand that you are opening the cursor and reading it into v_data.

Where do you step through v_data?

Regards

Tharg

Grinding away at things Oracular

 

[LadyDi02]

Hello,

thargtheslayer. Thanks for the reply. Well to be honest I am extremely new to oracle and trying my best to grasp the concepts. I thought the looping and the fetch of the cursor would handle this. Could you direct me to the line of code that is in question or help me with the modifying of the code to show the stepping through. Thanks again thargtheslayer.

 

[Thargy]

Ladydi,

Fair enough. You've definitely come to the right place to get Oracle info. Some of the guys who post answers are extremely experienced.

I suggest that your code would be much easier to create if you use a "cursor for loop" which is a particularly helpful construct in the PL/SQL language.

I've logged in at work, and I'm busy right now, so have a look on line for some info. As a taster, I've posted a cursor for loop of my own.

First, declare and define a cursor (this should live in a package somewhere ladydi).

  CURSOR table_names_cur RETURN USER_TABLES%ROWTYPE
   IS
        SELECT *
          FROM USER_TABLES U
         WHERE SUBSTR(table_name, 1 ,4) != 'AUD_' --Ignore audit tables
		   AND SUBSTR(table_name, 1 ,2) != 'A_' --Ignore admin tables
		   AND SUBSTR(table_name, 1 ,9) != 'MICROSOFT' --Ignore OLE DB stuff
		   AND table_name NOT LIKE ('%_MV') --Ignore materialized views
		   AND table_name NOT LIKE ('%$%') --Ignore materilaized view logs
   		   AND table_name != 'QUEST_TEMP_EXPLAIN' --Ignore TOAD's explain plan table
   		   AND table_name != 'PLAN_TABLE' --Ignore explain plan table
		   AND table_name NOT LIKE ('%HTTP%') --Ignore custom q tables
ORDER BY table_name;

The cursor for loop below, does some behind the scenes magic.

   PROCEDURE create_audit_tables
   IS
   BEGIN
      FOR table_names_rec IN table_names_cur
      LOOP
         create_audit_table (table_names_rec.table_name);
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         PLVexc.recngo;
   END create_audit_tables;

The bit which says "FOR table_names_rec " tells pl/sql to do open the cursor called table_names_cur, get the first record of it, and then repeatedly loop through the cursor, advancing one record each time, until the end. Note that I didn't have to declare "table_names_rec", it's done implicitly in the cursor for loop.

This makes the code in the loop simple, as it's now a one-liner.

Does this point you in roughly the right direction? Let me know, and if I get time today (I'm at work now, so can't help more) I'll check on your progress (if any).

Regards

Tharg

Grinding away at things Oracular

 

[LadyDi02]

Hello again Tharg,

I think it somewhat leads me in the right direction. After researching I think the only thing I was missing, and please correct me if I am wrong, was :

for v_data in get_data LOOP
----I have placed all my UTL calls within this loop.
END LOOP;

So this seems much better . However, since I don't know whether what I did was correct, but if it is correct, my code should be creating 4 emails (2 for first row - V_Email and D_email) and (2 for second row v_email and d_email) however it is only sending 3. Any ideas. I guess maybe you can start by telling me whether my code is correct so that it loops through a table and fires off an email to both v-email and d-email for every row. Thanks again Tharg.

 

[carp]

Ladydi -
Since you expect two emails to d_email and two for v_email (for a total of four), but you are only getting three, who is not getting their seconde email? Please provide this as well as your new code so that we can figure this out.

 

[Thargy]

Ladydi,

that's seems to be about right.
As Carp has said though, we need some more explicit info
to finally destroy this bug.

Regards

Tharg

Grinding away at things Oracular

 

[LadyDi02]

Hello and thanks again.
Well the problem was in our exchange server. It was just taking longer to send. It is now correctly sending out the appropriate emails. But I do have a question about emails. Does this package allow for external e-mail delivery? If I change the value in the table fields to an external email address I get a microsoft outlook reply saying it could not resolve address and my procedure does not run or stops. I need the procedure to loop through the list and continue to process all the email address regardless of whether they are invalid(or external if you experts tell me the package does not allow for external). It should not stop processing because it finds one bad email address. Thanks again and sorry for the delay but I was trying to troubleshoot the problem.

If I also could ask...please tell me if I should post another message/thread.........
How do I get my newly created table to auto increment the id. What I mean is I am manually inserting values and I hate always having to remember where I left off and what if I deleted a row of data the table should automatically adjust the id number. Any ideas? It is one table that holds all the values(email addresses, names and report id).Thanks again.

 

[carp]

Ladydi02 -
I have never had any problems with external email addresses (either in the From or To position), so this may be something unique to your environment. As far as its blowing up in mid-run, an exception handler would probably take care of this:

.
.
.
FOR i IN my_mailing_cursor LOOP
   BEGIN
      -- send an email code here
   EXCEPTION
      WHEN OTHERS THEN 
         -- send me an email telling me the email
         -- address that caused a problem as well 
         -- as the error message
   END;
END LOOP;
.
.
.

And yes, your next question would merit a separate thread - but the short answer is that you should define a sequence and then populate the ID number with the value generated by my_sequence.NEXTVAL. In order to simplify this, you could write a procedure for loading your data and just supply the input data as arguments.
Again, if you want to go into this in more detail, let's open another thread so that others can reference it.

 

[Thargy]

Ladydi,

pleased to hear you cracked the problem.

speak to you in another thread some time.

T

Grinding away at things Oracular

 

[LadyDi02]

Thanks again,
I'm sorry but I am stumped on your pseudocode. Could you possibly help me out. I tried my best with the shown code below,

EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
UTL_SMTP.MAIL(mail_conn, 'xxx.xxx.xxx);
raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
UTL_SMTP.QUIT(mail_conn);
END;
END LOOP;

BUT how do I
--send me an email telling me the email address that caused a problem as well as the error message. Thanks again.

If I run the code above it loops thorugh the database and sends out an email to all the addresses regardless of whether they are invalid and then sends the xxx.xxx.xxx email account an email saying that the email was Undeliverable. Not bad but the I do not want the email to process to the recipient(s) if the either email is invalid.
The raise application error doesn't even work(not sure where that will be shown).
Also, I guess I need to say that if 1 of the 2 emails(d_name and v_name) are incorrect do not process but send xxx.xxx.xxx and email saying the email address(v-email or d_email address) is invalid. Plus if both are invalid the same should happen. Could someone help me out again. Thanks.