| Sending Email from Database through PL/SQL [message #369713] |
Thu, 24 August 2000 09:10  |
Sivakumar SG
Messages: 14
Registered: August 2000
|
Junior Member |
|
|
Please help me in this particular situation.
My requirement is to sent email from Database through Pl/SQL block. OS is Solaris 5.6 and Oracle 8.1.6
I am using utl_tcp package options to send the mail. Program is getting compiled. But when i try to execute the program, it is going to exception handling and when i display the error message it says "ORA 29540 class oracle/net/plsql/TCPconnection not available.
How will i add classes to already installed oracle?
We have a SMTP mail server. Is TCP utility function is enough or should I use SMTP utilities. If so what should i do to execute the code. Already the TCP as well as SMTP pacakges are available in my database( I am able to view the packages and the functions inside).
If any one can help me to solve this problem, it will be great to me. My doubts are
Is TCP utility enough to sent mail.
If so what should I do to over come the ORA 29540 error ( Jave class not available error).
If not and if i have to use SMTP packages, can any one sent me a piece of code ( which is already functioning).
Please get me back as early as possible as the requirement is urgent .
Thanks in Advance
Siva
|
|
|
|
| Over 4 ways to send mail, and UTL_SMTP sample attached FYI [message #369729 is a reply to message #369713] |
Thu, 31 August 2000 14:51  |
jj wang
Messages: 3
Registered: August 2000
|
Junior Member |
|
|
Personally, I had tried several ways to send emails from within db.
1) Use the old-fashioned DBMS_PIPES, which requires some pro*C manipulation.
2) Use an EJB on MTS's IIOP protocol
3) Use Sun's Javamail API
4) Use UTL_SMTP
Your error "ORA 29540 class Oracle/net/plsql/TCPconnection not available" may point to a possibility that you had not fully inialized your JVM for using the mail function under UTL_SMTP. You would need to run
initjvm.sql under ORACLE_HOME/javavm/install and initplsj.sql located in ORACLE_HOME/rdbms/admin which is in fact a call of dbms_java.loadjava of a file called ('-resolve plsql/jlib/plsql.jar')
Make sure you fix the loadjava bug if Oracle's java environment could not be used. That will require a /bin/java outside of Oracle's tree. After loading the java (I used loadjava -r -f -v -s -g Public to solve permission and thin JDBC issue), compile the invalid java classes using the longname with quotes.
Please note UTL_SMTP has a shotcoming in comparision with Javamail API, namely, subject and body could not be formatted within UTL_SMTP, and you have to do it manually.
I had coded a complete block for your reference,a dn you could hardcode the sender and reciepient email address to point to "oracle@yourdomain.com" and "dba@yourdomain.com", respectively, thus leaving subject and body the only entries to insert into the procedure. It was fully tested and implemented by me via dbms_job on my databases.
++++++++++++++++++++++
create or replace PROCEDURE send2
(sender IN VARCHAR2,
recipient IN VARCHAR2,
subj IN VARCHAR2,
body IN VARCHAR2)
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2(4000);
mail_conn UTL_SMTP.CONNECTION;
cc_recipient VARCHAR2(50) default 'operations@yourdomain.com';
bcc_recipient VARCHAR2(50) default 'jjw_webmail@hotmail.com';
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.rcpt(mail_conn, cc_recipient);
utl_smtp.rcpt(mail_conn, bcc_recipient);
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || sender || crlf ||
'To: ' || recipient || crlf ||
'Cc: ' || cc_recipient || crlf ||
'Bcc: ' || bcc_recipient || crlf ||
'Subject: ' || subj || crlf;
mesg:= mesg || '' || crlf || body;
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
/
++++++++++++++++++
jj wang
jj wang
|
|
|
|
| Re: Over 4 ways to send mail, and UTL_SMTP sample attached FYI [message #369730 is a reply to message #369713] |
Fri, 01 September 2000 10:07 |
Sivakumar SG
Messages: 14
Registered: August 2000
|
Junior Member |
|
|
Thanks a lot wang for your information and extra effort to solve this problem.
I have tried with JVM loading. But it is eating a lot of resource and also my requirement is only to send email from the database. I am looking for a solution through dbms_pipes as it will use only less resources and more over sending mail is the only net based utility required for my application.
Also for Javamail API what additional software is required. My OS is Solaris 5.6
Wang, I would greatly appreciate, if you can send me your email id.
Thanks in Advance
Siva
|
|
|
|
|
|
| Re: Over 4 ways to send mail, and UTL_SMTP sample attached FYI [message #370147 is a reply to message #369713] |
Thu, 21 December 2000 18:31 |
sevans
Messages: 1
Registered: December 2000
|
Junior Member |
|
|
I get an error on the crlf line. i thought the variables had to have keyword DECLARE before them.
i used >start send2.sql and recieved the following error:
SQL> start send2
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE SEND2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PLS-00103: Encountered the symbol "CRLF" when expecting one of
the following:
; is with authid deterministic parallel_enable as
The symbol "is" was substituted for "CRLF" to continue.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
