By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,369 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,369 IT Pros & Developers. It's quick & easy.

UTL_SMTP PAckage problem

100+
P: 151
Hi all,

I have a problem with the ult_smtp package. Let me explain how the structure of my code is


procedure------------
begin
declarations of variables and cursors
........................
.....................
.................

for i in 1 ..3
loop

if i =1
capture the email of the HR in a variable
els if i =2
capture the email of supervisor in the same variable
else if=3
capture the email of the departmental manager in the same variable
else
capture the employee email
end if
utl_smtp.connection()
utl_smtp.rcpt()

-------- here wee call the utl _smtp package and send mail to all the four members
because it is inside the loop so the var captures all the four mail ids for each iteration of i
end loop


This was the code, but the problem is only the employee used to receive all the four mails.

Now i changed the code, such that the utl_smtp package is called inside the if condition so that all the persons recieve the mail and only once.

but none has received the mail at all.


what could be the problem. How should i make possible to do this such that all the four receive one mail each . will removing the for and if and writing a sequential code one after the other make this happen


thanks in advance
vamsi
Nov 24 '08 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
It is practically impossible to predict what is wrong without knowing the code that you are working on.
Nov 24 '08 #2

amitpatel66
Expert 100+
P: 2,367
Please post the source code that you have used for mailing purpose. I suggest you to use the UTL_SMTP.CONNECTION outside your loop. Inside your loop you should have UTL_SMTP.MAIL,UTL_SMTP.RCPT,UTL_SMTP.DATA. And after the LOOP ENDS, CLOSE the SMTP connection
Nov 24 '08 #3

100+
P: 151
Thanks a lot

I have enclosed my code, this is the old code . this code send four mails to the last person ie to the user id captured in last if condition.

