passing data around in PL/SQL longer then 32K

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Wed, 10 Apr 2002 21:43:28 GMT
Message-ID: <3cb4b087.1635609341@news.globix.com>

I have a procedure in PL/SQL to send email.. Oracle has suggested using

  utl_smtp.open_data(c);
  utl_smtp.write_data(c, ...);
  utl_smtp.write_data(c, ...);

  ....
  utl_smtp.close_data(c);

to overcome varchar2 limit of 32K.. the problem still remains though.. since I'd like my send_email procedure to continue it's existance.. can I pass LOBS or LONG to PL/SQL?

Can I pass a 'reference' to my data so that large chunks of data don't get copied in memory?

My PL/SQL skills are not advanced enough to answer these questions :)

Thanx.

procedure send_email (p_from IN varchar2,p_from_name IN varchar2 default null,
 p_subject IN varchar2, p_body IN varchar2, p_to IN varchar2, p_cc IN varchar2 d
efault null)

AS
 conn utl_smtp.connection;
 crlf varchar2(2) := chr(13)||chr(10);
 mesg varchar2(32767);
begin
 conn := utl_smtp.open_connection( 'mail.mydomain.com', 25);

 utl_smtp.helo(conn, 'mail.mydomain.com');
 utl_smtp.mail(conn, p_from);
 utl_smtp.rcpt(conn, p_to);

 if p_cc is not null then utl_smtp.rcpt(conn, 'cc:'||p_cc); end if;

 mesg:= 'Date : '||to_char(sysdate, 'dd Mon yy hh24:mi:ss') ||crlf||


        'From : '||p_from_name||'<'||p_from||'>'||crlf||

        'Subject : '||p_subject||crlf||

        'To: '||p_to||crlf||

        'Cc: '||p_cc||crlf||

        '' ||crlf||

        p_body||crlf;
 utl_smtp.data(conn,mesg);
 utl_smtp.quit(conn);
END;

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Wed Apr 10 2002 - 16:43:28 CDT