Search This Blog

04 January 2015

Large Objects for Databases

I am developing an application. I got stuck with a situation how to save my pictures. I walked down to my manager, who is an OCP, and asked him what should I do with my images? Where should I save them for my application? He gave a nasty smile, and said, "Bhaskar, Just flow with the Blob!"
I love Oracle DB engine over others. Although, as a basic developer like me, even Access or Excel would work, but Oracle mesmerizes me.  For this application, I chose Oracle over other MS SQL Server. And now I'm even more enlightened with 2 new data types. BLOB and CLOB. Psst, these aren't restricted to just Oracle DB Engine.

Character Large Objects (CLOB)

CLOBs are the large limit character data type for DB. Their limit may vary for DB Engines, but it is said that they're having limit of about  2GB. CLOB is said to be a collection of character data. It is stored at a separate location and is often referenced in a table. Being a data of this much limit, operation may end up slow on performance. Different DBM systems have different mechanism to overcome the performance issue. But, I'm sure, not all the developers will be using this datatype during their career. Hope not me!

Binary Large Objects (BLOB)

BLOB is a collection of binary objects stored as a single entity. This can be used to save multimedia objects. One can save images or audio to the DB by using BLOB. Oracle says "A BLOB without specified length is defaulted to two gigabytes".
BLOB and CLOB have two more siblings in Oracle DB, NCLOB and BFILE

National Character Large Object (NCLOB)

Very Similar to CLOB, with just a difference that it stores UNICODE national character set data.

BFILE

The BFILE datatype stores unstructured binary data in operating-system files outside the database. A BFILE column or attribute stores a file locator that points to an external file containing the data. The amount of BFILE data that can be stored is limited by the operating system. BFILEs are read only; you cannot modify them. They support only random (not sequential) reads, and they do not participate in transactions. 
I'll be trying BLOB on my application. It will be fun to store images to DB rather than keeping them on File Share.
Let's hope that all goes well with it.