Basic operations and Working of LOB

LOB, or Large OBject, is a data type in database management systems (DBMS) used to store large amounts of unstructured data, such as text, images, and videos. LOB data types are useful for storing and manipulating data that does not fit neatly into a traditional row-and-column structure, such as documents, graphics, or audio files.

In this article, we will explore the basic operations and working of LOB data types in DBMS and SQL. We will also provide examples of how to use LOB data types in SQL for storing and manipulating large amounts of unstructured data.

Types of LOB Data

There are several types of LOB data, including ?

• BLOB (Binary Large OBject) ? A BLOB is a collection of binary data, such as images, audio, or video files. BLOB data is stored as a sequence of bytes and does not have a specific character set.

• CLOB (Character Large OBject) ? A CLOB is a collection of character data, such as text documents or HTML files. CLOB data is stored as a sequence of characters and has a specific character set, such as UTF-8 or ASCII

• NCLOB (National Character Large OBject) ? A NCLOB is similar to a CLOB, but it is used to store character data in a national character set, such as Chinese, Japanese, or Korean.

Working of LOB Data Types

LOB data types are stored in a special area of the database called the LOB storage area. This allows LOB data to be stored and accessed separately from the rest of the database, which can improve performance and efficiency when working with large amounts of unstructured data.

LOB data is accessed using a pointer, which is a reference to the location of the LOB data in the LOB storage area. The pointer is stored in the database along with the rest of the data, but the actual LOB data is stored in the LOB storage area. This allows the database to access the LOB data quickly and efficiently without having to store the entire LOB in the database itself.

LOB Column States

LOB columns can be in one of three states ?

• NULL ? The LOB column contains no data.

• EMPTY ? The LOB column contains no data and has a length of zero.

• Populated ? The LOB column contains data and has a length greater than zero.

The state of a LOB column can be determined using the IS NULL and IS EMPTY predicates.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Check <span class="token keyword">if</span> a LOB column is <span class="token constant">NULL</span>
SELECT doc_id
FROM documents
WHERE doc_text IS <span class="token constant">NULL</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Check <span class="token keyword">if</span> a LOB column is EMPTY
SELECT doc_id
FROM documents
WHERE doc_text IS EMPTY<span class="token punctuation">;</span>

<span class="token operator">--</span> Check <span class="token keyword">if</span> a LOB column is populated
SELECT doc_id
FROM documents
WHERE doc_text IS NOT <span class="token constant">NULL</span> AND doc_text IS NOT EMPTY<span class="token punctuation">;</span>
</div>

It is important to note that a LOB column can be in the EMPTY state even if it has a non-zero length. This can occur if the LOB column contains only whitespace or control characters. To check for this, you can use the LENGTH function to determine the actual length of the LOB data.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Check <span class="token keyword">if</span> a LOB column is EMPTY but has a non<span class="token operator">-</span>zero length
SELECT doc_id
FROM documents
WHERE doc_text IS NOT <span class="token constant">NULL</span> AND doc_text IS EMPTY AND <span class="token function">LENGTH</span><span class="token punctuation">(</span>doc_text<span class="token punctuation">)</span> <span class="token operator">><font color="#000000"> </font></span><span class="token number">0</span><span class="token punctuation">;</span>
</div>

Basic Operations on LOB Data

There are several basic operations that can be performed on LOB data in SQL, including ?

Inserting LOB data ? LOB data can be inserted into a database using the INSERT statement. The LOB data can be specified as a string literal, a file, or a program variable.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Insert a BLOB from a file
INSERT INTO <span class="token function">images</span> <span class="token punctuation">(</span>image_id<span class="token punctuation">,</span> image<span class="token punctuation">)</span>
<span class="token function">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token function">BFILENAME</span><span class="token punctuation">(</span><span class="token string">'IMAGE_DIR'</span><span class="token punctuation">,</span> <span class="token string">'image1.jpg'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Insert a CLOB from a string literal
INSERT INTO <span class="token function">documents</span> <span class="token punctuation">(</span>doc_id<span class="token punctuation">,</span> doc_text<span class="token punctuation">)</span>
<span class="token function">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'This is the text of the document.'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Insert a NCLOB from a program variable
DECLARE
   doc_text CLOB<span class="token punctuation">;</span>
BEGIN
   doc_text <span class="token operator">:</span><span class="token operator">=</span> <span class="token string">'WELCOME'</span><span class="token punctuation">;</span>
   INSERT INTO <span class="token function">documents</span> <span class="token punctuation">(</span>doc_id<span class="token punctuation">,</span> doc_text<span class="token punctuation">)</span>
<span class="token function">   VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> doc_text<span class="token punctuation">)</span><span class="token punctuation">;</span>
END<span class="token punctuation">;</span>
</div>

Updating LOB data ? LOB data can be updated using the UPDATE statement. The LOB data can be specified as a string literal, a file, or a program variable.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Update a BLOB with a file
UPDATE images
SET image <span class="token operator">=</span> <span class="token function">BFILENAME</span><span class="token punctuation">(</span><span class="token string">'IMAGE_DIR'</span><span class="token punctuation">,</span> <span class="token string">'image2.jpg'</span><span class="token punctuation">)</span>
WHERE image_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Update a CLOB with a string literal
UPDATE documents
SET doc_text <span class="token operator">=</span> <span class="token string">'This is the updated text of the document.'</span>
WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Update a NCLOB with a program variable
DECLARE
doc_text CLOB<span class="token punctuation">;</span>
BEGIN
doc_text <span class="token operator">:</span><span class="token operator">=</span> <span class="token string">'WELCOME'</span><span class="token punctuation">;</span>
UPDATE documents
SET doc_text <span class="token operator">=</span> doc_text
WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
END<span class="token punctuation">;</span>
</div>

