APEX

Hey everyone...

Sorry for the long post...this is a technical question...not a troubleshoot my code question...

I am trying to send an e-mail using a PL/SQL process on an Apex page. There are a lot of people that need to be CC'd on the same message (so they can do a reply-to-all).

The problem is, the CC field is limited to 2000 characters (we can easily have up to 4000-6000 email address characters in one email).

I figured this out by the following command (amongst other findings on the internet):

describe flows_030100.wwv_flow_user_mail_queue;
Name Null Type
------------------------------ -------- -----------------------------------------------------------------
ID NOT NULL NUMBER
MAIL_TO VARCHAR2(2000)
MAIL_FROM VARCHAR2(2000)
MAIL_REPLYTO VARCHAR2(2000)
MAIL_SUBJ VARCHAR2(2000)
MAIL_CC VARCHAR2(2000)
MAIL_BCC VARCHAR2(2000)
MAIL_BODY CLOB()
MAIL_BODY_HTML CLOB()
MAIL_SEND_COUNT NUMBER(2)
MAIL_SEND_ERROR VARCHAR2(4000)
LAST_UPDATED_BY VARCHAR2(255)
LAST_UPDATED_ON DATE

When I try to send the email I get the following error:

ORA-12899: value too large for column "APEX_030200"."WWV_FLOW_MAIL_QUEUE"."MAIL_CC" (actual: 2488, maximum: 2000)


Is there anyway that I can increase the CC column size?

I also have a procedure that sends email using utl_smtp.connection and ( and later utl_smtp.data to actually send the email). Would this method run into the same problems? The reason why I want to do it via the pl/sql process on the page is because of the easy integration with sending attachments...not sure how i could easily do this within the external procedure...ie see code below...


DECLARE
v_subject VARCHAR2(100);
v_email_body VARCHAR2(12000);
v_recipient_mail VARCHAR2(200);
v_cc_list VARCHAR2(12000);
v_from_email_address VARCHAR2(200);
v_id NUMBER := :316_EMAILID;

-- holds token information for looping through recipient list to get
-- individual records
LC$Token VARCHAR2(200);
i INTEGER := 1;

BEGIN

v_subject := :P316_EMAILSUBJECT;
v_email_body := :P316_EMAILBODY;

-- send a copy to the sender, we will CC everyone else!
v_from_email_address := FUN_PE_PERSON_LOOKUP('EMAIL', :APP_USER, 'dummySessionID');
v_recipient_mail := v_from_email_address;

-- the page item is delimited with semi-colons, but APEX_MAIL calls for
-- CC lists to be seperated by commas
v_cc_list := replace(:P316_RECIPIENTLIST, ';' , ',');

v_id := APEX_MAIL.SEND
(p_to => v_recipient_mail -- we are really just sending to the sender
,p_cc => v_cc_list -- cc everyone in one big email
,p_from => v_from_email_address -- sender's email address
,p_replyto => v_from_email_address
,p_body => v_email_body
,p_body_html => v_email_body
,p_subj => v_subject);

-- Having set up your email, now add one (or more) attachments...
FOR c1 in (SELECT SUBSTR (FILENAME, (INSTR(FILENAME, '/') + 1)) as FILENAME
,BLOB_CONTENT
,MIME_TYPE
FROM TBL_PE_MSGS_EMAIL_ATTACHMENTS where parentEmailID = :P316_EMAILID)
LOOP
IF c1.blob_content IS NOT NULL THEN
APEX_MAIL.ADD_ATTACHMENT( p_mail_id => v_id,
p_attachment => c1.blob_content,
p_filename => c1.filename,
p_mime_type => c1.mime_type);
END IF;
END LOOP;

-- the push queue bypasses the 10 minute delay built into oracle by default
APEX_MAIL.PUSH_QUEUE;

-- add exception handling for when an e-mail address is unavailable (ie someone left
-- the company)

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');

-- end exception handling


END;

using
Application Express 3.2.1.00.11
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production

Thanks!!!!