Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL mail problem
PL/SQL mail problem [message #217272] Thu, 01 February 2007 07:02 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
CREATE OR REPLACE PROCEDURE SimpleTextMessage IS
mailHOST VARCHAR2(64) := <SMTP ADDRESS>;
mailFROM VARCHAR2(64);
mailTO VARCHAR2(64);
mailCONN utl_smtp.connection;
mailDATE VARCHAR2(20);
vreply utl_smtp.reply;
vreplies utl_smtp.replies;
i number;

BEGIN
mailFROM := 'To.mail@mail.com';
mailTO := 'From.mail@mail.com';
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') INTO mailDATE FROM dual;

dbms_output.put_line('open_connection');
dbms_output.put_line('---------------');
vreply := utl_smtp.open_connection(mailHOST, 25, mailCONN);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );

dbms_output.put_line('help');
dbms_output.put_line('----');
vreplies := utl_smtp.help(mailCONN, 'HELP');
for i in 1..vreplies.count loop
dbms_output.put_line( 'code = ' || vreplies(i).code );
dbms_output.put_line( 'text = ' || vreplies(i).text );
end loop;

dbms_output.put_line('ehlo');
dbms_output.put_line('----');
vreplies := utl_smtp.ehlo(mailCONN, mailHOST);
for i in 1..vreplies.count loop
dbms_output.put_line( 'code = ' || vreplies(i).code );
dbms_output.put_line( 'text = ' || vreplies(i).text );
end loop;

--dbms_output.put_line('helo');
--dbms_output.put_line('----');
--vreply := utl_smtp.helo(mailCONN, mailHOST);
--dbms_output.put_line( 'code = ' || vreply.code );
--dbms_output.put_line( 'text = ' || vreply.text );

dbms_output.put_line('mail');
dbms_output.put_line('----');
vreply := utl_smtp.mail(mailCONN, mailFROM);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );

dbms_output.put_line('rcpt');
dbms_output.put_line('----');
vreply := utl_smtp.rcpt(mailCONN, mailTO);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );

dbms_output.put_line('open_data');
dbms_output.put_line('---------');
vreply := utl_smtp.open_data(mailCONN);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );

utl_smtp.write_data(mailCONN, 'Subject: '|| 'A subject' || chr(13));
utl_smtp.write_data(mailCONN, 'From: '||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, 'Date: '||mailDATE || chr(13));
utl_smtp.write_data(mailCONN, 'To: '||mailTO || chr(13));
utl_smtp.write_data(mailCONN, 'CC: '||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, 'BCC: '||mailFROM || chr(13));
utl_smtp.write_data(mailCONN, chr(13));
utl_smtp.write_data(mailCONN, 'Hello Friend.' || chr(13));
utl_smtp.write_data(mailCONN, 'Drop me an e-mail.' || chr(13));

dbms_output.put_line('close_data');
dbms_output.put_line('----------');
vreply := utl_smtp.close_data(mailCONN);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );

dbms_output.put_line('quit');
dbms_output.put_line('----');
vreply := utl_smtp.quit(mailCONN);
dbms_output.put_line( 'code = ' || vreply.code );
dbms_output.put_line( 'text = ' || vreply.text );
EXCEPTION
WHEN others THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');

END;


But no mail is going to .. receipent ....
I am using oracle 9i.. and using smtp server
SELECT banner FROM sys.all_registry_banners
gives the

Oracle9i Catalog Views Release 9.2.0.4.0 - Production
Oracle9i Packages and Types Release 9.2.0.4.0 - Production
Oracle Workspace Manager 9.2.0.1.0 - Production



...

can any one guide me in this case??

Re: PL/SQL mail problem [message #217402 is a reply to message #217272] Fri, 02 February 2007 01:12 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
You didn't tell whether your procedure returns an exception or it actually "sends" an email, but no email arrives.

Verify three things:
1.)
 mailHOST VARCHAR2(64) := <SMTP ADDRESS>;

This has to be a valid address of your smtp server and your Oracle server MUST have access to your smtp server.

2.)
vreply := utl_smtp.open_connection(mailHOST, 25, mailCONN);

Check if port 25 is OK.

3.)
 mailFROM := 'To.mail@mail.com';

This has to be a valid email. Sender has to be registered with the smtp server. If your smtp server couldn't recognize "from mailer" it just ignores the request (actually, it treats your Oracle as a spamer and writes an error log).

It really depends on what the purpose of sending mails out of the database is. If it is a part of some automated transaction (e.g. sending e-mails to your clients all around the world), you can consider utl.smtp.reply record. But if you just want to send yourself an email when e.g. a job fails, then I don't see the point in working with utl.smtp record. Instead of, you can do:
CREATE OR REPLACE PROCEDURE SimpleTextMessage
IS
  g_mail_connection   utl_smtp.connection;
  g_smtp_server       CONSTANT VARCHAR2(30) := '<smtp server name>';
  g_smtp_port         CONSTANT INTEGER := 25;
  g_mailer            CONSTANT VARCHAR2(30) := '<valid registered mail address on the above server>';
