RE:Enviar correo desde ORACLE
utiliza :
Function Send_Mail(ln_req Number,ln_prov_key Number,lv_razon_social Varchar2, lv_mail Varchar2) Return Boolean Is
SendorAddress Varchar2(50);
ReceiverAddress varchar2(50);
EmailServer varchar2(30);
Port number;
fecha Date;
conn SYs.UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg Long;
mesg_body varchar2(4000);
subject Varchar2(300);
lb_result Boolean;
ln_max_art Number;
ln_max_um Number;
ln_max_marca Number;
ln_max_color Number;
ln_max_art2 Number;
ln_max_um2 Number;
ln_max_marca2 Number;
ln_max_color2 Number;
Cursor c_articulos Is
Select To_Char(a.partida,'000000')||' '||To_Char(a.cantidad,'9999999999')||' '||
b.nombre_articulo||lpad(' ',ln_max_art-length(b.nombre_articulo)+2)||' '||
c.descripcion_um||lpad(' ',ln_max_um-length(c.descripcion_um)+1) ||' '||
d.nombre_marca||lpad(' ',ln_max_marca-length(d.nombre_marca)+2) ||' '||
e.descripcion_color||lpad(' ',ln_max_color-length(e.descripcion_color)+2) ||' '||
a.observaciones partida
From compras.adq_requisiciones_det a,
compras.adq_articulos b,
compras.adq_um c,
compras.adq_marcas d,
compras.adq_colores e
Where a.art_key = b.art_key
And a.um_key = c.um_key
And a.marca_key = d.marca_key
And a.color_key = e.color_key
And req_key = ln_req;
lc_articulos c_articulos%RowType;
lv_marco Varchar2(200);
Begin
Begin
Select valor
Into Port
From ingresos.i_lst_valores
Where tipo = 'PUERTO_CORREO';
Exception When Others Then
Null;
End;
Begin
Select valor
Into EmailServer
From ingresos.i_lst_valores
Where tipo = 'SERVIDOR_CORREO';
Exception When Others Then
Null;
End;
Select Max(Length(b.nombre_articulo)) max_art,
Max(Length(c.descripcion_um)) max_um,
Max(Length(d.nombre_marca)) max_marca,
Max(Length(e.descripcion_color)) max_color
Into ln_max_art,
ln_max_um,
ln_max_marca,
ln_max_color
From compras.adq_requisiciones_det a,
compras.adq_articulos b,
compras.adq_um c,
compras.adq_marcas d,
compras.adq_colores e
Where a.art_key = b.art_key
And a.um_key = c.um_key
And a.marca_key = d.marca_key
And a.color_key = e.color_key
And req_key = ln_req;
If ln_max_art < 12 Then
ln_max_art2 := ln_max_art + 11-17;
Else
ln_max_art2 := ln_max_art - 10 + 4;
End If;
If ln_max_um < 3 Then
ln_max_um2 := ln_max_um + 2;
Else
ln_max_um2 := ln_max_um -2 + 4;
End If;
If ln_max_marca < 6 Then
ln_max_marca2 := ln_max_marca+1;
Else
ln_max_marca2 := ln_max_marca;
End If;
If ln_max_color < 6 Then
ln_max_color2 := ln_max_color;
Else
ln_max_color2 := ln_max_color;
End If;
Select 'PARTIDA '||'CANTIDAD '||'DESCRIPCIÓN'||lpad(' ',ln_max_art2)||'UM'||lpad(' ',ln_max_um2)||'MARCA'||lpad(' ',ln_max_marca)||'COLOR'||lpad(' ',ln_max_color2)||'OBSERVACIONES'
Into lv_marco
From Dual;
SendorAddress := Lower( <<< mail>>>>)
ReceiverAddress := lv_mail;
subject := <<<<<<<<Asunto>>>>>>>>>;
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress );
mesg:='Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||SendorAddress|| crlf ||
'Subject: '||subject|| crlf ||
'To: '||ReceiverAddress || crlf ||<<<<<<<mensaje>>>>>>>>>>>>>;
For lc_articulos In c_articulos Loop
mesg := mesg||lc_articulos.partida||chr(13);
End Loop;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
Return True;
Exception When Others Then
Return False;
End;