Erase BLOB Bytes in Oracle 26ai - Exercise

Objective: Use a persistent BLOB locator to erase a byte range, verify the value, and undo the change.

This is a 10-point self-assessment. Submit your SQL and explanation to compare them with the model answer. This website does not connect to Oracle or execute submitted code.

Prepare a practice session

Use your own Oracle AI Database 26ai practice schema with CREATE TABLE permission, tablespace quota, and access to DBMS_LOB. Use SQL*Plus, SQLcl, or a compatible script runner. Disable autocommit and use the same session throughout. Start with no unrelated uncommitted work: setup includes DDL and COMMIT.

Create this dedicated table once. The BLOB contains five known bytes, not a real photograph. If this table already exists, use a fresh practice schema or your existing unchanged practice row; do not overwrite unrelated data.

CREATE TABLE lesson9_erase_practice (
  product_id NUMBER PRIMARY KEY,
  payload BLOB
);
INSERT INTO lesson9_erase_practice (product_id, payload)
VALUES (901, HEXTORAW('1122334455'));
COMMIT;

Confirm the starting value with this query. Expect product 901, byte length 5, and hexadecimal 1122334455.

SELECT product_id,
       DBMS_LOB.GETLENGTH(payload) AS byte_length,
       RAWTOHEX(DBMS_LOB.SUBSTR(payload, 5, 1)) AS hex_bytes
FROM lesson9_erase_practice
WHERE product_id = 901;

Your tasks

Obtain row 901's BLOB locator and lock the row for modification.
Erase two bytes starting at offset 2. Supply a variable for the amount and report its value after the call. BLOB offsets start at 1.
Run the verification query before committing or rolling back. Record the byte length, hexadecimal bytes, and whether the row still exists.
Roll back, then query again to confirm restoration. Include exception handling that rolls back to a savepoint and re-raises errors.
Explain how erasing bytes differs from shortening a BLOB with TRIM and removing a row with DELETE.

Optional visual hint: reveal the expected byte changes

BLOB positions 2 and 3 change from hexadecimal 22 and 33 to 00 and 00. The five-byte value changes from 1122334455 to 1100004455; its length remains five bytes. — Illustrative expected result: erase two bytes starting at position 2, inspect the unchanged five-byte length, then roll back.

Positions 2 and 3 become zero bytes; positions 1, 4, and 5 stay unchanged. The amount returned is 2 and the length stays 5.

Self-assessment rubric: 10 points

2 points: Select row 901 into a BLOB locator and lock it with FOR UPDATE.
2 points: Use a writable amount variable initialized to 2 and offset 2; display the actual amount returned.
2 points: Verify hexadecimal 1100004455, length 5, and the retained row before rollback.
2 points: Roll back and verify 1122334455; propagate unexpected errors after undoing this block's work.
2 points: Explain how ERASE, TRIM, and DELETE differ.

Reference: Oracle AI Database 26ai DBMS_LOB documentation.