Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old February 23rd, 2009, 05:21 AM
prasadsug prasadsug is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 24 prasadsug User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 36 m 9 sec
Reputation Power: 0
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?.

Reply With Quote
  #2  
Old March 5th, 2009, 08:15 AM
debasisdas's Avatar
debasisdas debasisdas is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2007
Location: Bangalore, India
Posts: 153 debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level)debasisdas User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 2 Days 23 h 29 m
Reputation Power: 115
Send a message via ICQ to debasisdas Send a message via AIM to debasisdas Send a message via MSN to debasisdas Send a message via Yahoo to debasisdas Send a message via Google Talk to debasisdas Send a message via Skype to debasisdas
Bebo Facebook MySpace Orkut
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');

Reply With Quote
  #3  
Old March 6th, 2009, 01:07 AM
prasadsug prasadsug is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Feb 2009
Posts: 24 prasadsug User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 36 m 9 sec
Reputation Power: 0
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?..

Reply With Quote
  #4  
Old June 24th, 2009, 03:13 AM
shri12345 shri12345 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2009
Posts: 1 shri12345 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 m 48 sec
Reputation Power: 0
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]

Reply With Quote
  #5  
Old June 25th, 2009, 05:42 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,393 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532
Check your mail server

Reply With Quote
  #6  
Old August 31st, 2009, 06:07 AM
pasanindeewara's Avatar
pasanindeewara pasanindeewara is offline
Pasan Indeewara
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2009
Location: Ice Age
Posts: 63 pasanindeewara User rank is Sergeant (500 - 2000 Reputation Level)pasanindeewara User rank is Sergeant (500 - 2000 Reputation Level)pasanindeewara User rank is Sergeant (500 - 2000 Reputation Level)pasanindeewara User rank is Sergeant (500 - 2000 Reputation Level)pasanindeewara User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 1 h 5 m 28 sec
Reputation Power: 11
Send a message via AIM to pasanindeewara Send a message via MSN to pasanindeewara Send a message via Yahoo to pasanindeewara Send a message via Skype to pasanindeewara
MySpace
Thumbs up Cool

This is a cool topic.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > Send mail using plsql


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump




 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 




© 2003-2009 by Developer Shed. All rights reserved. DS Cluster 4 Hosted by Hostway
For more Enterprise Application Development news, visit eWeek