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