Expand|Select|Wrap|Line Numbers
  1.  
  2. elsif ((sysdate) - rec_pending.due_date) > 2 then 
  3.  
  4.                             for rec_count in 1..4  
  5.                             loop
  6.                                 if rec_count = 1 then ---email to the supervisor
  7.                                     For rec_person in cur_email_address(l_current_forward_to_id)
  8.                                     loop
  9.                                         l_usraddr   := rec_person.email_address;
  10.                                         l_user_name := rec_person.full_name;
  11.                                     End loop;
  12.  
  13.                                     body := crlf||l_user_name||crlf||crlf||
  14.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 1'||crlf||crlf||
  15.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  16.                                               'From Date    : '|| l_date_start ||crlf||
  17.                                               'To Date      : '|| l_date_end ||crlf||
  18.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  19.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
  20.                                               'However, you failed to process the application within the stipulated 48 hour period and have affected the efficiency of the system.'||crlf||crlf||
  21.                                               'The Departmental Manager has been advised of this delay and will liaise with you in this regard.'||crlf||crlf||
  22.                                               'To ensure the integrity of the system and the accuracy of the leave data, you are required to process the application '||crlf||crlf||
  23.                                               'via the system so that the employees leave balances are accurate';
  24.  
  25.  
  26.                                 elsif rec_count = 2 then  ---email to employee who has submitted the application
  27.  
  28.                                     For rec_person in cur_email_address(l_creator_person_id)
  29.                                     loop
  30.                                         l_usraddr   := rec_person.email_address;
  31.                                         l_user_name := rec_person.full_name;
  32.                                     End loop;
  33.  
  34.                                     body := crlf||l_user_name||crlf||crlf||
  35.                                              'Your application for '||crlf||crlf||
  36.                                              'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  37.                                              'From Date    : '|| l_date_start ||crlf||
  38.                                              'To Date      : '|| l_date_end ||crlf||
  39.                                              'Duration     : '|| L_absence_days||' day(s)'||crlf||crlf||
  40.                                              'submitted on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
  41.                                              'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf||crlf||
  42.                                              'This has affected the efficiency of the system and your application has now been sent to your Departmental Manager for processing.'||crlf||crlf||
  43.                                              'Kindly contact your Manager for further information.';
  44.  
  45.                                 elsif rec_count = 3 then ---email to Departmental manager of the employee who has submitted the application
  46.  
  47.                                     l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
  48.  
  49.                                     For rec_person in cur_email_address(l_manager_id)
  50.                                     loop
  51.                                         l_usraddr   := rec_person.email_address;
  52.                                         l_user_name := rec_person.full_name;
  53.                                     End loop;
  54.  
  55.                                     For rec_person in cur_email_address(l_current_forward_to_id)
  56.                                     loop
  57.                                         l_supervisor_name := rec_person.full_name;
  58.                                     End loop;
  59.  
  60.                                     body := crlf||l_user_name||crlf||crlf||
  61.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 3'||crlf||crlf||
  62.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  63.                                               'From Date    : '|| l_date_start ||crlf||
  64.                                               'To Date      : '|| l_date_end ||crlf||
  65.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  66.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf||
  67.                                               'Mr./Ms./Mrs '||l_supervisor_name||' Supervisor did not process the application within the stipulated 48 hour period and this  has affected the efficiency of the system.'||crlf||crlf||
  68.                                               'You intervention is required, kindly instruct   Supervisor to immediately process the application.'||crlf||crlf||
  69.                                               'If the Supervisor is unable to electronically  process the application at this time,  kindly advise the employee whether you  have approved or rejected  the application and submit a copy of the email to the Assistant Manger, HR.'||crlf||crlf||
  70.                                               'The supervisor will be required to approve the application at a later date.';
  71.  
  72.                                 elsif rec_count = 4 then ---email to HR department
  73.  
  74.                                     For rec_hr_manager in cur_hr_person_id
  75.                                     loop
  76.                                         For rec_person in cur_email_address(rec_hr_manager.person_id)
  77.                                         loop
  78.                                             l_usraddr   := rec_person.email_address;
  79.                                             l_user_name := rec_person.full_name;
  80.                                         End loop;
  81.                                     End loop;
  82.  
  83.                                     body := crlf||l_user_name||crlf||crlf||
  84.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 4 '||crlf||crlf||
  85.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  86.                                               'From Date    : '|| l_date_start ||crlf||
  87.                                               'To Date      : '|| l_date_end ||crlf||
  88.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  89.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||
  90.                                               'The Supervisor did not process the application within the stipulated 48 hour period and has affected the efficiency of the system.'||crlf||crlf||
  91.                                               'The Departmental Manager has been advised of this delay and instructed to approve or reject the application.'||crlf||crlf||
  92.                                               'Please ensure that the system has been updated with the relevant information.';
  93.  
  94.                                 end if;
  95.  
  96.                                 conn:= utl_smtp.open_connection( 'CBMS1',25);
  97.                                 utl_smtp.helo( conn, 'CBMS1');
  98.                                 utl_smtp.mail( conn, l_usraddr );
  99.  
  100.                                 utl_smtp.rcpt( conn, l_usraddr );
  101.                                 addrcnt:= addrcnt + 1;
  102.                                 addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
  103.  
  104.                                 mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  105.                               'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
  106.                               'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
  107.  
  108.                                 FOR i IN 1 .. addrcnt LOOP
  109.                                      mesg:= mesg || addrlist( i );
  110.                                 END LOOP;        
  111.  
  112.                                 mesg:= mesg || '' || crlf || BODY;
  113.                                 utl_smtp.data( conn, mesg );
  114.                                 utl_smtp.quit( conn );
  115.  
  116.                                 l_usraddr   := '';
  117.                                 l_user_name := '';
  118.                                 addrcnt := 0;
  119.  
  120.                             end loop;
  121.  
  122.                         end if;
  123.                     end loop;
  124.     end LOA_REM_SUP_MAIL;
  125.  
  126. End CBTT_LOA_REM_SUP_PKG;
  127.  

=============================================

I have changed this code in the following way

