|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Send mail using plsql
Hi,
I want to send mail using plsql.I know that this can be possible by UTL_SMTP package.So I went thro some doc's & I tried this code : PROCEDURE send_test_message IS mailhost VARCHAR2(64) := 'mailhost.fictional-domain.com'; /*(my computer name)*/ sender VARCHAR2(64) := 'prasadsug@yahoo.com'; recipient VARCHAR2(64) := 'prasadsug@rediffmail.com'; mail_conn utl_smtp.connection; BEGIN mail_conn := utl_smtp.open_connection(mailhost, 25); utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, sender); utl_smtp.rcpt(mail_conn, recipient); -- If we had the message in a single string, we could collapse -- open_data(), write_data(), and close_data() into a single call to data(). utl_smtp.open_data(mail_conn); utl_smtp.write_data(mail_conn, 'This is a test message.' || chr(13)); utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13)); utl_smtp.close_data(mail_conn); utl_smtp.quit(mail_conn); EXCEPTION WHEN OTHERS THEN dbms_output_line('error'); END; I got 'ora-00900:INVALID SQL STATEMENT'. Can anyone please help me which line i've done the mistake .how to rectify that?. |
|
#2
|
||||
|
||||
|
Try the following sample code using UTL_SMTP to send mail.
Code:
create or replace procedure sendmail(sender varchar2,recipient varchar2,subject varchar2, text
varchar2)
IS
mailhost VARCHAR2(64) := '192.168.1.32';
--The name of the SMTP server host
port constant number(2):=25;
--The port number on which SMTP server is listening (usually 25).
timeout number :=180;
--The time in seconds that the UTL_SMTP package waits before giving up in a read or write
operation in this connection.
--In read operations, this package gives up if no data is available for reading immediately.
--In write operations, this package gives up if the output buffer is full and no data is to be
sent into the network without being blocked.
--Zero (0) indicates not to wait at all.
--NULL indicates to wait forever.
mail_conn utl_smtp.connection;
BEGIN
--dbms_output.put_line(UTL_SMTP.VRFY (mail_conn,recipient));
mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
--Helo performs initial handshaking with SMTP server after connecting
utl_smtp.helo(mail_conn, mailhost);
--Mail Initiates a mail transaction with the server
utl_smtp.mail(mail_conn, sender);
--Specifies the recipient of an e-mail message
utl_smtp.rcpt(mail_conn, recipient);
-- open_data(), write_data(), and close_data() into a single call to data().
--Sends the DATA command
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'From'||':'|| Sender || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'To'||':'|| recipient || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn,'Subject' ||':'|| subject || UTL_TCP.CRLF);
--Writes a portion of the e-mail message
utl_smtp.write_data(mail_conn, text);
--Closes the data session
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
--dbms_output.put_line('Your message has been sent...!');
EXCEPTION
WHEN UTL_SMTP.PERMANENT_ERROR THEN
BEGIN
utl_smtp.quit(mail_conn);
END;
RAISE_APPLICATION_ERROR(-20101,'This id has Permanent Error');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
BEGIN
utl_smtp.quit(mail_conn);
END;
RAISE_APPLICATION_ERROR(-20102,'SMTP transient error:');
WHEN UTL_SMTP.INVALID_OPERATION THEN
BEGIN
utl_smtp.quit(mail_conn);
END;
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation in Mail using UTL_SMTP.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20104,'Some other Error ...!');
end;
/
To execute the above procedure try the following code. Code:
oracle
exec sendmail('sender@sender.com','recipient@recipient.com','Hi','Test Mail');
|
|
#3
|
|||
|
|||
|
Thanks Debasis!!..
I want one more help... Actually I want to use Oracle SQL Analyzer which is the tool for automated tuning for sql statements & integrated with Oracle Enterprise Manager.But I don't know that from which menu I need to click to get into this tool in OEM.I'm using Oracle9i release 2. Can you Please help me in this?.. |
|
#4
|
|||
|
|||
|
error occured
*
ERROR at line 1: ORA-29278: SMTP transient error: 421 Service not available ORA-06512: at "SYS.UTL_SMTP", line 21 ORA-06512: at "SYS.UTL_SMTP", line 97 ORA-06512: at "SYS.UTL_SMTP", line 399 ORA-06512: at "SYSTEM.SENDMAIL", line 46 ORA-29278: SMTP transient error: 421 Service not available ORA-06512: at line 1 Can anyone please help me which line i've done the mistake .how to rectify that?.[/QUOTE] |
|
#5
|
||||
|
||||
|
Check your mail server
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) |
|
#6
|
||||
|
||||
|
This is a cool topic.
![]() |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Send mail using plsql |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|