OraFAQ Forum: Forms » Mail through forms 6i

Home » Developer & Programmer » Forms » Mail through forms 6i (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mail through forms 6i [message #129147]

Fri, 22 July 2005 03:17

sQeKy
Messages: 24
Registered: July 2005

Junior Member

PROCEDURE SEND_EMAIL
(
smtp_server IN VARCHAR2,
port_no IN INTEGER,
sender_add IN VARCHAR2,
recipient_add IN VARCHAR2,
messaage IN VARCHAR2,
subject IN VARCHAR2
)
IS
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 ); -- For formating the lines
msg VARCHAR2(3000);
SMTP_conn utl_smtp.connection;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
BEGIN
SMTP_conn:= utl_smtp.open_connection( smtp_server, port_no );
utl_smtp.helo( SMTP_conn, smtp_server );
utl_smtp.mail( SMTP_conn, sender_add);
utl_smtp.rcpt( SMTP_conn, recipient_add );
s_var_msg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||sender_add|| crlf ||
'Subject: Mail From DAI team Bangalore' || crlf ||
'To: '|| recipient_add|| crlf || crlf || ' ' || messaage ||crlf||'.' ;
utl_smtp.data( SMTP_conn, msg );
utl_smtp.quit( SMTP_conn );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error sending mail');
END;
--------
i found this snippet some where on this forum...
i put it in my program units > procedure ...
and compiled
it gave the following error....
identifier UTL_SMTP.CONNECTION must be declared ....
this error comes in a lot of other snippets i have tried....
i think i am missing some file or package .. but i have no idea what ... i have to submit this mail assignment in the next two days ... please help me out here :s

Report message to a moderator

Re: Mail through forms 6i [message #129297 is a reply to message #129147]

Sat, 23 July 2005 08:20

oraclejo
Messages: 50
Registered: July 2005
Location: Ammar

Member

hello

if this is a forms procedure, please try to compile this procedure using sql*plus. i could be the case that your forms has no access to the SMTP package.

A workaround is to create a stored procedure or stored function on the database that contains your original code and then make a call to this procedure.

If you are using Oracle 10g database , there is a new MAIL package that is simpler to work with

Ammar Sajdi
www.e-ammar.com
Amman jordan

Report message to a moderator

Re: Mail through forms 6i [message #130566 is a reply to message #129147]

Mon, 01 August 2005 03:05

Ravi_prithiani
Messages: 14
Registered: July 2005
Location: Karachi

Junior Member

Hey i used this code it gives error saying:

Encountered the Symbol "End" when expecting one of the following:

begin function package pragma procedure form ..

Can anybody help solve this problem .. i need it urgently .. plz help .. i got to submit this within 2 days of time plz help ..

[Updated on: Mon, 01 August 2005 03:12]

Report message to a moderator

Re: Mail through forms 6i [message #130657 is a reply to message #130566]

Mon, 01 August 2005 18:46

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Your code

PROCEDURE SEND_EMAIL (
   smtp_server     IN   VARCHAR2,
   port_no         IN   INTEGER,
   sender_add      IN   VARCHAR2,
   recipient_add   IN   VARCHAR2,
   messaage        IN   VARCHAR2,
   subject         IN   VARCHAR2) IS
   crlf        VARCHAR2 (2)        := CHR (13) || CHR (10);   -- For formating the lines
   msg         VARCHAR2 (3000);
   SMTP_conn   utl_smtp.connection;
   crlf        VARCHAR2 (2)        := CHR (13) || CHR (10);
BEGIN
   SMTP_conn := utl_smtp.open_connection (smtp_server, port_no);
   utl_smtp.helo (SMTP_conn, smtp_server);
   utl_smtp.mail (SMTP_conn, sender_add);
   utl_smtp.rcpt (SMTP_conn, recipient_add);
   s_var_msg := 'Date: '
                || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
                || crlf
                || 'From:'
                || sender_add
                || crlf
                || 'Subject: Mail From DAI team Bangalore'
                || crlf
                || 'To: '
                || recipient_add
                || crlf
                || crlf
                || ' '
                || messaage
                || crlf
                || '.';
   utl_smtp.data (SMTP_conn, msg);
   utl_smtp.quit (SMTP_conn);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error sending mail');
END;

looks good to me. If this is in your form then go to the bottom of the Object Navigator and check the Built-In Packages and Database Objects. Look under SYS and Stored Program Units to see if you have the package available to you. I assume that you have connected to the right database!

David

Report message to a moderator

Re: Mail through forms 6i [message #130736 is a reply to message #130657]

Tue, 02 August 2005 03:39

Ravi_prithiani
Messages: 14
Registered: July 2005
Location: Karachi

Junior Member

david .. i checked i have the package available with sys and i have given grant and execute priviledges to a user but still i m getting same error can you help plz ..

Report message to a moderator

Re: Mail through forms 6i [message #130860 is a reply to message #129147]

Tue, 02 August 2005 23:20

hendy_wjj
Messages: 11
Registered: August 2005
Location: Jakarta

Junior Member

Hi Ravi....

I Try to help....
I make a send email from from61, using Java Virtual Machine From Command Prompt :

C:\>loadjava -user pricepemi/pricepemi -resolve d:\oracle\ora92\lib\xmlplsql.jar
C:\>loadjava -user pricepemi/pricepemi -synonym -resolve d:\oracle\ora92\lib\activation.jar
C:\>loadjava -user pricepemi/pricepemi -synonym -resolve d:\oracle\ora92\lib\mail.jar
C:\>loadjava -user pricepemi/pricepemi -synonym -resolve d:\oracle\ora92\lib\activation.jar

You should change "pricepemi/pricepemi" to username/password that should send an email. From SQL do :

connect price/pricepemi;
exec dbms_java.grant_permission('PRICEPEMI', 'SYS:java.util.PropertyPermission', '*', 'read, write');
exec dbms_java.grant_permission('PRICEPEMI', 'SYS:java.net.SocketPermission', 'mail.mail.co.id', 'connect, resolve');
exec dbms_java.grant_permission('PRICEPEMI', 'SYS:java.net.SocketPermission', '*', 'connect, resolve');

Change mail.mail.co.id to your email server name or IP.

Create this packages in your user name :

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SendMail" AS
import java.util.*;
import java.io.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SendMail {
// Sender, Recipient, CCRecipient, and BccRecipient are comma-
// separated lists of addresses;
// Body can span multiple CR/LF-separated lines;
// Attachments is a ///-separated list of file names;
public static int Send(String SMTPServer,
String Sender,
String Recipient,
String CcRecipient,
String BccRecipient,
String Subject,
String Body,
String ErrorMessage[],
String Attachments) {

// Error status;
int ErrorStatus = 0;

// create some properties and get the default Session;
Properties props = System.getProperties();
props.put("mail.smtp.host", SMTPServer);
Session session = Session.getDefaultInstance(props, null);

try {
// create a message;
MimeMessage msg = new MimeMessage(session);

// extracts the senders and adds them to the message;
// Sender is a comma-separated list of e-mail addresses as
// per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Sender);
msg.addFrom(TheAddresses);
}

// extract the recipients and assign them to the message;
// Recipient is a comma-separated list of e-mail addresses
// as per RFC822;
{
InternetAddress[] TheAddresses =
InternetAddress.parse(Recipient);
msg.addRecipients(Message.RecipientType.TO,
TheAddresses);
}

// extract the Cc-recipients and assign them to the
// message;
// CcRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != CcRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(CcRecipient);
msg.addRecipients(Message.RecipientType.CC,
TheAddresses);
}

// extract the Bcc-recipients and assign them to the
// message;
// BccRecipient is a comma-separated list of e-mail
// addresses as per RFC822;
if (null != BccRecipient) {
InternetAddress[] TheAddresses =
InternetAddress.parse(BccRecipient);
msg.addRecipients(Message.RecipientType.BCC,
TheAddresses);
}

// subject field;
msg.setSubject(Subject);

// create the Multipart to be added the parts to;
Multipart mp = new MimeMultipart();

// create and fill the first message part;
{
MimeBodyPart mbp = new MimeBodyPart();
mbp.setText(Body);

// attach the part to the multipart;
mp.addBodyPart(mbp);
}

// attach the files to the message;
if (null != Attachments) {
int StartIndex = 0, PosIndex = 0;
while (-1 != (PosIndex = Attachments.indexOf("///",
StartIndex))) {
// create and fill other message parts;
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex,
PosIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
PosIndex += 3;
StartIndex = PosIndex;
}
// last, or only, attachment file;
if (StartIndex < Attachments.length()) {
MimeBodyPart mbp = new MimeBodyPart();
FileDataSource fds =
new FileDataSource(Attachments.substring(StartIndex));
mbp.setDataHandler(new DataHandler(fds));
mbp.setFileName(fds.getName());
mp.addBodyPart(mbp);
}
}

// add the Multipart to the message;
msg.setContent(mp);

// set the Date: header;
msg.setSentDate(new Date());

// send the message;
Transport.send(msg);
} catch (MessagingException MsgException) {
ErrorMessage[0] = MsgException.toString();
Exception TheException = null;
if ((TheException = MsgException.getNextException()) !=
null)
ErrorMessage[0] = ErrorMessage[0] + "\n" +
TheException.toString();
ErrorStatus = 1;
}
return ErrorStatus;
}
}
/
show errors java source "SendMail"

CREATE OR REPLACE PACKAGE SendMailJPkg AS
-- EOL is used to separate text line in the message body;
EOL CONSTANT STRING(2) := CHR(13) || CHR(10);

TYPE ATTACHMENTS_LIST IS
TABLE OF VARCHAR2(4000);

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING DEFAULT '',
BccRecipient IN STRING DEFAULT '',
Subject IN STRING DEFAULT '',
Body IN STRING DEFAULT '',
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST DEFAULT NULL)
RETURN NUMBER;
END SendMailJPkg;
/
show errors

CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS

PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
AttachmentList OUT VARCHAR2) IS
AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
BEGIN
-- boolean short-circuit is used here;
IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
AttachmentList := Attachments(Attachments.FIRST);
-- scan the collection, skip first element since it has been
-- already processed;
-- accommodate for sparse collections;
FOR I IN Attachments.NEXT(Attachments.FIRST) ..
Attachments.LAST LOOP
AttachmentList := AttachmentList || AttachmentSeparator ||
Attachments(I);
END LOOP;
ELSE
AttachmentList := '';
END IF;
END ParseAttachment;

-- forward declaration;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER;

-- high-level interface with collections;
FUNCTION SendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
AttachmentList VARCHAR2(4000) := '';
AttachmentTypeList VARCHAR2(2000) := '';
BEGIN
ParseAttachment(Attachments,
AttachmentList);
RETURN JSendMail(SMTPServerName,
Sender,
Recipient,
CcRecipient,
BccRecipient,
Subject,
Body,
ErrorMessage,
AttachmentList);
END SendMail;

-- JSendMail's body is the java function SendMail.Send();
-- thus, no PL/SQL implementation is needed;
FUNCTION JSendMail(SMTPServerName IN STRING,
Sender IN STRING,
Recipient IN STRING,
CcRecipient IN STRING,
BccRecipient IN STRING,
Subject IN STRING,
Body IN STRING,
ErrorMessage OUT STRING,
Attachments IN STRING) RETURN NUMBER IS
LANGUAGE JAVA
NAME 'SendMail.Send(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String[],
java.lang.String) return int';
END SendMailJPkg;
/

To Try send email execute thi SQL connect as same username :

var ErrorMessage VARCHAR2(2000);
var ErrorStatus NUMBER;

-- enable SQL*PLUS output;
SET SERVEROUTPUT ON
-- redirect java output into SQL*PLUS buffer;
exec dbms_java.set_output(5000);
BEGIN
:ErrorStatus := SendMailJPkg.SendMail(
SMTPServerName => 'mail.mail.co.id',
Sender => 'senser@mail.co.id',
Recipient => 'recepient@mail.co.id',
CcRecipient => '',
BccRecipient => '',
Subject => 'This is the subject line: Test JavaMail',
Body => 'This is the body: Hello, this is a test' ||
'that spans 2 lines',
ErrorMessage => :ErrorMessage,
--Attachments => SendMailJPkg.ATTACHMENTS_LIST('d:\filname.ext')
Attachments => null
);
END;

This email to send an emil to recepient who have same mail server....I Still don't know hoe to send email from Oracle to another email address like to mail@yahoo.com, mail@hotmail.com, etc

May help you

Regards

Hendy

Report message to a moderator

Re: Mail through forms 6i [message #130861 is a reply to message #129147]

Tue, 02 August 2005 23:23

hendy_wjj
Messages: 11
Registered: August 2005
Location: Jakarta

Junior Member

Sorry...forget something

If you get an error when you load java on Command Prompt, you should try again until you don not get an error. You can load Java in server....cann't do in client

regards

Hendy

Report message to a moderator

Re: Mail through forms 6i [message #133014 is a reply to message #130657]

Wed, 17 August 2005 05:01

Ravi_prithiani
Messages: 14
Registered: July 2005
Location: Karachi

Junior Member

djmartin wrote on Tue, 02 August 2005 04:46

Your code

PROCEDURE SEND_EMAIL (
   smtp_server     IN   VARCHAR2,
   port_no         IN   INTEGER,
   sender_add      IN   VARCHAR2,
   recipient_add   IN   VARCHAR2,
   messaage        IN   VARCHAR2,
   subject         IN   VARCHAR2) IS
   crlf        VARCHAR2 (2)        := CHR (13) || CHR (10);   -- For formating the lines
   msg         VARCHAR2 (3000);
   SMTP_conn   utl_smtp.connection;
   crlf        VARCHAR2 (2)        := CHR (13) || CHR (10);
BEGIN
   SMTP_conn := utl_smtp.open_connection (smtp_server, port_no);
   utl_smtp.helo (SMTP_conn, smtp_server);
   utl_smtp.mail (SMTP_conn, sender_add);
   utl_smtp.rcpt (SMTP_conn, recipient_add);
   s_var_msg := 'Date: '
                || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
                || crlf
                || 'From:'
                || sender_add
                || crlf
                || 'Subject: Mail From DAI team Bangalore'
                || crlf
                || 'To: '
                || recipient_add
                || crlf
                || crlf
                || ' '
                || messaage
                || crlf
                || '.';
   utl_smtp.data (SMTP_conn, msg);
   utl_smtp.quit (SMTP_conn);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error sending mail');
END;

this worked fine but the problem is how can i send mail to more than one persons at a time ..?

Report message to a moderator

Re: Mail through forms 6i [message #133152 is a reply to message #133014]

Wed, 17 August 2005 19:32

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Oracle document b10802.pdf contains

Quote:

RCPT Function
Usage Notes
To send a message to multiple recipients, call this routine multiple times. Each invocation schedules delivery to a single e-mail address. The message transaction must have been begun by a prior call to MAIL, and the connection to the mail server must have been opened and initialized by prior calls to OPEN_CONNECTION and HELO or EHLO respectively.

Therefore you need to loop through a list of recipient email addresses to send multiple emails.

Try code similar to the following:

declare
/* all the parameters etc */
   i   number;
   j   number;
BEGIN
   SMTP_conn := utl_smtp.open_connection (smtp_server, port_no);
   utl_smtp.helo (SMTP_conn, smtp_server);
   utl_smtp.mail (SMTP_conn, sender_add);
   i := 1;
   j := instr (recipient_add, ',', i);

   if j > 0 then
      LOOP
         utl_smtp.rcpt (SMTP_conn, substr (recipient_add, i, j - 1) );
         i := i + 1;
         j := instr (recipient_add, ',', i);
         EXIT WHEN j = 0;
      END LOOP;
   else
      utl_smtp.rcpt (SMTP_conn, recipient_add); 
   end if;

   s_var_msg := 'The message';
   utl_smtp.data (SMTP_conn, msg);
   utl_smtp.quit (SMTP_conn);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error sending mail');
END;

I haven't tested it but it feels right.

David

Report message to a moderator

Re: Mail through forms 6i [message #133658 is a reply to message #133152]

Sat, 20 August 2005 20:26

samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong

Senior Member

Dear martin,

in your procedure i find the s_var_msg must be declare

what i have to do for that

samit gandhi

Report message to a moderator

Re: Mail through forms 6i [message #133708 is a reply to message #133658]

Sun, 21 August 2005 19:26

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

In my original posting 'msg' should be 's_var_msg'.

David

Report message to a moderator

Previous Topic:	Help
Next Topic:	Dynamic SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 12 14:58:25 CST 2025