[melody116]

You want to write a HTML Email or sth? Or you want to stop receiving these email?

If you want to write, here is sth i foundi did not catch, hope you can)

TO SEND AN EMAIL WITH ATTACHED FILE USING OCS EMAIL PL/SQL API:

Create a table containing the BLOB:

SQL> create table doc (id number primary key, text blob default empty_blob());
SQL> insert into doc values (1, empty_blob());

Load your zip into the BLOB:

Create a directory usage from the database to the filesystem:

SQL> create directory DIR_TMP as '/tmp';

Assuming there is a file /tmp/my.zip which size is 11097 bytes:

Run this procedure:

SQL> CREATE OR REPLACE PROCEDURE loadzip IS
Dest_loc BLOB;
Src_loc BFILE := BFILENAME('DIR_TMP', 'my.zip');
Amount INTEGER := 11097;
BEGIN
/* Opening the LOB is mandatory: */
select text into dest_loc from doc where id=1 for update;
DBMS_LOB.FILEOPEN(Src_loc, DBMS_LOB.LOB_READONLY);
/* Opening the LOB is optional: */
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
/* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE(Src_loc);
COMMIT;
END;
/

SQL> exec loadzip;

This loads the zip file into the BLOB row of doc table for id=1.

Send the email attaching this file.

Run the following SQL:

SQL> set serveroutput on size 1000000;

DECLARE
to_addr VARCHAR2(100) := 'recipient@mydomain.com';
from_addr VARCHAR2(100) := 'sender@mydomain.com';
subject_str VARCHAR2(100) :=
'Example: send a multipart/mixed message';

msg_obj mail_message_obj;
a_bodypart mail_bodypart_obj;
b_bodypart mail_bodypart_obj;
msg_data VARCHAR2(500);
myfile BLOB;

BEGIN
-- start composing a message
mail_message.compose_message(msg_obj);
dbms_output.put_line('start composing...');
-- set the message's headers
-- to multipart/mixed
mail_message.set_msgheader(
msg_obj, to_addr, from_addr, null, from_addr, sysdate,
subject_str, '1.0', 'multipart/mixed', null, null
);
dbms_output.put_line('set message header');

-- add the text/plain body-part
mail_message.add_bodypart(msg_obj, a_bodypart);
mail_message.set_bpheader(
a_bodypart, 'text/plain', 'us-ascii', 'quoted-printable',
null, null, null, null, null, null
);
msg_data := 'Please find a message in attachment';
mail_message.set_content(a_bodypart, utl_raw.cast_to_raw(msg_data));
dbms_output.put_line('set text/plain body');

-- add the text/html body-part

mail_message.add_bodypart(msg_obj, b_bodypart);
mail_message.set_bpheader(
b_bodypart, 'application/x-zip-compressed', 'us-ascii', 'base64',
null, null, null, null, 'attachment', 'myattach.zip'
);

-- retrieves the lob itself

select text
into myfile
from doc
where id = 1;

mail_message.set_content(b_bodypart, myfile);
dbms_output.put_line('set base64 body');

-- now send this message
mail_message.send_message(msg_obj);
dbms_output.put_line('send message');

-- commit
commit;
EXCEPTION
when OTHERS then
dbms_output.put_line ('send failed!');
dbms_output.put_line('SQLcode: ' || sqlcode);
dbms_output.put_line('SQLerrm: ' || sqlerrm);
rollback;
END;
/