Oracle BLOB vs VARCHAR

Question

I need to store a (large) SQL query in a column of a table and I thought using a BLOB field. To be clear, I want to store the query, not its result.

What's best to use: BLOB or a VARCHAR? Or something else maybe?

Answer 1

Another option is CLOB. For text data it's more logical to use CLOB than BLOB. Even if you don't have to analyze the data within the database it might still make sense to use CLOB, because even viewing the data is easier.

Some features are only available using VARCHAR. For example, you can only create an index on VARCHAR columns (I'm not talking about fulltext index here, I know you can create a fulltext index on a CLOB column). You can't have a CLOB or BLOB primary key (I guess you don't need that; just as an example).

Most VARCHAR operations are much faster than CLOB / BLOB operations. Even reading data is faster if you use VARCHAR (unless there is really a lot of text in the column). VARCHAR needs less memory overhead, but they will usually be fully read in memory, so at the end VARCHAR might still use more memory.

Answer 2

They are both different.

You use BLOB to store binary data like an image, audio and other multimedia data.

and VARCHAR to store text of any size up to the limit.

Answer 3

If you're going to store text data that can't fit in a VarChar2 then I think you're supposed to use a CLOB.

Quote from OraFaq: *A CLOB (Character Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOB's are handy for storing text. *

Answer 4

Short strings → VARCHAR

Long strings → CLOB

Binary data → BLOB