Home » SQL & PL/SQL » SQL & PL/SQL » MULTIPLE RECIENTS USING UTL_MAIL (OPENVMS)
MULTIPLE RECIENTS USING UTL_MAIL [message #344040] Thu, 28 August 2008 12:16 Go to next message
ettentrala
Messages: 2
Registered: August 2008
Location: Maryland
Junior Member
Hello All,

Here's a script that sends an attachment. My question is......without having to type each recipient's email address, how can I email the attachment to multiple recipients. I created a temp table that holds the sender and recpt's email address. Ex:

SEND_RECPT MAILIDS
SENDER xxxx@xx.com
RECPT you@oracle.com


This script works fine. All I'm trying to do is use the above table in a cursor to send email attachment to multiple recipients.

Can some help me please. I'm new to writing PL/SQL. Thanks.

DECLARE
fHandle sys.utl_file.file_type;
vTextOut varchar2(32000);
text varchar2(32000);
v_dir varchar2(30) := 'my_dir';

BEGIN

fHandle := sys.UTL_FILE.FOPEN('$1$DGA16:[JOBSUB.PPRD]','junk.doc','r');

IF sys.UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;

loop
begin
sys.UTL_FILE.GET_LINE(fHandle,vTextOut);
text:=text||vTextOut;
-- dbms_output.put_line(length(text));
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
end;
END LOOP;

--dbms_output.put_line(length(text));

sys.UTL_FILE.FCLOSE(fHandle);

sys.UTL_MAIL.SEND_ATTACH_VARCHAR2
(sender => 'art.spenr@xxxxx.com',
recipients => 'art.spenr@xxxxx.edu',
subject => 'Test mail',
message => 'This is a test to see if you receive this mail.',
attachment => text, ATT_INLINE => FALSE);
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
Re: MULTIPLE RECIENTS USING UTL_MAIL [message #344045 is a reply to message #344040] Thu, 28 August 2008 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Just concatenate the addesses together.

[Updated on: Thu, 28 August 2008 12:29] by Moderator

Report message to a moderator

Re: MULTIPLE RECIENTS USING UTL_MAIL [message #344056 is a reply to message #344045] Thu, 28 August 2008 12:46 Go to previous messageGo to next message
ettentrala
Messages: 2
Registered: August 2008
Location: Maryland
Junior Member
Thanks........I know that I can concatenate the email address together. Suppose I have 300 students I would like to send award letters to. I have a table with the students email address. where do I put the cursor in the previous script and how do I write the loop?
Re: MULTIPLE RECIENTS USING UTL_MAIL [message #344113 is a reply to message #344056] Thu, 28 August 2008 15:50 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
The script is fine. You're sending the email message to "art.spenr@xxxx.edu" instead of the list of email addresses you put in "text". the line recipients => 'art.spenr@xxxx.edu' should be recipients => text
Previous Topic: Trigger in support of a Staging Table on underlying table
Next Topic: sysrefcursor printing time
Goto Forum:
  


Current Time: Tue Dec 06 04:50:42 CST 2016

Total time taken to generate the page: 0.12241 seconds