Solved

e-mail-sql

Posted on 2011-03-21
10
663 Views
Last Modified: 2012-05-11

I have this procedure in c:\email.sql

CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
    msg_from     VARCHAR2 := 'sender@testing.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);      
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                    
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                        
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

I tried
sql>c"\email.sql

It is stucked and not working
0
Comment
Question by:nobleit
  • 4
  • 2
  • 2
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 35183106
you might need to sent EHLO instead of HELO
it's also possible your server is expecting TLS authentication

what happens if you telnet to your smtp server and port 25 and issue each of those commands?


what does "stucked and not working" mean exactly?
bad response?
no response?
slow response?
keyboard catches fire?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35183113
have you tried using utl_smtp  instead?  or, if your email messages are small enough, utl_mail?
0
 
LVL 47

Expert Comment

by:schwertner
ID: 35183549
Setup UTL_MAIL in Oracle 11g


Install UTL_MAIL:

# SQLPLUS SYS AS SYSDBA

SQL> @ C:\Oracle11g\product\11.1.0\db_1\RDBMS\ADMIN\utlmail.sql

SQL> @ C:\Oracle11g\product\11.1.0\db_1\RDBMS\ADMIN\prvtmail.plb

You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file.
However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN
which is guaranteed to be defined to perform appropriately.

ALTER SYSTEM SET smtp_out_server = 'donkey.icw.int' SCOPE=BOTH;


Ensure that you are connected as SYS using the SYSDBA privilege,
and then grant the following privileges to the Database Vault Owner account.

For example:

CONNECT SYS/AS SYSDBA
Enter password: password

GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner;
GRANT EXECUTE ON UTL_TCP TO dbvowner;
GRANT EXECUTE ON UTL_SMTP TO dbvowner;
GRANT EXECUTE ON UTL_MAIL TO dbvowner;
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO dbvowner;
The UTL_TCP, UTL_SMTP, UTL_MAIL, and DBMS_NETWORK_ACL_ADMIN PL/SQL packages
are used by the e-mail security alert that you will create.



Connect to SQL*Plus as the Oracle Database Owner (DV_OWNER) account.

For example:

CONNECT dbvowner
Enter password: password
Create the following procedure:

/***************************************************************
**
** The Procedure
**
****************************************************************/
CREATE OR REPLACE PROCEDURE email_alert AS
     msg varchar2(20000) := 'Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: ';
BEGIN
    msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
    UTL_MAIL.SEND (
    sender => 'Oracle@instance.orcl',
    recipients => 'josef.schwertner@some_company.com',
    subject => 'Table modification attempted outside maintenance!',
    message => msg);
EXCEPTION
WHEN others THEN
   dbms_output.put_line('  Error Encountered.');
END email_alert;
/


set     serveroutput on
execute email_alert;
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 35183596
Another package:

BUT it is important to find an accessible mail server like  'smtp.somecompany.int' in the company I used to work.
This mail server doesn't exist now a days, so this is not classified information.

 http://www.dba-oracle.com/t_utl_smtp_utility.htm

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := 'smtp.somecompany.int' ; -- local database MAIL host

BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
 utl_smtp.quit(c);
      raise;
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
 utl_smtp.quit(c);
      raise;
WHEN others THEN
dbms_output.put_line(' OTHERS Error Encountered.');
 utl_smtp.quit(c);
      raise;
END;
/

begin
  SEND_MAIL (  'josef.schwertner@somecompany.com',
                  'Subject: Testing',
                  'Howdy!');
end;
/
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nobleit
ID: 35195217
Can you please tell me how to run this procedure and what should I enter at prompt..
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35195271
what "this" are you referring to?

schwertner has posted alternates instead of trying to address your code, you can try those.  the utl_mail one doesn't take parameters, the other one does, both require modification to fit your scenario
but neither of us can tell you what your server, from or to addresses should be.  They are just examples, you'll need to fill in the rest.
If you need help, we can give advice but you'll have to give us some direction as to what you have tried and what happened with your attempt.


Same goes for your existing code,I'll be happy to help but you haven't stated what the problem is.  What did you do?  What happened?  

"stuck and not working" isn't very descriptive.  What happens exactly?
0
 

Author Comment

by:nobleit
ID: 35195481
the UTL_MAIL is successfully installed
SQL> @C:\app\Mathew\product\11.1.0\db_1\RDBMS\ADMIN\prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.

I am so sorry to ask this..
In this command
ALTER SYSTEM SET smtp_out_server = 'smtp.server.int' SCOPE=BOTH;
may I know 'smtp.server.int' in my case what could I add?

Could you please tell me to connect database vault account.
I searched select username from dba_users; from sys
it is not there..
you provided the information to give privileges to database vault user.
Could you please help me to create this.?


0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35195704
you need to put your own smtp server name there, we don't know what that server is

based on your question's code,  it looks like you are trying to use your local machine

you need to use one of your own accounts, the user above was just an example
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now