RE: email out of oracle
Hi all Ok all indications are that I need the JAVA modules installed. Client does not like the idea of installing the java modules now on a live system, So will be doing this using Unix shell scripting. Thx for the help, will try all out on the sand box server for myself and then ensure all is ready for future machines. 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:[EMAIL PROTECTED] 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 21:20 PM To: Multiple recipients of list ORACLE-L My 2cts at a contribution This is what I did to get email working Your rollback names will vary 1.) Increase the shared_pool_size parameter to at lease 50MB and set the java_pool_sze parameter to 20MB. Bounce the database. # increase from 12MB to 50MB shared_pool_size = 52428800 # added for java utilities - 20MB java_pool_size = 20971520 2.) Set all small rollback segments offline. Make sure the only rollback segment online is your large rollback segment. Usually called rs_lrg. alter rollback segment rs_lrg online; alter rollback segment rs_01b offline; alter rollback segment rs_01a offline; alter rollback segment rs_02a offline; alter rollback segment rs_02b offline; alter rollback segment rs_03a offline; alter rollback segment rs_03b offline; alter rollback segment rs_04a offline; alter rollback segment rs_04b offline; 3.) Run these 2 scripts as user SYS Run initjvm.sql first. The initjvm.sql script is located in the ORACLE_HOME/javavm/install directory. C:\Oracle\Ora81\javavm\install\initjvm.sql This installs all the 1000+ java classes and takes about an hour to run Run initplsj.sql second. The initplsj.sql is located in ORACLE_HOME/rdbms/admin. 4.) Set your rollback segments back to previous status. alter rollback segment rs_lrg online; alter rollback segment rs_01b online; alter rollback segment rs_01a online; alter rollback segment rs_02a online; alter rollback segment rs_02b online; alter rollback segment rs_03a online; alter rollback segment rs_03b online; alter rollback segment rs_04a online; alter rollback segment rs_04b online; 5.) Create procedure to send email. CREATE OR REPLACE PROCEDURE send_email (sender IN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2) IS mailhost VARCHAR2(30) := 'mailserver.host.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); END; / -- exec send_mail('[EMAIL PROTECTED]','[EMAIL PROTECTED]','text message') ; 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('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk
email out of oracle
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('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .nh.us cc: Sent by: Subject: Re: Unix for oracle dba -- Suggest a book ? [EMAIL PROTECTED] 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. Can u suggest me a good/best book for Unix ? ..Unix for oracle DBA. i.e,tuning unix for good performance of oracle. any such book available ? kindly let me know guys. TIA. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Damiano INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
Re: email out of oracle
$ oerr ora 29540 29540, 0, class %s does not exist // *Cause: Java method execution failed to find a class with the indicated name. // *Action: Correct the name or add the missing Java class. Looks like you are missing some Java Class... Do you actually get the email it sends? Also if you get the SQLERRM you might get the name of the class Babu Leonard, George george.leonard@fTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] arnell.com cc: Sent by: Subject: email out of oracle [EMAIL PROTECTED] 01/07/03 11:13 AM Please respond to 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 NP -- -29540 Package executed with following command: var np number; exec send_mail('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]] Gesendet: Montag, 6. Januar
RE: email out of oracle
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 NP -- -29540 Package executed with following command: var np number; exec send_mail('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] Web: http://www.didata.co.za -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: email out of oracle
My 2cts at a contribution This is what I did to get email working Your rollback names will vary 1.) Increase the shared_pool_size parameter to at lease 50MB and set the java_pool_sze parameter to 20MB. Bounce the database. # increase from 12MB to 50MB shared_pool_size = 52428800 # added for java utilities - 20MB java_pool_size = 20971520 2.) Set all small rollback segments offline. Make sure the only rollback segment online is your large rollback segment. Usually called rs_lrg. alter rollback segment rs_lrg online; alter rollback segment rs_01b offline; alter rollback segment rs_01a offline; alter rollback segment rs_02a offline; alter rollback segment rs_02b offline; alter rollback segment rs_03a offline; alter rollback segment rs_03b offline; alter rollback segment rs_04a offline; alter rollback segment rs_04b offline; 3.) Run these 2 scripts as user SYS Run initjvm.sql first. The initjvm.sql script is located in the ORACLE_HOME/javavm/install directory. C:\Oracle\Ora81\javavm\install\initjvm.sql This installs all the 1000+ java classes and takes about an hour to run Run initplsj.sql second. The initplsj.sql is located in ORACLE_HOME/rdbms/admin. 4.) Set your rollback segments back to previous status. alter rollback segment rs_lrg online; alter rollback segment rs_01b online; alter rollback segment rs_01a online; alter rollback segment rs_02a online; alter rollback segment rs_02b online; alter rollback segment rs_03a online; alter rollback segment rs_03b online; alter rollback segment rs_04a online; alter rollback segment rs_04b online; 5.) Create procedure to send email. CREATE OR REPLACE PROCEDURE send_email (sender IN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2) IS mailhost VARCHAR2(30) := 'mailserver.host.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); END; / -- exec send_mail('[EMAIL PROTECTED]','[EMAIL PROTECTED]','text message') ; 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('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] 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
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 [EMAIL PROTECTED] 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('[EMAIL PROTECTED]', '[EMAIL PROTECTED]', '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 (senderIN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhostVARCHAR2(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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .nh.us cc: Sent by: Subject: Re: Unix for oracle dba -- Suggest a book ? [EMAIL PROTECTED] 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California