Help me understand the difference between CLOBs and BLOBs in Oracle

Question

This is mainly just a "check my understanding" type of question. Here's my understanding of CLOBs and BLOBs as they work in Oracle:

• CLOBs are for text like XML, JSON, etc. You should not assume what encoding the database will store it as (at least in an application) as it will be converted to whatever encoding the database was configured to use.
• BLOBs are for binary data. You can be reasonably assured that they will be stored how you send them and that you will get them back with exactly the same data as they were sent as.

So in other words, say I have some binary data (in this case a pickled python object). I need to be assured that when I send it, it will be stored exactly how I sent it and that when I get it back it will be exactly the same. A BLOB is what I want, correct?

Is it really feasible to use a CLOB for this? Or will character encoding cause enough problems that it's not worth it?

Answer 1

CLOB is encoding and collation sensitive, BLOB is not.

When you write into a CLOB using, say, CL8WIN1251, you write a 0xC0 (which is Cyrillic letter А).

When you read data back using AL16UTF16, you get back 0x0410, which is a UTF16 represenation of this letter.

If you were reading from a BLOB, you would get same 0xC0 back.

Answer 2

Your understanding is correct. Since you mention Python, think of the Python 3 distinction between strings and bytes: CLOBs and BLOBs are quite analogous, with the extra issue that the encoding of CLOBs is not under your app's control.