- Oracle l'essentiel
- Installation d'Oracle
- Les Scripts
Seconde procédure permettant de transmettre un courriel avec un fichier joint. (tout type de fichier). Il faut créer un objet de type directory sur Oracle, et donner les droits Lecture/écriture pour le propriétaire de la procédure.(Téléchargement de cette procédure)
CREATE OR REPLACE PROCEDURE ITEMS."ENVOIEMAILATTACH" (NameDest IN VARCHAR2 DEFAULT '<toto.maison@provider.com>,<titi.maison@provider.com>',
Objet IN VARCHAR2 DEFAULT 'Objet du message',
BodyEmail IN VARCHAR2 DEFAULT 'Corp du message',Attachement IN BOOLEAN DEFAULT TRUE, pdirpath IN varchar2 DEFAULT 'DOC_DIR', pfilename IN varchar2 DEFAULT 'DOC.sql')
is
c utl_smtp.connection;
v_add_src varchar2(2000);
v_addr varchar2(40);
slen number := 1;
SLP PLS_INTEGER := 300;
v_reply UTL_SMTP.REPLY;
b_connected BOOLEAN := FALSE;
v_Bfile bfile;
l_boundary VARCHAR2(50) := '77LECOINDUDBA77';
lensrc_off PLS_INTEGER := 1;
lendst_off PLS_INTEGER := 1;
lenBuffSize integer := 57;
lIdx integer := 1;
lraw raw(57):= NULL;
LenBlob integer := 0;
BEGIN
BEGIN
if Attachement = TRUE THEN
v_Bfile := BFILENAME(pDirPath, pFileName);
end if;
EXCEPTION
WHEN no_data_found THEN
RETURN;
end;
FOR i IN 1 .. 3
LOOP
BEGIN
c := utl_smtp.open_connection('smtp.provider.com');
v_reply := utl_smtp.helo(c, 'smtp.provider.com');
IF 250 = v_reply.code THEN
b_connected := TRUE;
EXIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOCK.SLEEP (SLP);
END;
END LOOP;
IF b_connected = FALSE THEN
RETURN;
END IF;
utl_smtp.mail(c, '<no-reply@peutimporte.com>');
v_add_src := replace(NameDest,' ','_');
if(instr(NameDest,',') = 0) then
utl_smtp.rcpt(c, v_add_src);
else
v_add_src := replace(NameDest,' ','_')||',';
while(instr(v_add_src,',',slen) > 0)
loop
v_addr := substr(v_add_src, slen, instr(substr(v_add_src,slen),',')-1);
slen := slen+instr(substr(v_add_src, slen),',');
utl_smtp.rcpt(c, v_addr);
end loop;
end if;
utl_smtp.open_data(c);
utl_smtp.write_data(c, 'From: <no-reply@peutimporte.com>'|| utl_tcp.CRLF);
utl_smtp.write_data(c, 'To:'||NameDest|| utl_tcp.CRLF);
utl_smtp.write_raw_data(c,utl_raw.cast_to_raw( 'Subject: '||Objet||utl_tcp.CRLF ));
if Attachement = TRUE THEN
utl_smtp.write_data(c,'Mime-Version: 1.0'||utl_tcp.CRLF);
utl_smtp.write_data(c,'Content-Type: multipart/mixed;boundary="'||l_Boundary||'"'|| utl_tcp.CRLF|| ''|| utl_tcp.CRLF);
utl_smtp.write_data(c,'--'||l_Boundary|| utl_tcp.CRLF);
end if;
utl_smtp.write_data(c, 'Content-Type: text/plain; charset=iso-8859-1'||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Content-Transfer-Encoding: quoted-printable'||utl_tcp.CRLF);
utl_smtp.write_raw_data(c,utl_raw.cast_to_raw( utl_tcp.CRLF || BodyEmail));
utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF);
if Attachement = TRUE THEN
utl_smtp.write_data(c,'--'||l_Boundary|| utl_tcp.CRLF);
utl_smtp.write_data(c,'Content-Type: application/octet-stream; name="'|| pFileName|| '"'|| utl_tcp.CRLF);
utl_smtp.write_data(c,'Content-Disposition: attachment; filename="'|| pFileName|| '"'|| utl_tcp.CRLF);
utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF|| utl_tcp.CRLF);
BEGIN
dbms_lob.fileopen(v_Bfile, dbms_lob.file_readonly);
LenBlob := dbms_lob.GETLENGTH(v_Bfile);
while lIdx < LenBlob loop
dbms_lob.read(v_Bfile, lenBuffSize, lIdx, lraw);
utl_smtp.write_raw_data(c,utl_encode.base64_encode(lraw));
lraw := NULL;
lIdx := lIdx + lenBuffSize;
end loop;
EXCEPTION
WHEN no_data_found THEN
utl_smtp.quit(c);
return;
END;
dbms_lob.FILECLOSE(v_Bfile);
utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF|| '--'||l_Boundary||'--'|| utl_tcp.CRLF);
end if;
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; -- When the SMTP server is down or unavailable, we don't
-- have a connection to the server. The quit call will
-- raise an exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
Scripts Oracle Envoi de courriel suite