Pros and Cons between oracle long vs BLOB or NCLOB data type?

hi PLease read   "Speed of CLOB vs LONG",  on ask tom. Tom has given one more link to  another one of his answer..
Hope this will throw some light..

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

The given below is another extract from Asktom site. He suggests CLOB only..


   

Meyer -- Thanks for the question regarding "Lobs or Longs", version 8I and 9I
Submitted on 10-Jan-2003 12:14 Eastern US timeTom's latest followup | Bookmark | BottomLast updated 10-Jan-2003 13:01
You Asked
1)It is my understanding, at that at this point it is always better to use Blobs instead
of Longs...is that correct or wrong (table defs and pl/sql)?

2)When (if ever) do you still use longs?

3)For asktom, is our posts stored as longs or blobs...and if you re-created it
now...would it be the same?

4)Specifically going to create a web app that will store/display standard text that could
probably fit in a varchar(4000), but want to be ready for longer entries (just in
case)...They will be displayed/sorted based on other columns in the table. Lob or Long
for the text?

Thanks,
Meyer

 


and we said...
1) in the database -- do not use long or long raw -- only use blobs and clobs

in plsql, long is really just varchar2(32765) and long raw is just raw(32765).  If you
need a big string, go ahead and use long -- it is just a subtype that is a varchar2 in
disguise.

2) never

3) clobs -- all in clobs.  Yes, it would be the same.

4) CLOBS are the only type you should even think about considering.
 
 This is available at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7251849672474
 

Hi,

BLOB is a binary datatype, NCLOB is a character datatype.  NCLOB also can be 2 bytes per character depending on what NLS parameters you have...

good luck :)

Hi,

a few more notes about long data type
- is only provided for backward compatibility only
- can only store up to 2GB
- can only have one long column per table

Cheers,
Aimee

What about portablity between different database engine? is that LOB type only for oracle?

LOB is oracle datatype.. Might be you will have same or equivalent in other  database  like DB2 or SQLServer..

Use only LOBs because everything other is
for back compatibility.
Because of the big size of the LOB columns and the fact that every LOB
column is indexed by default Oracle provides and recommends to create for
every LOB column two tablespaces - one for the data and one for index:

CREATE TABLE test_lobtable (
      id  NUMBER
    , xml_file CLOB
    , image    BLOB
    , log_file BFILE
)
LOB (xml_file)
    STORE AS xml_file_lob_seg (
        TABLESPACE lob_data
        CHUNK 4096
        CACHE
        STORAGE (MINEXTENTS 2)
        INDEX xml_file_lob_idx (
            TABLESPACE lob_index
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
LOB (image)
    STORE AS image_lob_seg (
        TABLESPACE lob_data
        ENABLE STORAGE IN ROW
        CHUNK 4096
        CACHE
        STORAGE (MINEXTENTS 2)
        INDEX image_lob_idx (
            TABLESPACE lob_index
        )
    )
/