how to send email from oracle 10 using (gmail, hotmail, yahoo)



hi friends

could you provide me with simple code to sending an email by this server

gmail or hotmail or yahoo

any help i really appreciated

thanks & regard  
junior_in_oracleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BawerCommented:
code may not be required but from DBCA menu, try to configure SMTP server and provide your email address....
junior_in_oracleAuthor Commented:


hi Bawer

thanks for reply, i made this but i get too much error could you help me plz

create or replace procedure send_email
is
    mail_conn utl_smtp.connection;
    mail_relay varchar2(64):='smtp.mail.yahoo.com';
    mail_sender varchar2(64):='my_email_in_yahoo@yahoo.com';
    mail_recipient varchar2(64):='my_email_in_hotmail@hotmail.com';
    msg_body varchar2(255);
    mail_port number:=25;
begin
  msg_body:='This is my first email';
  mail_conn:=utl_smtp.open_connection(mail_relay,mail_port);
  utl_smtp.helo(mail_conn,mail_relay);
  utl_smtp.mail(mail_conn,mail_sender);
  utl_smtp.rcpt(mail_conn,mail_recipient);
  utl_smtp.data(mail_conn,msg_body);
utl_smtp.quit(mail_conn);
end;
/

Procedure created.

SQL> execute send_email;
BEGIN send_email; END;

*
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 authentication required - for help go to
http://help.yahoo.com/help/us/mail/pop/pop-11.html
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 222
ORA-06512: at "SCOTT.SEND_EMAIL", line 13
ORA-06512: at line 1

plz i need your help
AbarajCommented:

Steps to enable Mailing from Database
1. sqlplus ‘/ as sysdba’
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
25 = Default SMTP Port
If instance had been started with spfile
eg: alter system set smtp_out_server = ‘172.25.90.165:25' scope=both;
If instance had been started with pfile
alter system set smtp_out_server = ‘172.25.90.165:25';
Also make below entry in your initSID.ora
smtp_out_server = ‘172.25.90.165:25'
Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘oraclepitstop@wordpress.com’, recipients => ‘oraclepitstop@wordpress.com’, subject => ‘Testing UTL_MAIL Option’, message => ‘blah blah blah’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to apps;
 
http://oraclepitstop.wordpress.com/2008/04/24/how-to-send-email-from-10g-oracle-database-utl_mail/
http://searchoracle.techtarget.com/tip/Send-email-from-Oracle-10g-Server 
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BawerCommented:
Abaraj is right .....
slightwv (䄆 Netminder) Commented:
UTL_MAIL is great for simple emails but for anything overly complex you will likely need to go back to UTL_SMTP.

In this case you are trying to use Yahoo as the mail relay.  UTL_MAIL doesn't allow for any authentication and you will have to use UTL_SMTP

Check out the authentication code in:
http://forums.oracle.com/forums/thread.jspa?messageID=896035
or
http://www.orafaq.com/forum/t/67221/0/
junior_in_oracleAuthor Commented:


hi Abaraj

thank you very much for reply and info, i want to provide you by this info may be it help you

to solve my problem, i use oracle database 10g in windows xp

1- it's clear
2- it's also clear but i have this path (@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlmail.sql)
3- it's also clear but i have this path (@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtmail.plb)
4- alter system
   set smtp_out_server = 'SMTP_SERVER_IP_ADDRESS:SMTP_PORT' scope=both

   System altered.


i follow until step 4 but i did not get this part

= = = = = = = = = = = = = = = = = = = = = = = = =
Also make below entry in your initSID.ora     =
smtp_out_server = ‘172.25.90.165:25'          =
= = = = = = = = = = = = = = = = = = = = = = = = =

my inisid file in this path (D:\oracle\product\10.2.0\admin\orcl\pfile)
but i did not find (smtp_out_server) to change it

my ip address is 192.168.1.100

could you show me how i can check my instance is started with pfile, or spfile

plz more help
slightwv (䄆 Netminder) Commented:
By default 10g uses SPFILE.  To verify this from a sqlplus prompt:
SQL> show parameter spfile

If this isn't set, you used a pfile.

That said, did you get a chance to read my post?  Is 172.25.90.165 an internal mail server for your company?  The original error was an authentication error.  UTL_MAIL will still fail.
junior_in_oracleAuthor Commented:
hi slightwv

thanks for info (show parameter spfile), i get a table after i wrote your command,

i don't have mail server, i just have my personal PC . it's possible to send an email if i don't have

mail server ??
slightwv (䄆 Netminder) Commented:
>> it's possible to send an email if i don't have  mail server ??

As a concept the answer is:  No.  Something has to know what to do with an email message.  That is the job of a mail server.  No mail server somewhere, no email sent.

In your initial example you were trying to use Yahoo as a mail relay and were getting an authentication error because you didn't provide any login info.  The link I provided talks about passing this information to a mail server using UTL_SMTP.

There are a few threads out there that talk about using Yahoo as a mail relay.  I suggest you Google around to find them.  I've never tried it so can't be much help there.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
junior_in_oracleAuthor Commented:
thanks slightwv i will study your link and i hope finally solve my problem
junior_in_oracleAuthor Commented:


slightwv

can i ask you later after i test your link
slightwv (䄆 Netminder) Commented:
You can ask but I might not be much help since I've never used authentication with SMTP.

To get other expert comments since you closed this out you might want to open a new question.
junior_in_oracleAuthor Commented:

is this mean no one will help me here any more
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.