Home » Developer & Programmer » Forms » design of birthday reminder/ Oracle to mail client :
| design of birthday reminder/ Oracle to mail client : [message #85192] |
Tue, 08 June 2004 03:48  |
bluebyte
Messages: 25
Registered: September 2003
|
Junior Member |
|
|
Hi!
The requirement is as follows :
consider a table which maintains 3 fields - empnumber of the employee,the birthdate of the employee and the email id of that employee.
the proposed system should automatically check the birthday of the employee with the current date (sysdate) and if the day and month match,then automatically a happy birthday mail should be sent to that employee on the mail id stored in the database.
that means......first the data from the corresponding table has to be fetched....it should then be fed as input to a mail client ....and then an already drafted version of the happy birthday mail would be appended by the name of the employee and be sent to the respective email id of the employee.
Cud anyone pls help me with the tools/interfaces to design this using Oracle 8i on Win NT ?
Thnx in anticipation,
blubyte
|
|
|
|
|
|
|
|
| Re: design of birthday reminder/ Oracle to mail client : [message #85220 is a reply to message #85203] |
Wed, 09 June 2004 23:20  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Following is a Procedure which Send the Message as well as Attachements.
At a time it can send upto three Attachments.
This program uses UTL_SMTP, there are other methods also avilable like making use of Outlook Express etc.
HTH
Regards
Himanshu
CREATE OR REPLACE PROCEDURE MAIL_FILE( SUBJECT IN VARCHAR2,
MESSAGE IN VARCHAR2,
MAX_SIZE IN NUMBER DEFAULT 9999999999,
FILENAME1 IN VARCHAR2 DEFAULT NULL,
FILENAME2 IN VARCHAR2 DEFAULT NULL,
FILENAME3 IN VARCHAR2 DEFAULT NULL,
RETURN_DESC OUT VARCHAR2) IS
L_SMTP_SERVER VARCHAR2(20); /** TO STORE THE IP ADDRESS OF THE SMTP SERVER **/
L_SMTP_SERVER_PORT NUMBER; /** TO STORE THE SMTP PORT OF THE SMTP SERVER **/
L_DIRECTORY_NAME VARCHAR2(200); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/
L_FILE_NAME VARCHAR2(100); /** TO STORE THE FILENAME **/
L_LINE VARCHAR2(1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
L_MESG VARCHAR2(32767); /** TO STORE THE MESSAGE **/
CONN UTL_SMTP.CONNECTION; /** SMTP CONNECTION VARIABLE **/
L_MSG_TO VARCHAR2(2000); /** TO STORE THE LIST OF RECIPEINTS **/
L_SENDER_NAME VARCHAR2(200); /** TO STORE THE NAME OF THE SENDER **/
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
FILE_ARRAY VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
I BINARY_INTEGER; /** ARRAY INDEX **/
L_FILE_HANDLE UTL_FILE.FILE_TYPE; /** FILE POINTER **/
L_SLASH_POS NUMBER; /** TO STORE THE POSITION OF IN THE FILE NAME **/
L_MESG_LEN NUMBER; /** TO STORE THE LENGHT OF THE MESSAGE **/
ABORT_PROGRAM EXCEPTION; /** USER DEFINED EXCEPTION **/
MESG_LENGTH_EXCEEDED BOOLEAN := FALSE; /** BOOLEAN VARIABLE TO TRAP IF THE MESSAGE LENGHT IS EXCEEDING **/
RETURN_DESC1 VARCHAR2(2000); /** VARIABLE TO STORE THE ERROR MESSAGE. TO BE RETURNED TO THE CALLING PROGRAM **/
/*** CURSOR TO SELECT THE RECIPEINTS AND MARK A COPY TO THE SENDER AS WELL ***/
CURSOR RECIPIENT_CUR IS
SELECT AM_PAR_VAL
FROM MISC
WHERE KEY1 = 'TICKET_EMAIL'
AND (KEY2 = 'RECIPIENT EMAIL'
OR KEY2 = 'SENDER EMAIL');
/***
** THIS PROCEDURE FETCHES THE VALUES FOR MISCELLANEOUS PARAMETERS
***/
PROCEDURE FETCH_MISC IS
BEGIN
RETURN_DESC1 := 'E: PARAMETER NOT MAINTAINED IN MISC FOR KEY1 = SMTP SERVER. ';
L_SMTP_SERVER := PACKAGE.PVAL('SMTP SERVER');
RETURN_DESC1 := 'PARAMETER NOT MAINTAINED IN MISC FOR KEY1 = SMTP PORT. ';
L_SMTP_SERVER_PORT := PACKAGE.PVAL('SMTP PORT');
RETURN_DESC1 := 'PARAMETER NOT MAINTAINED IN MISC FOR KEY1 = TICKET_EMAIL AND KEY2 =SENDER EMAIL. ';
L_SENDER_NAME := PACKAGE.PVAL('TICKET_EMAIL','SENDER EMAIL');
EXCEPTION
WHEN OTHERS THEN
RAISE ABORT_PROGRAM;
END FETCH_MISC;
/**** MAIN PROGRAM STARTS HERE ****/
BEGIN
/*** FETCHING MISCELLANEOUS PARAMETERS ***/
FETCH_MISC;
/*** ASSIGNING FILE NAMES TO ARRAY ***/
FILE_ARRAY(1) := FILENAME1;
FILE_ARRAY(2) := FILENAME2;
FILE_ARRAY(3) := FILENAME3;
RETURN_DESC1 := '10 - E: THERE WAS AN ERROR IN OPENING CONNECTION. ';
CONN:= UTL_SMTP.OPEN_CONNECTION( L_SMTP_SERVER, L_SMTP_SERVER_PORT ); /** OPEN CONNECTION ON THE SERVER **/
UTL_SMTP.HELO( CONN, L_SMTP_SERVER ); /** DO THE INITIAL HAND SHAKE **/
UTL_SMTP.MAIL( CONN, L_SENDER_NAME );
RETURN_DESC1 := '20 - E: THERE WAS AN ERROR IN CREATING RECEIPIENTS. ';
FOR L_RECIPIENT_CUR_REC IN RECIPIENT_CUR /** LOOP FOR MULTIPLE RECEIPEINTS **/
LOOP
L_MSG_TO := L_RECIPIENT_CUR_REC.AM_PAR_VAL;
UTL_SMTP.RCPT( CONN, L_MSG_TO );
END LOOP;
UTL_SMTP.OPEN_DATA ( CONN );
/*** GENERATE THE MIME HEADER ***/
RETURN_DESC1 := '30 - E: THERE WAS AN ERROR IN GENERATING MIME HEADER. ';
L_MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
'From: ' || L_SENDER_NAME || CRLF ||
'Subject: ' || SUBJECT || CRLF ||
'To: ' || L_MSG_TO || CRLF ||
'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || CRLF ||
'' || CRLF ||
'This is a Mime message, which your current mail reader may not' || CRLF ||
'understand. Parts of the message will appear as text. If the remainder' || CRLF ||
'appears as random characters in the message body, instead of as' || CRLF ||
'attachments, then you''ll have to extract these parts and decode them' || CRLF ||
'manually.' || CRLF ||
'' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="message.txt"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF ||
'' || CRLF ||
MESSAGE || CRLF || CRLF || CRLF ;
L_MESG_LEN := LENGTH(L_MESG);
IF L_MESG_LEN > MAX_SIZE THEN
MESG_LENGTH_EXCEEDED := TRUE;
END IF;
RETURN_DESC1 := '40 - E: THERE WAS AN ERROR IN WRITING MESSAGE TO CONNECTION. ';
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
/*** START ATTACHING THE FILES ***/
FOR I IN 1..3 LOOP
EXIT WHEN MESG_LENGTH_EXCEEDED;
IF FILE_ARRAY(I) IS NOT NULL THEN
BEGIN
L_SLASH_POS := INSTR(FILE_ARRAY(I), '', -1 );
IF L_SLASH_POS = 0 THEN
L_SLASH_POS := INSTR(FILE_ARRAY(I), '/', -1 );
END IF;
L_DIRECTORY_NAME := SUBSTR(FILE_ARRAY(I), 1, L_SLASH_POS - 1 );
L_FILE_NAME := SUBSTR(FILE_ARRAY(I), L_SLASH_POS + 1 );
RETURN_DESC1 := '50 - E: THERE WAS AN ERROR IN OPENING FILE. ';
L_FILE_HANDLE := UTL_FILE.FOPEN(L_DIRECTORY_NAME, L_FILE_NAME, 'R' );
L_MESG := CRLF || '--DMW.Boundary.605592468' || CRLF ||
'Content-Type: application/octet-stream; name="' || L_FILE_NAME || '"' || CRLF ||
'Content-Disposition: attachment; filename="' || L_FILE_NAME || '"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF || CRLF ;
L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
LOOP
RETURN_DESC1 := '60 - E: THERE WAS AN ERROR IN READING FILE. ';
UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);
IF L_MESG_LEN + LENGTH(L_LINE) > MAX_SIZE THEN
L_MESG := '*** truncated ***' || CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
MESG_LENGTH_EXCEEDED := TRUE;
EXIT;
END IF;
L_MESG := L_LINE || CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN UTL_FILE.INVALID_PATH THEN
RAISE ABORT_PROGRAM;
WHEN OTHERS THEN
RAISE ABORT_PROGRAM;
END;
L_MESG := CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
UTL_FILE.FCLOSE(L_FILE_HANDLE);
END IF;
END LOOP;
RETURN_DESC1 := '70 - E: THERE WAS AN ERROR IN CLOSING MIME BOUNDARY. ';
L_MESG := CRLF || '--DMW.Boundary.605592468--' || CRLF;
UTL_SMTP.WRITE_DATA ( CONN, L_MESG );
UTL_SMTP.CLOSE_DATA( CONN );
UTL_SMTP.QUIT( CONN );
EXCEPTION
WHEN ABORT_PROGRAM THEN
RETURN_DESC := RETURN_DESC1;
WHEN OTHERS THEN
RETURN_DESC := RETURN_DESC1;
END;
/
|
|
|
|
| Re: design of birthday reminder/ Oracle to mail client : [message #85221 is a reply to message #85203] |
Wed, 09 June 2004 23:34 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Here is another code which can be used to send mails & attchaments from FORMS.
Regards
Himanshu
DECLARE
OutlookApp OLE2.OBJ_TYPE;
NameSpace OLE2.OBJ_TYPE;
MailItem OLE2.OBJ_TYPE;
OLEPARAM OLE2.LIST_TYPE;
Send OLE2.OBJ_TYPE;
Attachments OLE2.OBJ_TYPE;
Attachment_dummy OLE2.OBJ_TYPE;
var1 varchar2(100);
Begin
--assign a value to the variable from the Oracle Form
-- var1 := :BLOCK5.txt5;
--but for testing, populate the variable 'by hand'
var1 := 'This is a test of Outlook from Oracle app.';
OutlookApp := OLE2.CREATE_OBJ('Outlook.Application');
OLEPARAM := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(OLEPARAM,'MAPI');
NameSpace := OLE2.INVOKE_OBJ(OutlookApp,'GetNameSpace',OLEPARAM);
OLE2.DESTROY_ARGLIST(OLEPARAM);
OLEPARAM := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(OLEPARAM,0);
MailItem := OLE2.INVOKE_OBJ(OutlookApp,'CreateItem',OLEPARAM);
OLE2.DESTROY_ARGLIST(OLEPARAM);
OLE2.SET_PROPERTY(MailItem,'To','xyz@abc.com');
OLE2.SET_PROPERTY(MailItem,'Subject','message testing Outlook
automation from Oracle');
OLE2.SET_PROPERTY(MailItem,'Body', 'hello again '||var1);
--add an attachment
Attachments := OLE2.GET_OBJ_PROPERTY(MailItem,'Attachments');
OLEPARAM := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(OLEPARAM,'C:BBC.txt');
Attachment_dummy := OLE2.INVOKE_OBJ(Attachments,'add',OLEPARAM);
OLE2.DESTROY_ARGLIST(OLEPARAM);
Send := OLE2.INVOKE_OBJ(MailItem,'Send');
--destroy objects
OLE2.RELEASE_OBJ(MailItem);
OLE2.RELEASE_OBJ(NameSpace);
OLE2.RELEASE_OBJ(OutlookApp);
END;
|
|
|
|
Goto Forum:
Current Time: Mon Jul 08 15:35:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
