CODEWITHSUNDEEP
pythonoracle-database
Jason Baker
Jason Baker

Reputation: 198577

Help me understand the difference between CLOBs and BLOBs in Oracle

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

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?

Upvotes: 39

Views: 44105

Answers (2)

Quassnoi
Quassnoi

Reputation: 425371

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.

Upvotes: 56

Alex Martelli
Alex Martelli

Reputation: 881665

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.

Upvotes: 11

Related Questions