Oracle BLOB vs VARCHAR

Question

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

Which is the best to use: BLOB or VARCHAR ? Or maybe something else?

Answer 1

If you are going to store text data that cannot fit in VarChar2 , then I think you should use CLOB .

A quote from OraFaq : * CLOB (Large Large Object) is an Oracle data type that can contain up to 4 GB of data; CLOBs are convenient for storing text. *

Answer 2

Another option is CLOB. It is more logical to use CLOB for text data than BLOB. Even if you do not need to analyze the data in the database, it may make sense to use CLOB, because even viewing the data is easier.

Some functions are only available with VARCHAR. For example, you can only create an index for VARCHAR columns (I'm not talking about a full-text index here, I know that you can create a full-text index in a CLOB column). You cannot have a primary key CLOB or BLOB (I think you do not need this, as an example).

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

Answer 3

Short lines → VARCHAR

Long Strings -> CLOB

Binary data → BLOB

Answer 4

They are both different.

You use BLOB to store binary data such as images, audio, and other multimedia data.

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