| having trouble sending email using utl_smtp package (merged threads) [message #154317] |
Fri, 06 January 2006 15:47  |
geeklol
Messages: 59
Registered: March 2005
|
Member |
|
|
I have a stored procedure which is supposed to send out an email using the utl_smtp package.
I do get an email but it is not picking up the correct data that it is supposed to pick up. I know the problem area is the cursor c2 and that loop.
Here's my sp:
CREATE OR REPLACE PROCEDURE SEND_HRMAIL
IS
--Declaring a cursor which will get the email addresses
cursor c1 is select send_recpt from tbl_hrmail;
cursor c2 is select company from tbl_terminationemp;
c utl_smtp.connection;
rc integer;
v_company varchar2(30);
v_count integer;
v_view_count integer := 3150;
message VARCHAR2(2000);
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '10.1.1.40'; -- local database host
BEGIN
--Making a connection.
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
--Looping through the email addresses
for c1rec in c1 loop
utl_smtp.rcpt(c, c1rec.send_recpt);
end loop;
utl_smtp.data(c,'From: HR' || utl_tcp.crlf ||
'To: a,b,c' || utl_tcp.crlf ||
'Subject: ' || 'HR Report' ||
utl_tcp.crlf || 'Company Employee ID Location');
for c2rec in c2 loop
v_company := c2rec.company;
dbms_output.put_line(v_company);
utl_smtp.data(c, v_company);
end loop;--Closing the connection.
utl_smtp.quit(c);
END;
/
The error i get is:
SQL> exec send_hrmail;
PTA
BEGIN send_hrmail; END;
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 503 5.5.2 Need mail command.
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 271
ORA-06512: at "SYS.UTL_SMTP", line 247
ORA-06512: at "SYS.UTL_SMTP", line 258
ORA-06512: at "SYSADM.SEND_HRMAIL", line 30
ORA-06512: at line 1
Please help!
Thank you so much.
|
|
|
|
|
|
|
|
| Can someone please help me. Having trouble sending emails using UTL_SMTP package [message #154322 is a reply to message #154317] |
Fri, 06 January 2006 17:56  |
geeklol
Messages: 59
Registered: March 2005
|
Member |
|
|
Can someone please help me. I have a deadline in about 2 hours.
With my procedure, i am getting an email, but it only picks up ONE set of data. WHen the cursor c2 loops through the second time, is when i am getting the error.
Is it my cursor loop that i am having trouble with? please help.
CREATE OR REPLACE PROCEDURE SEND_HRMAIL
IS
--Declaring a cursor which will get the email addresses
cursor c1 is select send_recpt from tbl_hrmail;
cursor c2 is select company,emplid from tbl_terminationemp;
c utl_smtp.connection;
rc integer;
v_company varchar2(30);
v_emplid varchar2(30);
v_count integer;
v_view_count integer := 3150;
message VARCHAR2(2000);
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '10.1.1.40'; -- local database host
BEGIN
--Making a connection.
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
--Looping through the email addresses
for c1rec in c1 loop
utl_smtp.rcpt(c, c1rec.send_recpt);
end loop;
--utl_smtp.data(c,'From: HR' || utl_tcp.crlf ||
-- 'To: kparikh@decurion.com,cgarcia@decurion.com,cstruc@decurion.com' || utl_tcp.crlf ||
-- 'Subject: ' || 'HR Report' ||
-- utl_tcp.crlf || 'Company Employee ID Location');
for c2rec in c2 loop
v_company := '';
v_emplid := '';
v_company := c2rec.company;
v_emplid := c2rec.emplid;
dbms_output.put_line(v_company);
dbms_output.put_line(v_emplid);
utl_smtp.data(c, v_company||','|| v_emplid ||utl_tcp.crlf);
end loop;--Closing the connection.
utl_smtp.quit(c);
END;
/
SQL> exec send_hrmail;
PTA
102304
PTA
100410
BEGIN send_hrmail; END;
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 503 5.5.2 Need mail command.
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 271
ORA-06512: at "SYS.UTL_SMTP", line 247
ORA-06512: at "SYS.UTL_SMTP", line 258
ORA-06512: at "SYSADM.SEND_HRMAIL", line 35
ORA-06512: at line 1
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
