Menu
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?
669
A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
In Oracle there are several different kinds of LOBs: BLOB (Binary Large Object) datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. They are often used for storing multimedia, like images, audio, and video.
The difference between the 2 types is that in VARCHAR2 you have to specify the length of the stored string and that length is limited to 4000 characters while CLOB can store up to 128 terabytes of character data in the database.
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.
174
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. *
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
