Oracle BLOB vs VARCHAR

Question

Hi.

I need to store in a column of a table a SQL query (it's going to be a large one) and I thought using a BLOB field.

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

Thanks for any ideas.

C.C.

Answer 1

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 2

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 3

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 4

Short strings -> VARCHAR

Long strings -> CLOB

Binary data -> BLOB