OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » sending mail

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sending mail [message #234403]

Tue, 01 May 2007 08:15

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 1094 times)

Report message to a moderator

Re: sending mail [message #234408 is a reply to message #234403]

Tue, 01 May 2007 09:10

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;

Report message to a moderator

Re: sending mail [message #234415 is a reply to message #234403]

Tue, 01 May 2007 09:39

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at the packages described on AskTom: Sending e-mail! -- Oracle 8i specific response

Regards
Michel

Report message to a moderator

Re: sending mail [message #234517 is a reply to message #234415]

Tue, 01 May 2007 23:46

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

Report message to a moderator

Re: sending mail [message #234520 is a reply to message #234517]

Tue, 01 May 2007 23:59

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: sending mail [message #235062 is a reply to message #234520]

Thu, 03 May 2007 23:43

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.

Report message to a moderator