| Sending mail in Oracle 8.0.5 [message #59903] |
Fri, 02 January 2004 05:27  |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
Hi,
This is work related question. I am given an assignment to write a package that will send an email out when the inventory falls below a certain value.
I know that UTL_SMTP is available 8i and above. Is there anyway I could replicate this functionality in 8.0.5
I want to avoid using External Procedure because its a little complicated and my DBA does not believe in working hard on Fridays
Please advice.
Scott
|
|
|
|
|
|
|
|
| Re: Sending mail in Oracle 8.0.5 [message #60278 is a reply to message #59905] |
Fri, 30 January 2004 00:18  |
Narendra Nath.C
Messages: 1
Registered: January 2004
|
Junior Member |
|
|
Hi ,
Just now I gone thru this and this is the code and tips to send mail from Oracle database using PL/SQL code.
First of all , let me convey my thanks to Sameer Wadhwa (Wadhwa_S@Hotmail.com).
SAMEER WADHWA
Wadhwa_S@Hotmail.com
SEND MAIL THROUGH DATABASE BY UTL_SMTP
This is a feature of Oracle 8i (8.1.6 release 2) . We can send mail thorough database by using UTL_SMTP package.
To make it work check the following configuration before proceeding
Ø java option must be installed in the database.
Ø TCPconnection class (plsql.jar) must be loaded.
Ø Init parameters
SHARED_POOL_SIZE > = 65M
JAVA_POOL_SIZE >= 50M
50M free in the SYSTEM tablespace
250M of rollback segment space
STEP 1.
Run initjvm.sql through svrmgrl or by connecting as SYS
ON NT AT SQL/SVRMGRL : $ORACLE_HOMEjavavminstallinitjvm.sql
ON UNIX AT SQL/SVRMGRL : $ORACLE_HOME/javavm/install/initjvm.sql
STEP 2 :
ON NT AT DOS PROMPT :
$ORACLE_HOMEplsqljlib>loadjava -f -v -r -u sys/**** plsql.jar
ON UNIX AT UNIX PROMPT :
$ORACLE_HOME/plsql/jlib>loadjava -f -v -r -u sys/**** plsql.jar
The following output will be expected after running above command.
initialization complete
loading : oracle/plsql/net/TCPConnection
creating : oracle/plsql/net/TCPConnection
loading : oracle/plsql/net/InternetAddress
creating : oracle/plsql/net/InternetAddress
loading : META-INF/MANIFEST.MF
creating : META-INF/MANIFEST.MF
resolver :
resolving: oracle/plsql/net/InternetAddress
resolving: oracle/plsql/net/TCPConnection
STEP 3:
Run initplsj.sql through SVRMGRL or SYS
ON NT AT SQL/SVRMGRL : @$ORACLE_HOMErdbmsadmininitplsj.sql
ON UNIX AT SQL/SVRMGRL : @$ORACLE_HOME/rdbms/admin/initplsj.sql
The following output is expected
Call completed.
Call completed.
Setup has completed . Let us see the code for sending mail.
Sample PL/SQL Code to send a mail through Oracle Database.
In the following plsql I am declaring variables for sender address which is the email address of the person who is sending email , Receiver address which is the email address of the recipient , Email server which is the address of your email exchange server, Port number which is dedicated for email services.
Also you have to declare a variable conn having a declaration type UTL_SMTP.CONNECTION which establish a connection with the SMTP server.
The other variable are
Ø UTL_SMTP.HELO which does handshake with SMTP server.
Ø UTL_SMTP.MAIL which contains the mail id of sender ( FROM).
Ø UTL_SMTP.RCPT which contains the mail id of the receiver.
Ø UTL_SMTP.DATA which grab the message buffer and send it.
Ø UTL_SMTP.QUIT which closes the connection.
Declare
SendorAddress Varchar2(30) := 'swadhwa@Test.com';
ReceiverAddress varchar2(30) := 'DBA@Test.com';
EmailServer varchar2(30) := 'mail.Test.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;
/
Now let us try to send a mail to multiple recipients . I created a table mailed and insert sender and receivers mail ids
SQL>desc mailid
Name Null? Type
----------------- -------- ------------
SEND_RECPT VARCHAR2(30)
MAILIDS VARCHAR2(50)
SQL> select * from mailid;
SEND_RECPT MAILIDS
------------------------------ ----------------------------
SENDER swadhwa@Test.com
RECPT UnixAdmin@Test.com
RECPT DBA@Test.com
RECPT MANAGER@Test.com
CREATE or replace PROCEDURE MAILFROMDB ( MESSAGE IN VARCHAR) AS
cursor c1 is select send_recpt,mailids from mailid;
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
AdminMailid varchar2(30);
BEGIN
/* Open connection */
conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
/* Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com' );
/* Loop for configure sender and recipient to UTL_SMTP */
for c1rec in c1 loop
if c1rec.send_recpt = 'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
else
utl_smtp.rcpt( conn,c1rec.mailids );
end if;
end loop;
/* making a message buffer */
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From: Oracle Database'|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: All the Recipients '|| crlf
|| 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'||crlf||message ;
/* Configure sending message */
utl_smtp.data( conn, mesg );
/* closing connection */
utl_smtp.quit( conn );
END;
/
SQL> execute mailfromdb('Send Feedback at Wadhwa_s@hotmail.com');
PL/SQL procedure successfully completed.
Now send mail to many people via To ,CC or BCC.
To configure this and to make it more simpler I am created two tables MAILID and MAILID_ORDER.
MAILID Table will contain the properties of send_recpt as TO,CC or BCC. There would be one property named as SENDER, responsible for sending mail.
MAILID_ORDER Table will contains the corresponding order number of Send_recpt property. This table will contains the distinct properties and order number. Order number is very important here as my procedure will configure recipient according to order number.
Configure Mailid and Mailid_order table as follows :-
ora816 SamSQL :> select * from mailid order by 1;
SEND_RECPT EMAIL_ADDRESS
------------------------------ -------------------------------------
BCC shastrid@Test.com
BCC Tony@Test.com
CC Rohit@Test.com
CC UNIXADMIN@Test.com
CC John@Test.com
SENDER DBAADMIN@Test.com
TO swadhwa@Test.com
TO Manager@Test.com
8 rows selected.
ora816 SamSQL :> select * from mailid_order;
SEND_RECPT ORDER_NO
-------------------- ----------
SENDER 0
TO 1
CC 2
BCC 3
CREATE or replace PROCEDURE MAILFROMDB ( MESSAGE IN VARCHAR) AS
cursor c1 is select mailid.send_recpt,email_address,order_no from mailid,mailid_order
where Mailid.SEND_RECPT = Mailid_order.SEND_RECPT order by order_no;
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
AdminMailid varchar2(30);
vToList varchar2(2000);
vCcList varchar2(2000);
vBccList varchar2(2000);
vSenderEmail varchar2(2000);
BEGIN
/* Open connection */
conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
/* Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com' );
/* Loop for configure sender and recipient to UTL_SMTP */
for c1rec in c1 loop
if c1rec.send_recpt = 'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
vSenderEmail := c1rec.mailids;
else
utl_smtp.rcpt( conn,c1rec.mailids );
end if;
/* Making a TO list */
if upper(c1rec.send_recpt) = 'TO' then
vTolist := vToList || c1rec.mailids||';';
end if;
/* Making a CC list */
if upper(c1rec.send_recpt) = 'CC' then
vCclist := vCcList || c1rec.mailids||';';
end if;
/* Making a BCC list */
if upper(c1rec.send_recpt) = 'BCC' then
vBcclist := vBccList || c1rec.mailids||';';
end if;
end loop;
/* making a message buffer */
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From: '||vSenderEmail|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: '||VToList|| crlf||
'Cc: '||VCcList||crlf ||
'Bcc: '||VBccList||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'||crlf||message ;
/* Configure sending message */
utl_smtp.data( conn, mesg );
/* closing connection */
utl_smtp.quit( conn );
END;
/
If the sender email is configured in Microsoft exchange server. Then you will also get a failure notification in case of invalid email addresses.
If you want to send mail with attachment , you can send it by using java procedure . There is no method available as yet for it by UTL_SMTP package. Refer to DOC 120994.1 on metalink.
--------------------------------------------------------------------------------
Conclusion :- You observed that how powerful this package is and how easily we can configure it.
Thanks for reading
Sameer Wadhwa
Wadhwa_S@Hotmail.com
www.geocities.com/samoracle
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
