Home » RDBMS Server » Server Administration » sending mail
sending mail [message #234403] Tue, 01 May 2007 08:15 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
Database is oracle 9i R2.
OS is sunos 5.8

unable to send mail, but this procedure is getting executed successfully. No mail is reaching!!!

Please find the code in attachment.

Thanks.
  • Attachment: email.txt
    (Size: 1.15KB, Downloaded 169 times)
Re: sending mail [message #234408 is a reply to message #234403] Tue, 01 May 2007 09:10 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
m not an expert with pl/sql scripts.stil this piece of code may help you.

CREATE OR REPLACE PROCEDURE SYSTEM.SEND_MAIL ( RECIPIENT IN VARCHAR2 DEFAULT 'yourid@domain.com',
MESSAGE IN VARCHAR2 )
AS
dbname VARCHAR2(16) DEFAULT '' ;
hostname VARCHAR2(30) DEFAULT '' ;
lv_sender VARCHAR2(60) DEFAULT '' ;
mailhost VARCHAR2(30) DEFAULT 'mailserver.domain.com' ;
header VARCHAR2(255) DEFAULT '' ;

mail_conn utl_smtp.connection ;
BEGIN

select utl_inaddr.get_host_name, sys_context('USERENV','DB_NAME')
into hostname, dbname
from dual ;

mailhost := hostname ;

lv_sender := 'Oracle_'||dbname||'_'||hostname||'_Instance' ;

header:='Subject: ALERT -- '||lv_sender||' '||CHR(10)||CHR(13)||'To: "'||recipient||'" <'||recipient||'> '||CHR(10)||CHR(13)||CHR(10)||CHR(13) ;

mail_conn := utl_smtp.open_connection(mailhost,25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, lv_sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, header||message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'ERROR IN EMAIL => '||SQLCODE||': '||SQLERRM);
END;
Re: sending mail [message #234415 is a reply to message #234403] Tue, 01 May 2007 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the packages described on AskTom: Sending e-mail! -- Oracle 8i specific response

Regards
Michel
Re: sending mail [message #234517 is a reply to message #234415] Tue, 01 May 2007 23:46 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks all for information provided.
Michel, i just created procedure then i ran the anonymous pl sql block, i got the error like ORA-29278.

I just replaced the domain with localhost

Thanks
Re: sending mail [message #234520 is a reply to message #234517] Tue, 01 May 2007 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-29278: SMTP transient error: %s
 *Cause:  A SMTP transient error occurred.
 *Action: Correct the error and retry the SMTP operation.

It's a STMP server error. What was the complete message? Do you a smtp server on your localhost? Check if you can send mail out of Oracle.

Regards
Michel

Re: sending mail [message #235062 is a reply to message #234520] Thu, 03 May 2007 23:43 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi Michel,

The Followin the full details about that error, I can send mail from UNIX (HP UNIX) box by using mailx command!!

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "INFOPUMP.SEND_MAIL", line 8
ORA-06512: at line 2


Thanks.

Re: sending mail [message #235064 is a reply to message #235062] Thu, 03 May 2007 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if configurations at the same ones, it seems Oracle can't reach the smtp server you gave it.

Regards
Michel
Re: sending mail [message #235761 is a reply to message #235064] Mon, 07 May 2007 23:06 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi Michel,
Is there any idea how to check the configuration?

Thanks.
Re: sending mail [message #235809 is a reply to message #235761] Tue, 08 May 2007 01:26 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Try to telnet mailserver.yourdomain.com 25

Check this thread

[Updated on: Tue, 08 May 2007 01:28]

Report message to a moderator

Previous Topic: Support costs
Next Topic: Data from a partition was somehow lost
Goto Forum:
  


Current Time: Fri Dec 09 09:47:19 CST 2016

Total time taken to generate the page: 0.17059 seconds