BEGIN
  g_mail_connection := utl_smtp.open_connection(g_smtp_server, g_smtp_port);
  utl_smtp.helo(g_mail_connection, g_smtp_server);
  utl_smtp.mail(g_mail_connection, g_mailer);
  -- VALID e-mail addresses to recipients
  utl_smtp.rcpt(g_mail_connection, 'a@a.com');
  utl_smtp.rcpt(g_mail_connection, 'b@a.com');
  utl_smtp.rcpt(g_mail_connection, 'c@a.com');
  
  utl_smtp.open_data (g_mail_connection);
  utl_smtp.write_data(g_mail_connection, 'MIME-version: 1.0' || utl_tcp.crlf);
  utl_smtp.write_data(g_mail_connection, 'Content-Type: text/plain; charset=iso-8859-2' ||utl_tcp.crlf);
  -- put whatever you want here, doesn't need to be valid
  utl_smtp.write_data(g_mail_connection, 'From:  President' || utl_tcp.crlf);
  utl_smtp.write_data(g_mail_connection, 'To: someone' || utl_tcp.crlf);
  utl_smtp.write_data(g_mail_connection, 'Subject: This is test ' || utl_tcp.crlf);
  utl_smtp.write_data(g_mail_connection, utl_tcp.crlf);
  
  -- write your message here
  utl_smtp.write_raw_data(g_mail_connection, utl_raw.cast_to_raw(CONVERT('Hello!' ,'EE8ISO8859P2')));
  utl_smtp.write_raw_data(g_mail_connection, UTL_RAW.cast_to_raw(CONVERT('Another hello!','EE8ISO8859P2')));
  --------------------------
    
  utl_smtp.write_data(g_mail_connection, utl_tcp.crlf);
  utl_smtp.close_data(g_mail_connection);
  utl_smtp.quit(g_mail_connection);
  
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Something not right');
END;
/
Re: PL/SQL mail problem [message #217481 is a reply to message #217402] Fri, 02 February 2007 08:31 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
Ok great .. it now working ...
Thanks for the great help ..

Now .. going one step ahead ..
I want to create table in this mail body ....and want to populate date in the mail body ..also want to give any other website link ...

how i can do that...??
pls help
Re: PL/SQL mail problem [message #217488 is a reply to message #217481] Fri, 02 February 2007 09:00 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
You can't... As simple as that. But ok, let me rephrase myself - you can - in DOS style using characters. Something like this can be done with some PL/SQL programming:
I-----------------------I------------------------I
I         DATE          I     DESCRIPTION        I
I-----------------------I------------------------I
I      1.2.2007         I      done this         I
I-----------------------I------------------------I
I      2.2.2007         I      done that         I
I-----------------------I------------------------I
I      3.2.2007         I      about to do       I
I-----------------------I------------------------I         

Smtp stands for "Simple Mail Transfer Protocol" and you have to take the word "simple" quite literally. No such message body formating can be done. Not even in Oracle 10g.

If you want to complicate things further, you'll have to use java mail API. Here's the link

http://www.akadia.com/services/java_mail_plsql.html

Cheers
Re: PL/SQL mail problem [message #217939 is a reply to message #217488] Tue, 06 February 2007 02:57 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
thnks for help.
I am able to make table in mail send from pl/sql ..
by just using HTML.


utl_smtp.write_data(g_mail_connection, 'MIME-version: 1.0' || utl_tcp.crlf);
utl_smtp.write_data(g_mail_connection, 'Content-Type: text/html; charset=windows-1252' ||utl_tcp.crlf);

l_string := '<html><head></head><body><table border="2" cellspacing="0" cellpadding="2" width ="70%"><tr><td> Name</td><td> Empid</td></tr><tr><td> XXXXXXXXXXX</td><td> 123</td></tr></table></body></html>';
utl_smtp.write_raw_data(g_mail_connection, UTL_RAW.cast_to_raw(CONVERT(l_string,'EE8ISO8859P2')));



Re: PL/SQL mail problem [message #217954 is a reply to message #217939] Tue, 06 February 2007 03:47 Go to previous message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
Nice trick... Smile Storing html inside VARCHAR2. Have to remember that.

Just one more thing.

Quote:
utl_smtp.write_data(g_mail_connection, 'Content-Type: text/plain; charset=iso-8859-2' ||utl_tcp.crlf);

and
Quote:
utl_smtp.write_raw_data(g_mail_connection, utl_raw.cast_to_raw(CONVERT('Hello!' ,'EE8ISO8859P2')));


You see, the 'charset=iso-8859-2' and CONVERT('Hello!' ,'EE8ISO8859P2') are things that go together hand in hand. Character set in my example is EE8ISO8859P2 (ISO 8859-2 East European, ISO Latin-2 or whatever the name can be). However, 'charset=windows-1252' and 'EE8ISO8859P2' are not the same (should be WE8ISO8859P1). Both character sets are for European languages. If you live in Europe, that's fine. If not, you should change your character set accordingly.

Cheers
Previous Topic: pls help me
Next Topic: How to track mutating table
Goto Forum:
  


Current Time: Thu Dec 08 22:32:40 CST 2016

Total time taken to generate the page: 0.04879 seconds