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  |
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 #455879 is a reply to message #221019] |
Thu, 13 May 2010 04:37  |
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
|
|
|
|
Goto Forum:
Current Time: Tue Aug 26 20:25:46 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
