UTL_SMTP PAckage problem

[vamsioracle]

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.connec tion()
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

[debasisdas]

It is practically impossible to predict what is wrong without knowing the code that you are working on.

[amitpatel66]

Please post the source code that you have used for mailing purpose. I suggest you to use the UTL_SMTP.CONNEC TION outside your loop. Inside your loop you should have UTL_SMTP.MAIL,U TL_SMTP.RCPT,UT L_SMTP.DATA. And after the LOOP ENDS, CLOSE the SMTP connection

[vamsioracle]

Utl_smtp code snippet

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.

[code=oracle]

elsif ((sysdate) - rec_pending.due _date) > 2 then

for rec_count in 1..4
loop
if rec_count = 1 then ---email to the supervisor
For rec_person in cur_email_addre ss(l_current_fo rward_to_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 1'||crlf||crlf| |
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||c rlf||
'However, you failed to process the application within the stipulated 48 hour period and have affected the efficiency of the system.'||crlf| |crlf||
'The Departmental Manager has been advised of this delay and will liaise with you in this regard.'||crlf| |crlf||
'To ensure the integrity of the system and the accuracy of the leave data, you are required to process the application '||crlf||crlf||
'via the system so that the employees leave balances are accurate';


elsif rec_count = 2 then ---email to employee who has submitted the application

For rec_person in cur_email_addre ss(l_creator_pe rson_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Your application for '||crlf||crlf||
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf|| crlf||
'submitted on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||c rlf||
'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf| |crlf||
'This has affected the efficiency of the system and your application has now been sent to your Departmental Manager for processing.'||c rlf||crlf||
'Kindly contact your Manager for further information.';

elsif rec_count = 3 then ---email to Departmental manager of the employee who has submitted the application

l_manager_id := TO_NUMBER(subst r(CBTT_AME_PKG. CBTT_AME_MANAGE R(l_transaction _id),11));

For rec_person in cur_email_addre ss(l_manager_id )
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

For rec_person in cur_email_addre ss(l_current_fo rward_to_id)
loop
l_supervisor_na me := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 3'||crlf||crlf| |
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY ')||crlf||crlf| |
'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||
'You intervention is required, kindly instruct Supervisor to immediately process the application.'|| crlf||crlf||
'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||crl f||
'The supervisor will be required to approve the application at a later date.';

elsif rec_count = 4 then ---email to HR department

For rec_hr_manager in cur_hr_person_i d
loop
For rec_person in cur_email_addre ss(rec_hr_manag er.person_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 4 '||crlf||crlf||
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||
'The Supervisor did not process the application within the stipulated 48 hour period and has affected the efficiency of the system.'||crlf| |crlf||
'The Departmental Manager has been advised of this delay and instructed to approve or reject the application.'|| crlf||crlf||
'Please ensure that the system has been updated with the relevant information.';

end if;

conn:= utl_smtp.open_c onnection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );

utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1> ' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_nam e||crlf;

FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;

mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

l_usraddr := '';
l_user_name := '';
addrcnt := 0;

end loop;

end if;
end loop;
end LOA_REM_SUP_MAI L;

End CBTT_LOA_REM_SU P_PKG;
[/code]


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

I have changed this code in the following way

[code=oracle]

elsif ((sysdate) - rec_pending.due _date) > 2 then

for rec_count in 1..4
loop
if rec_count = 1 then ---email to the supervisor
For rec_person in cur_email_addre ss(l_current_fo rward_to_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 1'||crlf||crlf| |
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||c rlf||
'However, you failed to process the application within the stipulated 48 hour period and have affected the efficiency of the system.'||crlf| |crlf||
'The Departmental Manager has been advised of this delay and will liaise with you in this regard.'||crlf| |crlf||
'To ensure the integrity of the system and the accuracy of the leave data, you are required to process the application '||crlf||crlf||
'via the system so that the employees leave balances are accurate';

conn:= utl_smtp.open_c onnection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );

utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1> ' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_nam e||crlf;

FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;

mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

l_usraddr := '';
l_user_name := '';
addrcnt := 0;


elsif rec_count = 2 then ---email to employee who has submitted the application

For rec_person in cur_email_addre ss(l_creator_pe rson_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Your application for '||crlf||crlf||
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf|| crlf||
'submitted on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||c rlf||
'was not processed by your Supervisor within the stipulated 48 hour period.'||crlf| |crlf||
'This has affected the efficiency of the system and your application has now been sent to your Departmental Manager for processing.'||c rlf||crlf||
'Kindly contact your Manager for further information.';

conn:= utl_smtp.open_c onnection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );

utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1> ' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_nam e||crlf;

FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;

mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

l_usraddr := '';
l_user_name := '';
addrcnt := 0;

elsif rec_count = 3 then ---email to Departmental manager of the employee who has submitted the application

l_manager_id := TO_NUMBER(subst r(CBTT_AME_PKG. CBTT_AME_MANAGE R(l_transaction _id),11));

For rec_person in cur_email_addre ss(l_manager_id )
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;

For rec_person in cur_email_addre ss(l_current_fo rward_to_id)
loop
l_supervisor_na me := rec_person.full _name;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 3'||crlf||crlf| |
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY ')||crlf||crlf| |
'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||
'You intervention is required, kindly instruct Supervisor to immediately process the application.'|| crlf||crlf||
'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||crl f||
'The supervisor will be required to approve the application at a later date.';

conn:= utl_smtp.open_c onnection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );

utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1> ' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_nam e||crlf;

FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;

mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

l_usraddr := '';
l_user_name := '';
addrcnt := 0;

elsif rec_count = 4 then ---email to HR department

For rec_hr_manager in cur_hr_person_i d
loop
For rec_person in cur_email_addre ss(rec_hr_manag er.person_id)
loop
l_usraddr := rec_person.emai l_address;
l_user_name := rec_person.full _name;
End loop;
End loop;

body := crlf||l_user_na me||crlf||crlf| |
'Mr./Ms./Mrs '||l_person_nam e||' submitted an application for 4 '||crlf||crlf||
'Leave Type : '|| substr(L_absenc e_name,6) ||crlf||
'From Date : '|| l_date_start ||crlf||
'To Date : '|| l_date_end ||crlf||
'Duration : '|| L_absence_days| |' day(s)'||crlf||
'on '|| To_char(rec_pen ding.transactio n_effective_dat e,'DD-MON-YYYY')||crlf||
'The Supervisor did not process the application within the stipulated 48 hour period and has affected the efficiency of the system.'||crlf| |crlf||
'The Departmental Manager has been advised of this delay and instructed to approve or reject the application.'|| crlf||crlf||
'Please ensure that the system has been updated with the relevant information.';

conn:= utl_smtp.open_c onnection( 'CBMS1',25);
utl_smtp.helo( conn, 'CBMS1');
utl_smtp.mail( conn, l_usraddr );

utl_smtp.rcpt( conn, l_usraddr );
addrcnt:= addrcnt + 1;
addrlist( addrcnt ):= 'To: ' || '<' || l_usraddr || '>' || crlf;

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || 'CBTT Oracle Applications Alert' || ' <applmgr@CBMS1> ' || crlf ||
'Subject: '||'REMINDER: Pending for Approval of Leave of Absence for '||l_person_nam e||crlf;

FOR i IN 1 .. addrcnt LOOP
mesg:= mesg || addrlist( i );
END LOOP;

mesg:= mesg || '' || crlf || BODY;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );

l_usraddr := '';
l_user_name := '';
addrcnt := 0;

end if;

end loop;

end if;
end loop;
end LOA_REM_SUP_MAI L;

End CBTT_LOA_REM_SU P_PKG
[/code]

This code is not at all sending any mail

Hope my code doesn't confuse and waiting for a solution

[amitpatel66]

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_addre ss(l_current_fo rward_to_id)
2. cur_email_addre ss(l_creator_pe rson_id)
3. cur_email_addre ss(l_current_fo rward_to_id)
4. cur_email_addre ss(rec_hr_manag er.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_addre ss

[Pilgrim333]

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.