Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

e-mail-sql

Posted on 2011-03-21
10
Medium Priority
?
724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
10 Comments
 
LVL 74

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 74

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 48

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 48

Accepted Solution

by:
schwertner earned 2000 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
 

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 74

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 74

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

721 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