How Fast is ULT_SMTP

[vamsioracle]

HI all

I ahve used utl_smtp package many a times. But how can we determine its speed.

I mean suppose if i call this from my code inside a package, how can we determine the speed it sends a mail.

--> Can i send mail to a gmail account
--> in utl_smtp.conect ion , what are two parameters. How can i use these params in my local system.


thanks
vamsi

[debasisdas]

1.speed of sending mail depends on size nand type of mail and size of attchemnt if any.
2.you can send mail to any mail account.
3.utl_smtp.conn ection does not accept any parameters but utl_smtp.open_c onnection does, the parametrs are mailhost and port number for SMTP (usually 25) This is the default value .

so you can work with only one parameter that is hostname.

[vamsioracle]

Thanks a lot.

Now i want to write a package, which i will be calling from a Oracle Form. The form has a text box that is used to enter the email address. As soon as user clicks on submit button. the mail should be triggered to the captured email id. How to do that?

Irrespective of the mail id,(assumin all the mail ids are from Gmail only) can we send the mail.

Vamsi

[amitpatel66]

Yes you can always do that.. In forms builder, on button click, wirte a code to send a mail and you will need to read the to address from a text box and store that in a variable.

[vamsioracle]

thanks a lot

but my question was different. I am done with the code at th forms side. But in my package ehile using utl_smtp package, how can i dynamically selct the host.

suppose the user enters a ymail, then will the host for utl_smtp change. How can we determine the host if the mail id is always changing

[debasisdas]

HOST NAME refers to the host of the sending mail server not the target mail server address.

[amitpatel66]

And the Host name used in utl_smtp does not change.it remains the same since it is the host name of source smtp server and not the destination server as mentioned by Debasis

[vamsioracle]

I got the followinr error when trying to send mail

[code=oracle]

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SM TP", line 17
ORA-06512: at "SYS.UTL_SM TP", line 96
ORA-06512: at "SYS.UTL_SM TP", line 138
ORA-06512: at "SCOTT.SEND_MAI L", line 11
ORA-06512: at line 1
[/code]

and this is my code:
[code=oracle]

create or replace procedure send_mail
is
conn utl_smtp.connec tion;
host varchar2(100) := '220.226.25.213 ';
send varchar2(200);
msg varchar2(100);

begin
Select ename into msg from emp where empno = 7369;
send :='mailtovamsid har.p@gmail.com ';
conn:= utl_smtp.open_c onnection( 'host');
utl_smtp.helo( conn, 'host');
utl_smtp.mail( conn,send);
utl_smtp.rcpt( conn, send);

utl_smtp.data( conn,msg);
utl_smtp.quit( conn );
end send_mail;
[/code]

[madankarmukta]

Hi,

Check whether the SMTP service is Up/running on the Oracle database server you are using.

Thanks!

[amitpatel66]

Try this code:

create or replace procedure send_mail
is
conn utl_smtp.connection;
host varchar2(100) := '220.226.25.213';
send varchar2(200);
msg varchar2(100);

begin
Select ename into msg from emp where empno = 7369;
send :='mailtovamsidhar.p@gmail.com';
conn:= utl_smtp.open_connection( host,25);
utl_smtp.helo( conn, host);
utl_smtp.mail( conn,send);
utl_smtp.rcpt( conn, send);

utl_smtp.data( conn,msg);
utl_smtp.quit( conn );
end send_mail;

[vamsioracle]

This was the error i got.

I made the IIS changes in control panel.


ERROR at line 1:
ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for mailtovamsidhar .p@gmail.com
ORA-06512: at "SYS.UTL_SM TP", line 17
ORA-06512: at "SYS.UTL_SM TP", line 98
ORA-06512: at "SYS.UTL_SM TP", line 240
ORA-06512: at "SCOTT.MAIL ", line 14
ORA-06512: at line 1

what is relaying the mail id


thanks
vamsi

[amitpatel66]

Your From Address should be related to your source SMTP server. For eg: if our server alias is acne.com then from addr will be 'abcd@acne.com'. So change your From address in the line UTL_SMTP.MAIL with the propoer from address

[vamsioracle]

The package is executing perfectly but still i get the same error. I checked the IIS in the control panel and everything is fine. Still i get the same error. Is there any other reason to get this other than IIS-->SMTP enabling

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SM TP", line 17
ORA-06512: at "SYS.UTL_SM TP", line 96
ORA-06512: at "SYS.UTL_SM TP", line 138
ORA-06512: at "SCOTT.SEND_MAI L", line 11
ORA-06512: at line 1

[amitpatel66]

The error itself is self explanatory. "Service Not Available". Do you have any other Unix Server or SMTP server that you can use for testing. Either your SMTP Server is Down or Incorrect.

[vamsioracle]

I am trying this on my laptop, running Windows Xp SP2. So is there a solution for this ?