Oracle-L: RE: email out of oracle

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 07 Jan 2003 12:11:11 -0800
Message-ID: <F001.0052907A.20030107121111@fatcity.com>

George,

Did you resolve this yet? The error code indicates that you are missing a Java class. Did you load the ORACLE_HOME/rdbms/initplsj.sql file? this will load the PL/SQL Java classes needed to send mail.

hope this helps

PS. Uncomment your exception clause in your procedure so that you can see the text of the error message. it should give you a better idea of the java classes that are missing.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Tuesday, January 07, 2003 11:14 AM To: Multiple recipients of list ORACLE-L

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

-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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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). Received on Tue Jan 07 2003 - 14:11:11 CST