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  |
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;
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 01:47:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
