saving emails in an Oracle table

From: Wallyraju <wallyraju_at_gmail.com>
Date: Wed, 27 Jan 2010 09:13:54 -0800 (PST)
Message-ID: <fea22752-b3ff-4a93-961b-1491ad373b4c_at_r37g2000vbp.googlegroups.com>

Oracle 11g R1
RHEL (not sure of version)

We are in the process of designing a table(s) in Oracle to store all the information pertaining to saving everything related to an email being sent out. The idea is to
gather everything for the email that needs to go out, save it to this table,
select from this table and send the email out using java code or oracle stored procs.

What I am wondering is that, are they standard templates out there that are being followed for a table to accomplish this. I searched on Google and Ask Tom, and found the pl.sql code to send the email but not a table definition to store the said email

Here is what I can think the column list should be. Please let me know if I am going overboard or if I am missing something. I took some of the parameters from the UTL_MAIL package and made them into columns in the table.

---

Here is the spec of the package I used

UTL_MAIL.SEND_ATTACH_VARCHAR2 (

   sender            IN    VARCHAR2 CHARACTER SET ANY_CS,
   recipients        IN    VARCHAR2 CHARACTER SET ANY_CS,
   cc                IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   bcc               IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   subject           IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   message           IN    VARCHAR2 CHARACTER SET ANY_CS,
   mime_type         IN    VARCHAR2 DEFAULT 'text/plain; charset=us-
ascii',
   priority          IN    PLS_INTEGER DEFAULT NULL
   attachment        IN    RAW,
   att_inline        IN    BOOLEAN DEFAULT TRUE,
   att_mime_type     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT
                          'application/octet',
   att_filename      IN    VARCHAR2 DEFAULT NULL);


--------------------------------------------------------------------------------------------------------------------------------------


<primary key col>      - NUMBER
from                          - varchar2(200)
reply_to                     - varchar2(200)
subject                      - varchar2(200)
priority                      - varchar2(20)
mime_type                - varchar2(20)
messageContent        - clob
toList                        - varchar2(4000)
ccList                       - varchar2(4000)
bccList                     - varchar2(4000)
att_inline                  - varchar2(1)
att_mime_type         - varchar2(20)
create_date              - date
create_user              - varchar2(30)
email_sent_indicator  - varchar2(1)
email_sent_date        - date

A supporting table for this would be one where the attachments are stored, and we already have one in place for that.

Thanks in advance for your help.

Wally Received on Wed Jan 27 2010 - 11:13:54 CST