Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: passing data around in PL/SQL longer then 32K
Our PSP_Mail product overcomes this by using PL/SQL table type to pass in the
message body. You can download evaluation copy and documentation at
http://www.dpsp-yes.com
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3cb4b087.1635609341_at_news.globix.com...Received on Thu Apr 11 2002 - 06:45:50 CDT
> 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