Solved

Send Mail thru Oracle 9i Database by UTL_SMTP, plsql.jar not found

Posted on 2003-11-15
11
6,091 Views
Last Modified: 2012-08-13
Hi,
Iam using oracle 9i release 2. I want to send mail thru oracle 9i database.
for that i got a procedure from net. Also they asked to follow three steps. the steps are
1. Execute the script as sys "$ORACLE_HOME\javavm\install\initjvm.sql"
2. Execute the loadjava classfile as
$ORACLE_HOME\plsql\jlib>loadjava -f -v -r -u sys/****  plsql.jar
3. Execute the script as sys "$ORACLE_HOME\rdbms\admin\initplsj.sql"

I sucessfully executed the first step, but for the second step iam not able to locate the plsql.jar file in the specified path.

So Please tell me if there is any other method to perform this task

Regards
Raja

the procedure i executed is given below
*******************************************
Declare

  SendorAddress  Varchar2(30)  := 'raja@sw.com';
  ReceiverAddress varchar2(30) := 'DBA@sw.com';
  EmailServer     varchar2(30) := 'mail.sw.com';
  Port number  := 25;
  conn UTL_SMTP.CONNECTION;
  crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
  mesg VARCHAR2( 4000 );
  mesg_body varchar2(4000);

BEGIN
  conn:= utl_smtp.open_connection( EmailServer, Port );
  utl_smtp.helo( conn, EmailServer );
  utl_smtp.mail( conn, SendorAddress);
  utl_smtp.rcpt( conn, ReceiverAddress );
  mesg:=
        'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
         'From:'||SendorAddress|| crlf ||
         'Subject: Mail Through ORACLE Database' || crlf ||
         'To: '||ReceiverAddress || crlf ||
         '' || crlf ||
         ' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||'It is very easy to configure  Tell me if you face any problems' ;

  utl_smtp.data( conn, mesg );
  utl_smtp.quit( conn );
END;
********************************************
0
Comment
Question by:mRaja
11 Comments
 
LVL 8

Expert Comment

by:Danielzt
ID: 9754675
This is good paper for how to set up and use UTL_SMTP.

http://www.geocities.com/samoracle/swHowtoSendMail.htm
0
 
LVL 2

Expert Comment

by:mszacik
ID: 9757817
plsql.jar is in your $ORACLE_HOME/plsql/jlib directory.
0
 

Expert Comment

by:a_kumar75
ID: 9757821
0
 
LVL 47

Expert Comment

by:schwertner
ID: 9762627
PROCEDURE SEND_EMAIL (sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'smtp.aaaaa.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);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
-- Handle the error
END;


PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);

-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);

BEGIN

vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);

-- Splits body (long) into varchar2s.

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP

DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);

END LOOP;

UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);


EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;

BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20100, vSqlErrm);

WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;

BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;

raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;
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:mRaja
ID: 9769022
Hi schwertner,

Thanx for the procedure u send. I executed your procedure. It shows the following error.
Can u help me how to solve the error.

regs
Raja

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.SEND_EMAIL", line 8
ORA-06512: at line 1
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 125 total points
ID: 9769341
I found following:

·      goal: How to handle 'ORA-20001: 421 Service not available' when calling
·      UTL_SMTP.open_connection
·      
·      fact: Oracle Server - Enterprise Edition
·      
·      fact: JDBC Thin driver
·      
·      fact: JDBC OCI driver
·      
·      fact: Package UTL_SMTP
·      
·      


fix:

Using following code sometimes causes errors
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at line 5
----------------------------------------------------------
set serverout on
declare
  c utl_smtp.connection;
  r utl_smtp.reply;
begin
  c := utl_smtp.open_connection('xsmtp.oraclecorp.com', 25);
  utl_smtp.helo(c,'x.com');
end;
/  
----------------------------------------------------------
If you want to catch the 421 error, e.g. because you want retry to connect if
the 421 occurs, you can use
  FUNCTION open_connection(host  IN  VARCHAR2,
                           port  IN  PLS_INTEGER DEFAULT 25,
                           c     OUT connection) RETURN reply;
like:
----------------------------------------------------------
set serverout on
declare
  c utl_smtp.connection;
  r utl_smtp.reply;
begin
  r := utl_smtp.open_connection('xsmtp.oraclecorp.com', 25, c);
  dbms_output.put_line(r.code||': '||r.text);
  if r.code = 421 then
    handle_the_421;
  end if;
  r := utl_smtp.helo(c,'x.com');
  dbms_output.put_line(r.code||': '||r.text);
end;
/

·      . fact: Oracle Server - Enterprise Edition 9
·      
·      fact: Oracle Server - Enterprise Edition 8.1
·      
·      fact: PL/SQL
·      
·      fact: UTL_SMTP
·      
·      fact: OPEN_CONNECTION
·      
·      symptom: UTL_SMTP.OPEN_CONNECTION fails
·      
·      symptom: ORA-20001: 421 Service not available
·      
·      cause: This error is comming from your SMTP mail server and is being
·      propogated into
·      the UTL_SMTP package.  Typically this error indicates that you are not using
·      the correct email server name in the UTL_SMTP.OPEN_CONNECTION() call.
·      
·      An easy way to determine your correct email host is to review your email client
·      configuration.  If you are using Netscape, for example, you would check under
·      'preferences' then 'mail servers' to find this information.
·      
·      One cause is that DNS could not resolve the mail server name
·      
·      In this case a 421 was returned when a connection attempt was made to an invalid
·      email server host.
·      
·      There are many SMTP return codes and possibly different reasons why you may
·      receive the 421 code mentioned above.  This article presents one solution.  
·      Oracle Support DOES NOT support the SMTP protocol or any email server
·      configuration issues. Please contact your email administrator for information
·      about the causes of the various SMTP return codes.
·      
·      


fix:

Verify that email server is accessable with the address give, if not correct
that.

Workaround
  Use the IP address of the mail server instead of the DNS name as an input
parameter of the UTL_SMTP.OPEN_CONNECTION PROCEDURE.

Reference:
Supplied PL/SQL Packages and Types Reference
  Chapter: UTL_SMTP

0
 

Author Comment

by:mRaja
ID: 9776519
Hi schwertner,

Thanx for ur continous support.

I executed the below codes. it sucessfully executed.
_____________________________________________
SQL> declare
  2    c utl_smtp.connection;
  3    r utl_smtp.reply;
  4  begin
  5    c := utl_smtp.open_connection('90.0.7.3', 25);
  6    utl_smtp.helo(c,'90.0.7.3');
  7  end;
  8  /

PL/SQL procedure successfully completed.
_______________________________________________________________________
  1  declare
  2    c utl_smtp.connection;
  3    r utl_smtp.reply;
  4  begin
  5    r := utl_smtp.open_connection('90.0.7.3', 25, c);
  6    dbms_output.put_line(r.code||': '||r.text);
  7    if r.code = 421 then
  8     dbms_output.put_line('Error Occured');
  9      --handle_the_421;
 10    end if;
 11    r := utl_smtp.helo(c,'90.0.7.3');
 12    dbms_output.put_line(r.code||': '||r.text);
 13* end;
SQL> /
220: <1069219706.1384@sworac2.SWORA2> [XMail 1.7 (Win32/Ix86) ESMTP Server]
service ready; Wed, 19 Nov 2003 10:58:26 +0530
250: test.com

PL/SQL procedure successfully completed.
_______________________________________________________________________

But on executing this procedure,
i got the following error..

  1  create or replace PROCEDURE SEND_EMAIL (sender IN VARCHAR2,
  2  recipient IN VARCHAR2,
  3  message IN VARCHAR2)
  4  IS
  5  mailhost VARCHAR2(30) := '90.0.7.3';
  6  mail_conn utl_smtp.connection;
  7  BEGIN
  8  mail_conn := utl_smtp.open_connection(mailhost, 25);
  9  utl_smtp.helo(mail_conn, mailhost);
 10  utl_smtp.mail(mail_conn, sender);
 11  utl_smtp.rcpt(mail_conn, recipient);
 12  utl_smtp.data(mail_conn, message);
 13  utl_smtp.quit(mail_conn);
 14  -- Handle the error
 15* END;
 16  /

Procedure created.

SQL> exec send_email('shankar@test.com','raja@test.com','test message');
BEGIN send_email('shankar@test.com','raja@test.com','test message'); END;

*
ERROR at line 1:
ORA-29278: SMTP transient error: 451 Requested action aborted: (-31) local
error in processing
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "SYS.SEND_EMAIL", line 10
ORA-06512: at line 1

where is the problem exists.. So pls tell me what to do next.

thanx & regs
Raja
0
 

Author Comment

by:mRaja
ID: 9776522
Hi schwertner,

Thanx for ur continous support.

I executed the below codes. it sucessfully executed.
_____________________________________________
SQL> declare
  2    c utl_smtp.connection;
  3    r utl_smtp.reply;
  4  begin
  5    c := utl_smtp.open_connection('90.0.7.3', 25);
  6    utl_smtp.helo(c,'90.0.7.3');
  7  end;
  8  /

PL/SQL procedure successfully completed.
_______________________________________________________________________
  1  declare
  2    c utl_smtp.connection;
  3    r utl_smtp.reply;
  4  begin
  5    r := utl_smtp.open_connection('90.0.7.3', 25, c);
  6    dbms_output.put_line(r.code||': '||r.text);
  7    if r.code = 421 then
  8     dbms_output.put_line('Error Occured');
  9      --handle_the_421;
 10    end if;
 11    r := utl_smtp.helo(c,'90.0.7.3');
 12    dbms_output.put_line(r.code||': '||r.text);
 13* end;
SQL> /
220: <1069219706.1384@sworac2.SWORA2> [XMail 1.7 (Win32/Ix86) ESMTP Server]
service ready; Wed, 19 Nov 2003 10:58:26 +0530
250: test.com

PL/SQL procedure successfully completed.
_______________________________________________________________________

But on executing this procedure,
i got the following error..

  1  create or replace PROCEDURE SEND_EMAIL (sender IN VARCHAR2,
  2  recipient IN VARCHAR2,
  3  message IN VARCHAR2)
  4  IS
  5  mailhost VARCHAR2(30) := '90.0.7.3';
  6  mail_conn utl_smtp.connection;
  7  BEGIN
  8  mail_conn := utl_smtp.open_connection(mailhost, 25);
  9  utl_smtp.helo(mail_conn, mailhost);
 10  utl_smtp.mail(mail_conn, sender);
 11  utl_smtp.rcpt(mail_conn, recipient);
 12  utl_smtp.data(mail_conn, message);
 13  utl_smtp.quit(mail_conn);
 14  -- Handle the error
 15* END;
 16  /

Procedure created.

SQL> exec send_email('shankar@test.com','raja@test.com','test message');
BEGIN send_email('shankar@test.com','raja@test.com','test message'); END;

*
ERROR at line 1:
ORA-29278: SMTP transient error: 451 Requested action aborted: (-31) local
error in processing
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "SYS.SEND_EMAIL", line 10
ORA-06512: at line 1

where is the problem exists.. So pls tell me what to do next.

thanx & regs
Raja
0
 

Accepted Solution

by:
aldogg254 earned 125 total points
ID: 9814577
i got a email procedure to work recently with my program... I had the same error message "421 Service not available" when the procedure couldn't find my SMTP server.  Later I tried it on two different servers and they both worked.. one was my ISP's SMTP server (Road Runner) and one was an SMTP server I installed on my computer called Mail Express Pro http://www.idataexpress.com/  ... I downloaded the shareware from CNet.

Anyways, I didn't need all these other procedures to check for errors and whatnot.. i just had 1 procedure similar to the one you started off with.

The only thing you need to make sure of is that you have the UTL_SMTP package in the user SYS as this page states: http://www.dbasupport.com/oracle/ora9i/oracleemail.shtml .. if it's not there, then that means you need to install them.. the page above says you need to do the initjvm.sql and initplsj.sql installations, but doesn't say anything about the plsql.jar install (which you had an error on before). I had these already installed when I was doing my procedure, so I have no trouble shooting experience with that.

I would get rid off all but your initial procedure, because you're only opening more possibilities of errors with all that extra code.  Try to use a different SMTP server or install one on your computer but DONT user 1st SMTP Server (available from d/l at CNet).. it wouldn't work with oracle.

Al
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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

743 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

14 Experts available now in Live!

Get 1:1 Help Now