Expand|Select|Wrap|Line Numbers
  1.  
  2. elsif ((sysdate) - rec_pending.due_date) > 2 then
  3.  
  4. for rec_count in 1..4
  5. loop
  6. if rec_count = 1 then ---email to the supervisor
  7.                                     For rec_person in cur_email_address(l_current_forward_to_id)
  8.                                     loop
  9.                                         l_usraddr   := rec_person.email_address;
  10.                                         l_user_name := rec_person.full_name;
  11.                                     End loop;
  12.  
  13.                                     body := crlf||l_user_name||crlf||crlf||
  14.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 1'||crlf||crlf||
  15.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  16.                                               'From Date    : '|| l_date_start ||crlf||
  17.                                               'To Date      : '|| l_date_end ||crlf||
  18.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  19.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
  20.                                               'However, you failed to process the application within the stipulated 48 hour period and have affected the efficiency of the system.'||crlf||crlf||
  21.                                               'The Departmental Manager has been advised of this delay and will liaise with you in this regard.'||crlf||crlf||
  22.                                               'To ensure the integrity of the system and the accuracy of the leave data, you are required to process the application '||crlf||crlf||
  23.                                               'via the system so that the employees leave balances are accurate';
  24.  
  25. conn:= utl_smtp.open_connection( 'CBMS1',25);
  26.                                 utl_smtp.helo( conn, 'CBMS1');
  27.                                 utl_smtp.mail( conn, l_usraddr );
  28.  
  29.                                 utl_smtp.rcpt( conn, l_usraddr );
  30.                                 addrcnt:= addrcnt + 1;
  31.                                 addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
  32.  
  33.                                 mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  34.                               'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
  35.                               'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
  36.  
  37.                                 FOR i IN 1 .. addrcnt LOOP
  38.                                      mesg:= mesg || addrlist( i );
  39.                                 END LOOP;
  40.  
  41.                                 mesg:= mesg || '' || crlf || BODY;
  42.                                 utl_smtp.data( conn, mesg );
  43.                                 utl_smtp.quit( conn );
  44.  
  45.                                 l_usraddr   := '';
  46.                                 l_user_name := '';
  47.                                 addrcnt := 0;
  48.  
  49.  
  50.                                 elsif rec_count = 2 then  ---email to employee who has submitted the application
  51.  
  52.                                     For rec_person in cur_email_address(l_creator_person_id)
  53.                                     loop
  54.                                         l_usraddr   := rec_person.email_address;
  55.                                         l_user_name := rec_person.full_name;
  56.                                     End loop;
  57.  
  58.                                     body := crlf||l_user_name||crlf||crlf||
  59.                                              'Your application for '||crlf||crlf||
  60.                                              'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  61.                                              'From Date    : '|| l_date_start ||crlf||
  62.                                              'To Date      : '|| l_date_end ||crlf||
  63.                                              'Duration     : '|| L_absence_days||' day(s)'||crlf||crlf||
  64.                                              'submitted on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||crlf||
  65.                                              'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf||crlf||
  66.                                              'This has affected the efficiency of the system and your application has now been sent to your Departmental Manager for processing.'||crlf||crlf||
  67.                                              'Kindly contact your Manager for further information.';
  68.  
  69. conn:= utl_smtp.open_connection( 'CBMS1',25);
  70.                                 utl_smtp.helo( conn, 'CBMS1');
  71.                                 utl_smtp.mail( conn, l_usraddr );
  72.  
  73.                                 utl_smtp.rcpt( conn, l_usraddr );
  74.                                 addrcnt:= addrcnt + 1;
  75.                                 addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
  76.  
  77.                                 mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  78.                               'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
  79.                               'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
  80.  
  81.                                 FOR i IN 1 .. addrcnt LOOP
  82.                                      mesg:= mesg || addrlist( i );
  83.                                 END LOOP;
  84.  
  85.                                 mesg:= mesg || '' || crlf || BODY;
  86.                                 utl_smtp.data( conn, mesg );
  87.                                 utl_smtp.quit( conn );
  88.  
  89.                                 l_usraddr   := '';
  90.                                 l_user_name := '';
  91.                                 addrcnt := 0;
  92.  
  93.                                 elsif rec_count = 3 then ---email to Departmental manager of the employee who has submitted the application
  94.  
  95.                                     l_manager_id := TO_NUMBER(substr(CBTT_AME_PKG.CBTT_AME_MANAGER(l_transaction_id),11));
  96.  
  97.                                     For rec_person in cur_email_address(l_manager_id)
  98.                                     loop
  99.                                         l_usraddr   := rec_person.email_address;
  100.                                         l_user_name := rec_person.full_name;
  101.                                     End loop;
  102.  
  103.                                     For rec_person in cur_email_address(l_current_forward_to_id)
  104.                                     loop
  105.                                         l_supervisor_name := rec_person.full_name;
  106.                                     End loop;
  107.  
  108.                                     body := crlf||l_user_name||crlf||crlf||
  109.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 3'||crlf||crlf||
  110.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  111.                                               'From Date    : '|| l_date_start ||crlf||
  112.                                               'To Date      : '|| l_date_end ||crlf||
  113.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  114.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY ')||crlf||crlf||
  115.                                               'Mr./Ms./Mrs '||l_supervisor_name||' Supervisor did not process the application within the stipulated 48 hour period and this  has affected the efficiency of the system.'||crlf||crlf||
  116.                                               'You intervention is required, kindly instruct   Supervisor to immediately process the application.'||crlf||crlf||
  117.                                               'If the Supervisor is unable to electronically  process the application at this time,  kindly advise the employee whether you  have approved or rejected  the application and submit a copy of the email to the Assistant Manger, HR.'||crlf||crlf||
  118.                                               'The supervisor will be required to approve the application at a later date.';
  119.  
  120. conn:= utl_smtp.open_connection( 'CBMS1',25);
  121.                                 utl_smtp.helo( conn, 'CBMS1');
  122.                                 utl_smtp.mail( conn, l_usraddr );
  123.  
  124.                                 utl_smtp.rcpt( conn, l_usraddr );
  125.                                 addrcnt:= addrcnt + 1;
  126.                                 addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
  127.  
  128.                                 mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  129.                               'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
  130.                               'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
  131.  
  132.                                 FOR i IN 1 .. addrcnt LOOP
  133.                                      mesg:= mesg || addrlist( i );
  134.                                 END LOOP;
  135.  
  136.                                 mesg:= mesg || '' || crlf || BODY;
  137.                                 utl_smtp.data( conn, mesg );
  138.                                 utl_smtp.quit( conn );
  139.  
  140.                                 l_usraddr   := '';
  141.                                 l_user_name := '';
  142.                                 addrcnt := 0;
  143.  
  144.                                 elsif rec_count = 4 then ---email to HR department
  145.  
  146.                                     For rec_hr_manager in cur_hr_person_id
  147.                                     loop
  148.                                         For rec_person in cur_email_address(rec_hr_manager.person_id)
  149.                                         loop
  150.                                             l_usraddr   := rec_person.email_address;
  151.                                             l_user_name := rec_person.full_name;
  152.                                         End loop;
  153.                                     End loop;
  154.  
  155.                                     body := crlf||l_user_name||crlf||crlf||
  156.                                               'Mr./Ms./Mrs '||l_person_name||' submitted an application for 4 '||crlf||crlf||
  157.                                               'Leave Type   : '|| substr(L_absence_name,6) ||crlf||
  158.                                               'From Date    : '|| l_date_start ||crlf||
  159.                                               'To Date      : '|| l_date_end ||crlf||
  160.                                               'Duration     : '|| L_absence_days||' day(s)'||crlf||
  161.                                               'on '|| To_char(rec_pending.transaction_effective_date,'DD-MON-YYYY')||crlf||
  162.                                               'The Supervisor did not process the application within the stipulated 48 hour period and has affected the efficiency of the system.'||crlf||crlf||
  163.                                               'The Departmental Manager has been advised of this delay and instructed to approve or reject the application.'||crlf||crlf||
  164.                                               'Please ensure that the system has been updated with the relevant information.';
  165.  
  166. conn:= utl_smtp.open_connection( 'CBMS1',25);
  167.                                 utl_smtp.helo( conn, 'CBMS1');
  168.                                 utl_smtp.mail( conn, l_usraddr );
  169.  
  170.                                 utl_smtp.rcpt( conn, l_usraddr );
  171.                                 addrcnt:= addrcnt + 1;
  172.                                 addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;
  173.  
  174.                                 mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
  175.                               'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1>' || crlf ||
  176.                               'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_name||crlf;
  177.  
  178.                                 FOR i IN 1 .. addrcnt LOOP
  179.                                      mesg:= mesg || addrlist( i );
  180.                                 END LOOP;
  181.  
  182.                                 mesg:= mesg || '' || crlf || BODY;
  183.                                 utl_smtp.data( conn, mesg );
  184.                                 utl_smtp.quit( conn );
  185.  
  186.                                 l_usraddr   := '';
  187.                                 l_user_name := '';
  188.                                 addrcnt := 0;
  189.  
  190.                                 end if;
  191.  
  192.                                                             end loop;
  193.  
  194.                         end if;
  195.                     end loop;
  196.     end LOA_REM_SUP_MAIL;
  197.  
  198. End CBTT_LOA_REM_SUP_PKG
  199.  
This code is not at all sending any mail

Hope my code doesn't confuse and waiting for a solution
Nov 26 '08 #4

amitpatel66
Expert 100+
P: 2,367
In your first code where it is sending 4 mails to the employee only, Can you print the output of the cursor by passing the following parameters:

1. cur_email_address(l_current_forward_to_id)
2. cur_email_address(l_creator_person_id)
3. cur_email_address(l_current_forward_to_id)
4. cur_email_address(rec_hr_manager.person_id)

From 1 and 3 you can see you are passing same value to the cursor parameter so when loop count = 1 and 3 then it will be sending mail to same peoples. Lets see the list of email addresses for the above four parameters when passed to the cursor cur_email_address
Nov 26 '08 #5

100+
P: 127
Hi,

Best way to test this, is by hardcoding your own emailaddress in the code for each mail that needs to be sent. Then run the program, if the mail reaches you, then there is something wrong in the table holding the emailaddresses. If the mail does not reach you, then either your code is not getting to the part where it needs to send the emails or something wrong with your smtp server.

The use of the smtp package has not changed in the old and new code, so that shouldn't be causing the problems if the old code used to work.

Pilgrim.
Nov 26 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.