Home » SQL & PL/SQL » SQL & PL/SQL » Sent attachment with pl/sql as a mail with ( mulitpel recepents ) (oracle 8i)
Sent attachment with pl/sql as a mail with ( mulitpel recepents ) [message #377598] Tue, 23 December 2008 14:59 Go to next message
satishk
Messages: 26
Registered: October 2008
Location: HYD
Junior Member
Hi,

I have an requirement ,like send a particular file as attachment.

i have done the same... but the issue is i need to send the file to multiple recipients that was based on the user provided value.

Ex: xyz_sat@yahoo.com;xyz2_sat2@yahoo.com;xyz3_sat3@yahoo.com;

but i am able to send only one recipient( xyz2_sat2@yahoo.com ) at a time can we send the same for multiple recipients...please suggest me ...



Please find the code i have written : It's working for single user( address)

declare
v_location VARCHAR2 (1000) := '/u01/share/';
v_file_id_num UTL_FILE.file_type;
v_file_name VARCHAR2 (2000):= 'o1296075.out';
v_from VARCHAR2 (80) := 'test_satish@yahoo.co.in';
v_recipient VARCHAR2 (80) := 'chiru_sati.yahoo.com';
--v_cc VARCHAR2 (80) := '';
v_subject VARCHAR2 (80) := ' OFS Test';
v_mail_host VARCHAR2 (30) := '172.16.24.192';
v_data VARCHAR2 (32000) := NULL;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
c UTL_TCP.connection;
rc INTEGER;
vSFile UTL_FILE.file_type;
vNewLine VARCHAR2(2000);
v_descriptor VARCHAR2(50);
v_count number ;

BEGIN

begin
select ( LENGTH(myfield)- LENGTH(REPLACE(myfield,';',''))) + ( LENGTH(myfield)- LENGTH(REPLACE(myfield,',',''))) into v_count from dual;
exception WHEN others then
v_count := 0 ;
end;


c := utl_tcp.open_connection('192.168.10.25', 25);
rc := utl_tcp.write_line(c, 'HELO 192.168.10.25');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 192.168.10.25');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||v_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||v_recipient);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
rc := utl_tcp.write_line(c, 'From: '||v_from||' <'||v_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||v_recipient||' <'||v_recipient||'>');
rc := utl_tcp.write_line(c, 'Subject: '||v_subject);
rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;');
rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"');
rc := utl_tcp.write_line(c,'');
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain');
rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit');
rc := utl_tcp.write_line(c,'');
rc := utl_tcp.write_line(c, ' Please use the file name '||substr(v_file_name,1,length(v_file_name )-4)||'.txt when running the order import program for mass mailing.'); ----- TEXT OF EMAIL MESSAGE
rc := utl_tcp.write_line(c,'');
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain;');
rc := utl_tcp.write_line(c, ' name="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;');
rc := utl_tcp.write_line(c, ' filename='||'"'||substr(v_file_name,1,length(v_file_name )-4)||'.pdf"');
rc := utl_tcp.write_line(c,'');

BEGIN
vSFile := utl_file.fopen(v_location, v_file_name ,'r');
IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine);

IF vNewLine IS NULL THEN
EXIT;
END IF;

rc := utl_tcp.write_line(c,vNewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);
END;

rc := utl_tcp.write_line(c, '-------SECBOUND--');
rc := utl_tcp.write_line(c,'');
rc := utl_tcp.write_line(c, '.');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c);

EXCEPTION
when others then
raise_application_error(-20000, SQLERRM);
END;

Re: Sent attachment with pl/sql as a mail with ( mulitpel recepents ) [message #377773 is a reply to message #377598] Thu, 25 December 2008 01:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you use utl_tcp to write directly to the smtp server, this problem is not Oracle-related. Check the SMTP protocol to see how to address multiple recipients.
Previous Topic: Applying Valid Number & Date Check
Next Topic: SWITCH-CASE
Goto Forum:
  


Current Time: Thu Dec 08 00:37:28 CST 2016

Total time taken to generate the page: 0.05400 seconds