Home » SQL & PL/SQL » SQL & PL/SQL » utl_smtp and national charset (Oracle 9i)
utl_smtp and national charset [message #320162] Wed, 14 May 2008 03:45 Go to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Hello,

I know this question has been asked before, but sorry I still can't make it work Sad
I have a package created like this:
CREATE OR REPLACE PACKAGE SETTINGS.EMAIL_PACK
IS
-- SEND MAIL

	PROCEDURE SEND_MAIL(
	pSender    IN VARCHAR2,
	pRecipient IN VARCHAR2,
	pSubject   IN VARCHAR2,
	pMessage   IN LONG);

END;

CREATE OR REPLACE 
PACKAGE BODY          SETTINGS.email_pack   AS

--private area
--private constants
	crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
--private functions
FUNCTION FORMAT_EMAIL_BODY(EMAIL_BODY IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN REPLACE(EMAIL_BODY,CHR(10),crlf);

END FORMAT_EMAIL_BODY;

---------------------public area---------
	PROCEDURE SEND_MAIL(pSender    IN VARCHAR2,
		  			pRecipient IN VARCHAR2,
					pSubject   IN VARCHAR2,
					pMessage   IN LONG)
IS
	mailhost  VARCHAR2(30);

--    UTL_TCP.CRLF
	mesg     LONG;
	mail_conn utl_smtp.connection;

BEGIN
--
mailhost := SETTINGS.SISTEMOS_NUSTATYMAS_PACK.GET_REIKSME_TEKSTAS('SMTP_EMAIL_HOST');
   mail_conn := utl_smtp.open_connection(mailhost, 25);
   mesg := 'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || 
           'Mime-Version: 1.0' || crlf ||
           'Content-Type: text/plain; charset=Windows-1257' || crlf || FORMAT_EMAIL_BODY(pMessage);

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, pSender);
   utl_smtp.rcpt(mail_conn, pRecipient);
   utl_smtp.data(mail_conn, mesg);
/*utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(CONVERT(pMessage ,'BLT8MSWIN1257')));*/
   utl_smtp.quit(mail_conn);

END SEND_MAIL;

END;


If I execute a test script that includes Lithuanian letters:
DECLARE BEGIN SEND_MAIL ('vb@vb.lt', 'vb@vb.lt', 
'à è æ ë á ð ø û þ À È Æ Ë Á Ð Ø Û Þ', 
'
à è æ ë á ð ø û þ
À È Æ Ë Á Ð Ø Û Þ  
' ); END;


I receive an email that looks like this:
Quote:
Subject: a c e ? ? s ? ? z A C E ? ? S ? ? Z
Body:
a c e ? ? s ? ? z
A C E ? ? S ? ? Z
.

I have read that I have to do some trick with RAW conversion, but I can't find what it is exactly. E.g. if I uncomment the line
   utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(CONVERT(pMessage ,'BLT8MSWIN1257')));

I receive error ORA-29277: Invalid SMTP operation.

Help please! Razz
Re: utl_smtp and national charset [message #320311 is a reply to message #320162] Wed, 14 May 2008 11:44 Go to previous message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
What is the output if you run just this?
utl_raw.cast_to_raw(CONVERT(pMessage ,'BLT8MSWIN1257'))

Or this?
CONVERT(pMessage ,'BLT8MSWIN1257')

Keep in mind you can specify the original character set for CONVERT:
utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw(CONVERT(pMessage ,'BLT8MSWIN1257',<original char set>)));
Previous Topic: Retriving Package
Next Topic: Not able to write the data into output file
Goto Forum:
  


Current Time: Sun Nov 03 01:18:59 CDT 2024