Selecting LOB data ? LOB data can be retrieved from the database using the `SELECT` statement. The LOB data can be returned as a string or written to a file.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Select a BLOB <span class="token operator">and</span> write it to a file
SELECT image
INTO <span class="token function">BFILENAME</span><span class="token punctuation">(</span><span class="token string">'IMAGE_DIR'</span><span class="token punctuation">,</span> <span class="token string">'image3.jpg'</span><span class="token punctuation">)</span>
FROM images
WHERE image_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Select a CLOB <span class="token operator">and</span> <span class="token keyword">return</span> it as a string
SELECT doc_text
FROM documents
WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Select a NCLOB <span class="token operator">and</span> <span class="token keyword">return</span> it as a string
SELECT doc_text
FROM documents
WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
</div>

Deleting LOB data ? LOB data can be deleted from the database using the DELETE statement.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Delete LOB data
DELETE FROM images
WHERE image_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

DELETE FROM documents
WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

DELETE FROM documents
WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
</div>

Advanced Operations on LOB Data

In addition to the basic operations described above, there are also several advanced operations that can be performed on LOB data in SQL.

Searching LOB Data

The LIKE operator can be used to search for specific patterns within LOB data. The DBMS_LOB package also provides several functions for searching and manipulating LOB data.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Search a CLOB <span class="token keyword">for</span> a specific pattern
SELECT doc_id
FROM documents
WHERE doc_text LIKE <span class="token string">'%specific pattern%'</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Use the INSTR function to search a CLOB
SELECT doc_id
FROM documents
WHERE <span class="token function">INSTR</span><span class="token punctuation">(</span>doc_text<span class="token punctuation">,</span> <span class="token string">'specific pattern'</span><span class="token punctuation">)</span> <span class="token operator">></span> <span class="token number">0</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Use the SUBSTR function to extract a portion of a CLOB
SELECT <span class="token function">SUBSTR</span><span class="token punctuation">(</span>doc_text<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span>
FROM documents
WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
</div>

Comparing LOB Data

The = operator can be used to compare LOB data for equality. The DBMS_LOB package also provides the COMPARE function for comparing LOB data.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Compare LOB data <span class="token keyword">for</span> equality
SELECT doc_id
FROM documents
WHERE doc_text <span class="token operator">=</span> <span class="token string">'This is the text of the document.'</span><span class="token punctuation">;</span>

<span class="token operator">--</span> Use the COMPARE function to compare LOB data
SELECT doc_id
FROM documents
WHERE <span class="token function">COMPARE</span><span class="token punctuation">(</span>doc_text<span class="token punctuation">,</span> <span class="token string">'This is the text of the document.'</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
</div>

Truncating LOB Data

The DBMS_LOB package provides the TRUNCATE function for truncating LOB data to a specific length.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Truncate a CLOB to <span class="token number">50</span> characters
UPDATE documents
SET doc_text <span class="token operator">=</span> <span class="token function">TRUNCATE</span><span class="token punctuation">(</span>doc_text<span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span>
WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
</div>

Copying LOB Data

The DBMS_LOB package provides the COPY function for copying LOB data from one LOB to another.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Copy CLOB data from one row to another
DECLARE
   source_doc CLOB<span class="token punctuation">;</span>
   target_doc CLOB<span class="token punctuation">;</span>
BEGIN
   SELECT doc_text INTO source_doc FROM documents WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
   SELECT doc_text INTO target_doc FROM documents WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
   <span class="token function">COPY</span><span class="token punctuation">(</span>source_doc<span class="token punctuation">,</span> target_doc<span class="token punctuation">)</span><span class="token punctuation">;</span>
   UPDATE documents SET doc_text <span class="token operator">=</span> target_doc WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
END<span class="token punctuation">;</span>
</div>

Concatenating LOB Data

The DBMS_LOB package provides the CONCATENATE function for concatenating two LOBs together.

<div class="code-mirror  language-cpp" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token operator">--</span> Concatenate two CLOBs together
DECLARE
   doc1 CLOB<span class="token punctuation">;</span>
   doc2 CLOB<span class="token punctuation">;</span>
   doc3 CLOB<span class="token punctuation">;</span>
BEGIN
   SELECT doc_text INTO doc1 FROM documents WHERE doc_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
   SELECT doc_text INTO doc2 FROM documents WHERE doc_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span>
   doc3 <span class="token operator">:</span><span class="token operator">=</span> <span class="token function">CONCATENATE</span><span class="token punctuation">(</span>doc1<span class="token punctuation">,</span> doc2<span class="token punctuation">)</span><span class="token punctuation">;</span>
   INSERT INTO <span class="token function">documents</span> <span class="token punctuation">(</span>doc_id<span class="token punctuation">,</span> doc_text<span class="token punctuation">)</span> <span class="token function">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> doc3<span class="token punctuation">)</span><span class="token punctuation">;</span>
END<span class="token punctuation">;</span>
</div>

Conclusion

In this article, we have explored the basic operations and working of LOB data types in DBMS and SQL. LOB data types are useful for storing and manipulating large amounts of unstructured data, such as text, images, and videos. We have also provided examples of how to use LOB data types in SQL for storing, updating, selecting, and deleting large amounts of unstructured data, as well as for performing advanced operations such as searching, comparing, truncating, copying, and concatenating LOB data.