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.

-- Check if a LOB column is NULL
SELECT doc_id
FROM documents
WHERE doc_text IS NULL;

-- Check if a LOB column is EMPTY
SELECT doc_id
FROM documents
WHERE doc_text IS EMPTY;

-- Check if a LOB column is populated
SELECT doc_id
FROM documents
WHERE doc_text IS NOT NULL AND doc_text IS NOT EMPTY;

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.

-- Check if a LOB column is EMPTY but has a non-zero length
SELECT doc_id
FROM documents
WHERE doc_text IS NOT NULL AND doc_text IS EMPTY AND LENGTH(doc_text) > 0;

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.

-- Insert a BLOB from a file
INSERT INTO images (image_id, image)
VALUES (1, BFILENAME('IMAGE_DIR', 'image1.jpg'));

-- Insert a CLOB from a string literal
INSERT INTO documents (doc_id, doc_text)
VALUES (1, 'This is the text of the document.');

-- Insert a NCLOB from a program variable
DECLARE
   doc_text CLOB;
BEGIN
   doc_text := 'WELCOME';
   INSERT INTO documents (doc_id, doc_text)
   VALUES (2, doc_text);
END;

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.

-- Update a BLOB with a file
UPDATE images
SET image = BFILENAME('IMAGE_DIR', 'image2.jpg')
WHERE image_id = 1;

-- Update a CLOB with a string literal
UPDATE documents
SET doc_text = 'This is the updated text of the document.'
WHERE doc_id = 1;

-- Update a NCLOB with a program variable
DECLARE
doc_text CLOB;
BEGIN
doc_text := 'WELCOME';
UPDATE documents
SET doc_text = doc_text
WHERE doc_id = 2;
END;

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.

-- Select a BLOB and write it to a file
SELECT image
INTO BFILENAME('IMAGE_DIR', 'image3.jpg')
FROM images
WHERE image_id = 1;

-- Select a CLOB and return it as a string
SELECT doc_text
FROM documents
WHERE doc_id = 1;

-- Select a NCLOB and return it as a string
SELECT doc_text
FROM documents
WHERE doc_id = 2;

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

-- Delete LOB data
DELETE FROM images
WHERE image_id = 1;

DELETE FROM documents
WHERE doc_id = 1;

DELETE FROM documents
WHERE doc_id = 2;

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.

-- Search a CLOB for a specific pattern
SELECT doc_id
FROM documents
WHERE doc_text LIKE '%specific pattern%';

-- Use the INSTR function to search a CLOB
SELECT doc_id
FROM documents
WHERE INSTR(doc_text, 'specific pattern') > 0;

-- Use the SUBSTR function to extract a portion of a CLOB
SELECT SUBSTR(doc_text, 1, 50)
FROM documents
WHERE doc_id = 1;

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.

-- Compare LOB data for equality
SELECT doc_id
FROM documents
WHERE doc_text = 'This is the text of the document.';

-- Use the COMPARE function to compare LOB data
SELECT doc_id
FROM documents
WHERE COMPARE(doc_text, 'This is the text of the document.') = 0;

Truncating LOB Data

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

-- Truncate a CLOB to 50 characters
UPDATE documents
SET doc_text = TRUNCATE(doc_text, 50)
WHERE doc_id = 1;

Copying LOB Data

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

-- Copy CLOB data from one row to another
DECLARE
   source_doc CLOB;
   target_doc CLOB;
BEGIN
   SELECT doc_text INTO source_doc FROM documents WHERE doc_id = 1;
   SELECT doc_text INTO target_doc FROM documents WHERE doc_id = 2;
   COPY(source_doc, target_doc);
   UPDATE documents SET doc_text = target_doc WHERE doc_id = 2;
END;

Concatenating LOB Data

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

-- Concatenate two CLOBs together
DECLARE
   doc1 CLOB;
   doc2 CLOB;
   doc3 CLOB;
BEGIN
   SELECT doc_text INTO doc1 FROM documents WHERE doc_id = 1;
   SELECT doc_text INTO doc2 FROM documents WHERE doc_id = 2;
   doc3 := CONCATENATE(doc1, doc2);
   INSERT INTO documents (doc_id, doc_text) VALUES (3, doc3);
END;

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.

Updated on: 10-Jan-2023

255 Views

Related Articles

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements