DBMS_LOB Functions in Oracle

Managing Large Objects (LOBs) like text, images, audio, or video requires special tools. Oracle provides the DBMS_LOB package to work with these large objects efficiently. This package includes functions that allow developers to read, write, search, copy, and modify LOBs (CLOB, BLOB, and NCLOB).
Key DBMS_LOB Functions:
1. DBMS_LOB.GETLENGTH
DBMS_LOB.GETLENGTH function returns the length of a LOB in bytes (for BLOB) or characters (for CLOB/NCLOB). It’s useful when you want to find the size of the data in the LOB.
Example: Let’s assume you have a CLOB column document_content in the documents table. You want to find out the length of the content in a specific document (with document_id = 101).
SELECT DBMS_LOB.GETLENGTH(document_content) AS doc_length
FROM documents
WHERE document_id = 101;
This will return the length of the document_content LOB.

2. DBMS_LOB.INSTR
DBMS_LOB.INSTR function searches for a substring within a LOB. It returns the position of the substring or 0 if not found.
Example: Suppose you want to find the position of the word "Oracle" in the document_content LOB of the documents table for document_id = 101.
SELECT DBMS_LOB.INSTR(document_content, 'Oracle') AS position
FROM documents
WHERE document_id = 101;
This will return the position of the first occurrence of the word "Oracle" within the document_content LOB.

3. DBMS_LOB.COPY
DBMS_LOB.COPY function copies data from one LOB to another. This is useful for duplicating or transferring LOB data.
Example: You want to copy the document_content from one document to another in the same documents table.
DECLARE
  v_source_lob CLOB;
  v_dest_lob CLOB;
BEGIN
  -- Retrieve source LOB
  SELECT document_content
  INTO v_source_lob
  FROM documents
  WHERE document_id = 101;

  -- Retrieve destination LOB
  SELECT document_content
  INTO v_dest_lob
  FROM documents
  WHERE document_id = 102;

  -- Copy data from source to destination
  DBMS_LOB.COPY(v_dest_lob, v_source_lob, DBMS_LOB.GETLENGTH(v_source_lob));

  COMMIT;
END;
/
This copies the content from one LOB to another, ensuring the entire content is transferred.

4. DBMS_LOB.ERASE
DBMS_LOB.ERASE function erases a portion of a LOB. It’s useful when you need to delete part of the data from a LOB.
Example: Let’s say you want to erase the first 100 bytes of a BLOB stored in the image_data column of the images table.
DECLARE
  v_blob BLOB;
BEGIN
  -- Retrieve the BLOB
  SELECT image_data
  INTO v_blob
  FROM images
  WHERE image_id = 1001;

  -- Erase the first 100 bytes of the BLOB
  DBMS_LOB.ERASE(v_blob, 100);

  COMMIT;
END;
/
This will erase the first 100 bytes of the image_data BLOB.

5. DBMS_LOB.SUBSTR
DBMS_LOB.SUBSTR function allows you to extract a substring from a LOB. You can define the starting position and the length of the substring.
Example: If you want to retrieve the first 100 characters of a CLOB column document_content for document_id = 101:
SELECT DBMS_LOB.SUBSTR(document_content, 100, 1) AS first_100_chars
FROM documents
WHERE document_id = 101;
This extracts the first 100 characters from the document_content LOB starting from position 1.

6. DBMS_LOB.COMPARE
DBMS_LOB.COMPARE function compares two LOBs. It returns:
    A value less than 0 if the first LOB is smaller.
    0 if the LOBs are equal.
    A value greater than 0 if the first LOB is larger.
Example: You want to compare the document_content of two documents with document_id = 101 and document_id = 102.
SELECT DBMS_LOB.COMPARE(doc1.document_content, doc2.document_content) AS comparison_result
FROM documents doc1, documents doc2
WHERE doc1.document_id = 101 AND doc2.document_id = 102;
This compares the two document_content LOBs and returns the result.

7. DBMS_LOB.APPEND
DBMS_LOB.APPEND function appends one LOB to the end of another. It’s helpful when you want to add content to an existing LOB.
Example: Suppose you want to append content from one document (document_id = 103) to another (document_id = 101).
DECLARE
  v_additional_content CLOB;
  v_existing_content CLOB;
BEGIN
  -- Retrieve the additional content to append
  SELECT additional_content
  INTO v_additional_content
  FROM documents
  WHERE document_id = 103;

  -- Retrieve the existing content of document 101
  SELECT document_content
  INTO v_existing_content
  FROM documents
  WHERE document_id = 101;

  -- Append the additional content
  DBMS_LOB.APPEND(v_existing_content, v_additional_content);

  COMMIT;
END;
/
This appends the additional_content to the document_content of document 101.

8. DBMS_LOB.READ
 DBMS_LOB.READ function allows you to read a specified portion of a LOB. You can define the starting position and the number of bytes (for BLOBs) or characters (for CLOBs/NCLOBs) to read.
Example: Let’s read the first 50 bytes of a BLOB from the image_data column in the images table.

DECLARE
  v_image_data BLOB;
  v_read_data VARCHAR2(50);
BEGIN
  -- Retrieve the BLOB data
  SELECT image_data
  INTO v_image_data
  FROM images
  WHERE image_id = 1001;

  -- Read the first 50 bytes
  v_read_data := DBMS_LOB.READ(v_image_data, 50, 1);

  -- Output the read data (in hexadecimal format)
  DBMS_OUTPUT.PUT_LINE(v_read_data);
END;
/
This reads the first 50 bytes of the image_data BLOB starting from position 1.

9. DBMS_LOB.WRITEAPPEND
DBMS_LOB.WRITEAPPEND function allows you to append data to the end of a LOB, such as adding more text to an existing CLOB.
Example: Suppose you want to append the string "Additional data" to the document_content LOB for document_id = 101:
DECLARE
  v_append_data VARCHAR2(100) := 'Additional data';
  v_document_content CLOB;
BEGIN
  -- Retrieve the document content
  SELECT document_content
  INTO v_document_content
  FROM documents
  WHERE document_id = 101;

  -- Append the data
  DBMS_LOB.WRITEAPPEND(v_document_content, LENGTH(v_append_data), v_append_data);

  COMMIT;
END;
/
This appends the string "Additional data" to the document_content LOB.