Re: passing data around in PL/SQL longer then 32K

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 10 Apr 2002 23:10:03 GMT
Message-ID: <fD3t8.38833$zN.16193644@twister.socal.rr.com>

You could use a temporary internal CLOB but that could be slow (writes to TEMPORARY tablespace) and a pain (have to use DBMS_LOB to manipulate). It might be easier for you to have your own table type that could store multiple varchar2 chunks (up to 32k each) directly. Pass the table reference directly (IN arguments are all passed by reference) and iterate over the table elements for your calls to utl_smtp.write_data...

package mail ...

    type varchar2_table_type is table of varchar2(32767) index by binary_integer;

package body mail ...

    procedure send_email (... p_body in varchar2_table_type ...

    ...

    i := p_body.first;

    loop

        exit when i is null;
        utl_smtp.write_date(c, ...);
        i := p_body.next(i);

    end loop;

caller ...

    p_body(1) := <first_chunk>;
    p_body(2) := <second_chunk>;     

    ...     

    mail.send_mail( .... p_body ...)

Richard

NetComrade wrote:
>
> 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 - 18:10:03 CDT