Re: email out of oracle

I seem to recall there being a bug in some versions of Oracle (8i??) where you had to manually load a class into the database before the utl_smtp would work. Do a quick search on Metalink and you should be able to find it.

Robert

• "Leonard, George" <george.leonard_at_farnell.com> wrote:
  > Hi there
  >
  > Trying to email out from Oracle.
  >
  > Utl_smtp is installed, executing procedure currently
  > as a DBA. This sun
  > Machine does send email notifications out via the
  > crontab to me so I know I
  > can send email via the exchange smtp server.
  >
  > Problem, Email packages execute, if I do a print I
  > see code
  > SQL> print
  >
  > NP
  > ----------
  > -29540
  >
  > Package executed with following command:
  >
  > var np number;
  > exec send_mail('leonardg_at_farnellcomponents.co.uk',
  > 'leonardg_at_farnellcomponents.co.uk', 'testmsg', :np);
  >
  > Below is the code of the send_mail package, can
  > anyone see the problem or
  > know what this error code means.
  >
  > Thx George
  >
  >
  > System
  >
  > Oracle 8.1.6.3 EE 32 Bit
  > Solaris 2.6
  >
  > --
  > -- Sending email out of Oracle using a stored
  > procedure.
  > --
  > Create or replace PROCEDURE
  > send_mail (sender IN VARCHAR2,
  > recipient IN VARCHAR2,
  > message IN VARCHAR2,
  > nStatus OUT NUMBER)
  > IS
  > mailhost VARCHAR2(30) := '90.1.1.100';
  > mail_conn utl_smtp.connection;
  >
  > BEGIN
  > nStatus := 0;
  > 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);
  > EXCEPTION
  > WHEN OTHERS THEN
  > nStatus := SQLCODE;
  > END send_mail;
  > /
  >
  > George
  > ________________________________________________
  > George Leonard
  > Oracle Database Administrator
  > Dimension Data (Pty) Ltd
  > (Reg. No. 1987/006597/07)
  > Cell: (+27) 82 655 2466
  > Tel: (+27 11) 575 0573
  > Fax: (+27 11) 576 0573
  > E-mail:george.leonard_at_za.didata.com
  > Web: http://www.didata.co.za
  >
  > You Have The Obligation to Inform One Honestly of
  > the risk, And As a Person
  > You Are Committed to Educate Yourself to the Total
  > Risk In Any Activity!
  > Once Informed & Totally Aware of the Risk, Every
  > Fool Has the Right to Kill
  > or Injure Themselves as They See Fit!
  >
  > -----Original Message-----
  > Sent: 07 January 2003 14:09 PM
  > To: Multiple recipients of list ORACLE-L
  >
  > I read it and love it. The only thing I was
  > wondering about is the fact,
  > that he uses tcl/tk, which I found most people don't
  > use anymore. Nice
  > surprise.
  > I wasn't quite sure wether oraora was looking for
  > books that gives more of a
  > general overview of books that delve into the depth
  > of unix internals.
  > Anyway, here is my favorite on Unix internals
  > (hence, the name of the book
  > ;):
  >
  > UNIX Internals: The New Frontiers by Uresh Vahalia
  >
  > Eventhough it was published in 1995, it gives you a
  > very good understanding
  > about how things really work and why they work the
  > way they do.
  >
  > Regards,
  > Stefan
  >
  > -----Ursprüngliche Nachricht-----
  > Von: Hately, Mike (NESL-IT)
  > [mailto:Mike.Hately_at_npowernorthern.com]
  > Gesendet: Montag, 6. Januar 2003 18:04
  > An: Multiple recipients of list ORACLE-L
  > Betreff: RE: Unix for oracle dba -- Suggest a book ?
  >
  >
  > If you want to understand how Oracle uses Unix get a
  > copy of James Morle's
  > "Scaling Oracle".
  > It's not a beginner's Unix book but it's accurate
  > and detailed.
  >
  > regards,
  > Mike Hately
  >
  > -----Original Message-----
  > Sent: 06 January 2003 15:59
  > To: Multiple recipients of list ORACLE-L
  >
  >
  >
  > Doesn't anyone read the manuals any more?!
  >
  > Oracle9i Installation Guide - Unix
  >
  > http://download-east.oracle.com/docs/html/A96167_01/toc.htm
  > Oracle9i Administrator's Reference - Unix
  >
  > http://download-east.oracle.com/docs/html/A97297_01/toc.htm
  >
  >
  >
  >
  >
  >
  > "James Damiano"
  >
  > <jadam_at_nhes.state To:
  > Multiple recipients
  > of list ORACLE-L <ORACLE-L_at_fatcity.com>
  >
  > .nh.us> cc:
  >
  > Sent by:
  > Subject: Re: Unix for oracle
  > dba -- Suggest a book ?
  >
  > root_at_fatcity.com
  >
  >
  >
  >
  >
  > 01/06/03 06:28 AM
  >
  > Please respond to
  >
  > ORACLE-L
  >
  >
  >
  >
  >
  >
  >
  >
  >
  > I've found a wonderful resource in the following
  > book:
  >
  > "Oracle DBA on Unix and Linux"
  > by Michael Wessler
  > http://www.samspublishing.com
  >
  > It covers some of the differences in features
  > between 8i and 9i as well as
  > handling the specifics of administrating Oracle
  > specifically on Unix
  > platforms. Highly recommended (at least by me).
  >
  > Jim Damiano
  >
  >
  > > Guys,
  >
  > > i know a bit of Linux.....and not completely a
  > newbie to Unix.
  >
  === message truncated ===

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Sheraton
  INET: robertgfreeman_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).