Oracle Database Discussions

Hi Group,

I have been asked to use UTL_SMTP to send a plain file in a oracle database version 9i over Windows, I receive the attached file on my mail, but the problem is that when we open the file, the text shows junk chars, or only numbers...

What parameter controls encoding the text or what I am doing wrong.

Thanks in advance.

declare
  l_maicon utl_smtp.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
---
  v_archivo bfile:=NULL;
  v_texto varchar(4000);
  v_adjunto raw(32767);
begin
  l_maicon :=utl_smtp.open_connection('mail.anyuser.com');
  utl_smtp.helo(l_maicon,'hostname');
  utl_smtp.mail(l_maicon,'NotificadorOracle@anyuser.com');
  utl_smtp.rcpt(l_maicon,'dbamail@anydomain.com');

  UTL_SMTP.open_data(l_maicon);
  
  UTL_SMTP.write_data(l_maicon, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'To: ' || 'anyusermail@anydomain.com' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'From: ' || 'NotificadorOracle@anydoming.com' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Subject: ' || 'test email oracle 9i con anexado'|| UTL_TCP.crlf);
--  UTL_SMTP.write_data(l_maicon, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
  
  UTL_SMTP.write_data(l_maicon, '--' || l_boundary || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_maicon, 'test de email oracle 9i, pf no tomar en cuenta');
  UTL_SMTP.write_data(l_maicon, UTL_TCP.crlf || UTL_TCP.crlf);


  UTL_SMTP.write_data(l_maicon, '--' || l_boundary || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Content-Type: ' || 'text/plain; charset="iso-8859-1"' || '; name="' || 'switch.sql' || '"' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_maicon, 'Content-Disposition: attachment; filename="' || 'switch.sql' || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  v_archivo:=bfilename('BDUMP', 'switch.sql');
  dbms_lob.fileopen(v_archivo, dbms_lob.file_readonly);
  v_adjunto:=dbms_lob.substr(v_archivo);
--  v_texto:=dbms_lob.substr(v_archivo);
  dbms_lob.close(v_archivo);

  UTL_SMTP.write_data(l_maicon, v_adjunto);

  UTL_SMTP.write_data(l_maicon, UTL_TCP.crlf || UTL_TCP.crlf);
  
  UTL_SMTP.write_data(l_maicon, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_maicon);

  utl_smtp.quit(l_maicon);
end;
/