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

I'm not sure if this will work for you but look at the DBMS_SQL package and see if perhaps the ability to move SQL into a PL/SQL table and execute the table might solve the problem.

Daniel Morgan

Richard Kuhler wrote:

> 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:23:26 CDT