Grundlegende Operationen und Funktionsweise von LOB

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;

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

DELETE FROM documents
WHERE doc_id = 1;

DELETE FROM documents
WHERE doc_id = 2;

-- 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;

-- 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;

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

-- 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;

-- 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;