Skip to Main Content
  • Questions
  • Loading email content into oracle table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, vivek.

Asked: February 14, 2011 - 9:24 am UTC

Last updated: May 17, 2019 - 9:21 am UTC

Version: 11.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have an interesting requirement, I want to load complete emails ( example outlook) in oracle tables.


=> When a mail content ( preview) is greater than 4000 than store in attachement table with name "long content"
and store complete content into clob field and also marked is_preview_full to set 1
=> if mail content ( preview) is less than 4000 than no entry should be created in attachement table for name "long content"
and is_preview_full shoul be 0

I have to load data via sql*loader
and i have comeup with following structure

Please Help me I am not able to fulfil requirement when mail content is greater than 4000 bytes.

Looking fwd for your reply

With regards, Vivek
PS: My test table structure is follows:



CREATE TABLE EMAIL_MESSAGE
(
MESSAGE_ID NUMBER(30),
SENDER VARCHAR2 (255) ,
RECIPIENT VARCHAR2 (4000) ,
RECEIVED_AT TIMESTAMP(6) ,
SOURCE VARCHAR2 (50) ,
INTERACTION_TYPE_CODE VARCHAR2 (10) NOT NULL ,
STATUS VARCHAR2 (10) ,
TITLE NVARCHAR2 (100) ,
PREVIEW NVARCHAR2 (4000) ,
IS_PREVIEW_FULL NUMBER (1)
);

ALTER TABLE EMAIL_MESSAGE
ADD CONSTRAINT EMAIL_MESSAGE_PK PRIMARY KEY ( MESSAGE_ID) ;

CREATE TABLE EMAIL_ATTACHEMENTS
(
ATTACHEMENT_ID NUMBER(30),
MESSAGE_ID NUMBER(30) ,
NAME VARCHAR2 (20) ,
VALUE_CLOB CLOB ,
VALUE_NCLOB NCLOB ,
VALUE_BLOB BLOB
)
;

ALTER TABLE EMAIL_ATTACHEMENTS
ADD CONSTRAINT ATTACHEMENTS_PK PRIMARY KEY ( ATTACHEMENT_ID ) ;



ALTER TABLE EMAIL_ATTACHEMENTS
ADD CONSTRAINT MESSAGE_2_ATT_FK FOREIGN KEY
(
MESSAGE_ID
)
REFERENCES EMAIL_MESSAGE
(
MESSAGE_ID
)
ON DELETE CASCADE
;


LOAD DATA
INTO TABLE EMAIL_ATTACHEMENTS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ATTACHEMENT_ID EXTERNAL_INTEGER,
MESSAGE_ID EXTERNAL_INTEGER ,
NAME CHAR(4000),
VALUE_CLOB CHAR(1000000) ,
VALUE_NCLOB CHAR(1000000),
VALUE_BLOB BLOB
)
INTO TABLE DEMO_MESSAGE$M
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
context_id EXTERNAL_INTERGER,
sender CHAR(255),
recipient CHAR(4000),
received_at EXPRESSION "current_timestamp(6)",
SOURCE CHAR(50) ,
INTERACTION_TYPE_CODE CHAR(10),
STATUS CHAR(10),
TITLE CHAR(100),
PREVIEW CHAR(4000)
)
BEGINDATA
1,abc@gmail.com,abc@yahoo.com,EMAIL,DUMMY,DUMMY1,TEST-1,11111-I am feeling lucky because I love my parents|
2,def@gmail.com,def@yahoo.com,EMAIL,DUMMY,DUMMY2,TEST-2,22222-I am feeling lucky because I love my parents|
3,ghi@gmail.com,ghi@yahoo.com,EMAIL,DUMMY,DUMMY3,TEST-3,33333-I am feeling lucky because I love my parents1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmn
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890|
4,jkl@gmail.com,jkl@yahoo.com,EMAIL,DUMMY,DUMMY4,TEST-4,44444-I am feeling lucky because I love my parents|

and Tom said...

... I have to load data via sql*loader
...

you'll probably have to change that - that is the cool thing about requirements - they are actually "requests" and we can and will change them many times.

I would start with your structure. Use one table and only one table. You have a 1:1 optional relationship, just stick those columns on the end of your first table - do not use two tables.

Then I would hit the requirement to put it in one column if 4000 or less and a lob if more than 4000. Just use the LOB and "enable storage in row". Physically - we will store the lob inline, in the table, if it is less than 4000 (just like you are trying to do) and store it out of line in another segment if it is greater than 4000 (just like you are trying to do!!!!)

So, use one table and JUST ONE COLUMN. A raw email will be a CLOB - attachments and such would be encoded in text so just a CLOB or NCLOB (but just one of them) is all you need.


Now, you have the issue of how to load data with sqlldr with embedded newlines.
This addresses that question:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1286201753718


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

vivek Nema, February 14, 2011 - 6:07 pm UTC

Thanks,

Fully Agree, requirement can be rethink with one table contain CLOB.Let see if its possible.
Anyway,
Still I am wondering how can solve the existing problem
if preview <= 4000 then load into preview field
if preview > 4000 then load as clob in another table
Tom Kyte
February 15, 2011 - 8:17 am UTC

... Still I am wondering how can solve the existing problem
if preview <= 4000 then load into preview field
if preview > 4000 then load as clob in another table
..

re-read my answer, that is the second requirement I said "rethink and discard".

We (as in the LOB type) ALREADY DOES THAT.

If you define your lob as "enable storage in row" (the default setting by the way), then

IF lob < 4000 bytes
then
   store the lob right in the table, right in the row
else
   store a pointer (like a foreign key) in the row
   store the lob in another segment outside of the table
end if



Think about it - how much simpler will your code be if you just use a single column. Your code is MUCH easier to write/maintain *and* your GOAL (of having the short emails stored IN the table and LARGE emails stored in another 'table') is 100% achieved.

You need do nothing special.

One table
One column
Simple
all *goals* achieved.


Agree

vivek Nema, February 15, 2011 - 8:21 am UTC

Hi Tom,

Thanks for input. Finally was Able to test and trying to load complete data. It seems your suggestion is correct and implementation become very simpler.

Wonderful, Appreciate that!

With regards,

Loading email attachment to table

Dipika, May 14, 2019 - 6:54 am UTC

Hello Experts,

There is also new requirement from our end that we want to save email attachment(let's say PDF,CSV or excel)to directory into oracle db tables as we receive mail in inbox.

Can you please help us.
Thanks in advance.
Chris Saxon
May 17, 2019 - 9:21 am UTC

What automatically what the email arrives?

If so you're going to have build something which processes emails as they arrive in your mail client/server.

Exactly how you do this depends on the client you're using.

Or are people going to save these manually to a shared folder?

There are many details like this which affect how you'll build your solution. So this is beyond the scope of what we can help with here.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here