Sending an Email from a Procedure in Oracle 8.1.7

[jdmartin74]

I have been trying to achieve the above today and after reviewing many articles and posts in this forum (and a bit of head scratching) I have finally got it working.

I thought rather than reply to a few posts, I would post a new thread with my results.

The following link

http://www.geocities.com/samoracle/swHowtoSendMail.htm

served the best for getting the Java components installed. Note that you need to be logged into SQL+ as 'SYS' not 'SYSTEM' as I mis-read!

Follow steps 1, 2 and 3 and you should have the components installed.

I used his code as a strong basis for mine, but his had a few errors in. You can also email multiple recipients without the need for a DB table. My code is below. The code is far from comprehensive, but does work.

Hope this helps at least one person.

J.

CODE STARTS HERE:

CREATE OR REPLACE PROCEDURE EMAIL(
p_from_email IN VARCHAR2,
p_from_name IN VARCHAR2,
p_rcpt IN VARCHAR2,
p_subject IN VARCHAR2 := '',
p_message IN VARCHAR2 := '',
p_cc IN VARCHAR2 := '') IS

v_CRLF VARCHAR2(2) := chr(13)||chr(10); -- EOL CHARACTERS
mailhost VARCHAR2(35) := 'smtp.mydomain.com'; -- << CHANGE TO SMTP SERVER NAME
mail_connection utl_smtp.connection;

BEGIN
-- ESTABLISH CONNECTION AND PERFORM HANDSHAKING
mail_connection := utl_smtp.open_connection(mailhost,25);
utl_smtp.helo(mail_connection,mailhost);
utl_smtp.mail(mail_connection,p_from_email);
utl_smtp.rcpt(mail_connection,p_rcpt);

-- BUILD THE MAIL MESSAGE AND SEND IT OUT
utl_smtp.data(mail_connection,
'From: ' || p_from_name || ' <' || p_from_email || '>' || v_CRLF ||
'Subject: ' || p_subject || v_CRLF ||
'To: ' || p_rcpt || v_CRLF ||
'CC: ' || p_cc || v_CRLF ||
v_CRLF || p_message);
-- 'Subj:' ||p_subject||v_CRLF||v_CRLF||p_message||v_CRLF);

-- SEVER THE CONNECTION
utl_smtp.quit(mail_connection);

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'ERROR IN EMAIL => '||SQLCODE||':
'||SQLERRM);
END;
/

 

[lecorr]

Try this code, I use it in prod daily.

FUNCTION Send_a_mail( Recp       IN varchar2,
	                Subject    IN varchar2,
	                Text       IN varchar2,
	                Attch      IN varchar2,
	                dialog     IN number ) RETURN NUMBER
 IS
objOutlook OLE2.OBJ_TYPE;
objMail OLE2.OBJ_TYPE;
objArg OLE2.LIST_TYPE;
objAttach OLE2.OBJ_TYPE;

BEGIN
	-- Connection à Outlook
  objOutlook := OLE2.CREATE_OBJ('Outlook.Application');

	-- Crée le Mail
	objarg := OLE2.CREATE_ARGLIST;
	OLE2.ADD_ARG(objarg,0);
	objMail := OLE2.INVOKE_OBJ(objOutlook,'CreateItem',objarg);
	OLE2.DESTROY_ARGLIST(objarg);


	-- Attache le fichier joint
	objAttach := OLE2.GET_OBJ_PROPERTY(objmail, 'Attachments');
	objarg := OLE2.CREATE_ARGLIST; 
	OLE2.ADD_ARG(objarg, Attch ); -- filename
	OLE2.INVOKE(objattach, 'Add', objarg); 
	OLE2.DESTROY_ARGLIST(objarg);

	-- Mets les éléments texte
	OLE2.SET_PROPERTY(objmail,'To',Recp);
	OLE2.SET_PROPERTY(objmail,'Subject',Subject);
	OLE2.SET_PROPERTY(objmail,'Body',Text);

	-- Envoi
	IF dialog = 1 THEN
		OLE2.INVOKE(objmail, 'Display');
	ELSE
		OLE2.INVOKE(objmail, 'Send');
	END IF;

	-- Libère
	OLE2.RELEASE_OBJ(objmail);
	OLE2.RELEASE_OBJ(objOutlook);
	return 0;

END;

Christian

 

[sem]

Lecorr: I suppose the post was about sending email from Oracle database, not from Forms on Windows platform only.

Regards, Dima

 

[lecorr]

Dima,

True, but this is a good place to put it since, if someone search the first, it may be that he wants the second.

Regards.

Christian

 

[sem]

Ok, agree

Regards, Dima

 

[Spikmeister]

I have to say that we use utl_smtp to send out over 10000 e-mails a month from one of our systems and it works pretty well.