BLOB or CLOB?

[lynn814]

I want to store Word documents in the db. We don't want to use BFILE beacuse we need the files to be stored in the the db.

Should we use BLOB or CLOB?

[tamilselvan]

You can use BLOB.

[lynn814]

I was under the impression that we use CLOB. Could you explain as to why we use BLOB?

[julian]

Originally posted by lynn814
I was under the impression that we use CLOB. Could you explain as to why we use BLOB?

BLOB is for binary objects, CLOB is for ASCIIs. Now, is a Word file an ASCII file? No. So, BLOB is the answer.

[ales]

Originally posted by julian
CLOB is for ASCIIs.

Just a little refinement:

CLOB is for character data corresponding to the database character set, it's not restricted for ASCII characterset only.

Ales

[julian]

Here is some additional info realted to this thread:

"In Oracle 8.0.x CLOBs are not supported in multibyte character set databases (e.g. UTF8 databases). In Oracle8i a workaround has been implemented: CLOB data are converted to fixed-width Unicode UTF-16 encoding before being stored in the database. Unicode is a superset (though not a binary superset) of all Oracle character sets and thus there is no risk of any data loss in this conversion.

But fixed-width Unicode is not a *binary* superset of Oracle database character sets. Even ASCII characters are stored in two bytes each with most siginificant byte being zero. If the database character set is US7ASCII or WE8ISO8859P1, CLOB data are stored as US7ASCII or WE8ISO8859P1. But if it is UTF8, data are stored as UTF-16. And, if you just change the database character set declaration with ALTER DATABASE, the CLOB data becomes invalid -- it lacks a zero MSB between each two US7ASCII bytes."