Home » SQL & PL/SQL » SQL & PL/SQL » I am unable to send mails through oracle 9i (using gmail account)
I am unable to send mails through oracle 9i (using gmail account) [message #221019] Fri, 23 February 2007 04:33 Go to next message
paparao03
Messages: 3
Registered: October 2006
Location: Hyderabad
Junior Member
Hi all,

I am succeeded to send mail (through my comapany mail server) using following pl/sql. But, i am unable to send mails using the same coding, when i connected to smtp.gmail.com.

i received the following error :

ERROR at line 1:
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first
y78sm463148pyg
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "VRAO.DEMO_MAIL", line 258
ORA-06512: at "VRAO.DEMO_MAIL", line 119
ORA-06512: at line 13


>> utl_smtp.command(conn,'STARTTLS');
later, I added the above command line into my package(demo_mail), when i am connected through smtp.gmail.com.

Then I received the following error message:

ERROR at line 1:
ORA-29278: SMTP transient error: 454 TLS not available due to temporary reason
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 158
ORA-06512: at "VRAO.DEMO_MAIL", line 242
ORA-06512: at "VRAO.DEMO_MAIL", line 118
ORA-06512: at line 13

Plese help me for the above.

What's wrong with me?

Thanks & regards
Ram paparao.





my demo_mail package body coded as
...
...
..
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
-- utl_smtp.command(conn,'STARTTLS');
utl_smtp.command(conn, 'AUTH LOGIN');
utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw('xxxxxxx')));
utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw('xxxxxxx')));
RETURN conn;
END;


My pl/Sql is :
DECLARE
conn utl_smtp.connection;
pieces bfile;
data RAW(2100);
chunks PLS_INTEGER;
pos PLS_INTEGER;
len PLS_INTEGER;
buf1 blob;
amt number;
ex number;
i number;
BEGIN
conn := demo_mail.begin_mail(
sender => 'Me <xxxxxx@gmail.com>',
recipients => 'Ram paparao <xxxx@xxx.com>',
subject => 'Attachment Test',
mime_type => demo_mail.MULTIPART_MIME_TYPE);
demo_mail.attach_text(
conn => conn,
data => '<h1>Hi! This is a test.</h1>',
mime_type => 'text/html');
DBMS_LOB.CREATETEMPORARY(buf1,true);
pieces:= BFILENAME('BFILE_MAIL_DIR','1z0-032 1.pdf');
ex := dbms_lob.fileexists(pieces);
if ex = 1 then
dbms_output.put_line('file exist..');
end if;
BEGIN
DBMS_LOB.fileOpen(pieces);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.Put_Line('File not found');
END;
amt := DBMS_LOB.getLength(pieces);
dbms_output.put_line('file size'||amt);
DBMS_LOB.loadFromFile(buf1, pieces, amt);
demo_mail.begin_attachment(conn,'application/pdf',TRUE,'1z0-032 1.pdf','base64');
i := 1;
len := DBMS_LOB.getLength(pieces);
WHILE (i < len) LOOP
IF(i + demo_mail.MAX_BASE64_LINE_WIDTH < len)THEN
UTL_SMTP.Write_Raw_Data (conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(buf1, demo_mail.MAX_BASE64_LINE_WIDTH, i)));
ELSE
UTL_SMTP.Write_Raw_Data (conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(buf1, (len - i)+1, i)));
END IF;
UTL_SMTP.Write_Data(conn, UTL_TCP.CRLF);
i := i + demo_mail.MAX_BASE64_LINE_WIDTH;
END LOOP;
DBMS_LOB.fileClose(pieces);
DBMS_LOB.FreeTemporary(buf1);
demo_mail.end_attachment(conn => conn);
demo_mail.attach_text(
conn => conn,
data => '<h1>This is a HTML report.</h1>',
mime_type => 'text/html',
inline => FALSE,
filename => 'report.htm',
last => TRUE);
demo_mail.end_mail( conn => conn );
END;

[Updated on: Fri, 23 February 2007 05:24]

Report message to a moderator

Re: I am unable to send mails through oracle 9i (using gmail account) [message #221035 is a reply to message #221019] Fri, 23 February 2007 05:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is an issue from the mailserver; better ask your networking-guy.
Re: I am unable to send mails through oracle 9i (using gmail account) [message #455879 is a reply to message #221019] Thu, 13 May 2010 04:37 Go to previous message
maxapp
Messages: 2
Registered: January 2008
Junior Member
The example you find over the internet is wrong.

The correct syntax is the following :

FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
-- utl_smtp.command(conn,'STARTTLS');
utl_smtp.command(conn, 'AUTH LOGIN');
utl_smtp.command(conn, UTL_RAW.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('xxxxxxx'))));
utl_smtp.command(conn, UTL_RAW.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('xxxxxxx'))));
RETURN conn;
END;

Bye

Previous Topic: GMT format
Next Topic: run procedure by select
Goto Forum:
  


Current Time: Sat Dec 10 01:14:25 CST 2016

Total time taken to generate the page: 0.23552 seconds