Introduction to Oracle BLOB Data Type

Oracle BLOB is a binary large object data type used to store binary data such as images, audio, videos, etc.

Syntax

The syntax for BLOB data type is as follows:

BLOB

Use Cases

BLOB data type is suitable for storing binary data such as images, audio, videos, etc. Typically, these binary data are large in size and need to be stored and transmitted in binary form. Therefore, using BLOB data type allows efficient storage and processing of these large binary data.

Examples

Here are some examples of using BLOB data type:

Example 1

CREATE TABLE image_table (
  id NUMBER(10),
  image BLOB
);

INSERT INTO image_table (id, image) VALUES (1, empty_blob());

DECLARE
  v_blob BLOB;
BEGIN
  SELECT image INTO v_blob FROM image_table WHERE id = 1 FOR UPDATE;

  -- Read file and store it in BLOB
  DBMS_LOB.OPEN(v_blob, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(v_blob, 'image.jpg', DBMS_LOB.GETLENGTH(v_blob));
  DBMS_LOB.CLOSE(v_blob);
END;
/

SELECT id, dbms_lob.getlength(image) AS length FROM image_table;

Output:

 ID  LENGTH
---  -------
  1  50236

Example 2

CREATE OR REPLACE DIRECTORY data_dir AS 'C:.ata';

CREATE TABLE pdf_table (
  id NUMBER(10),
  pdf BLOB
);

INSERT INTO pdf_table (id, pdf) VALUES (1, empty_blob());

DECLARE
  v_blob BLOB;
BEGIN
  SELECT pdf INTO v_blob FROM pdf_table WHERE id = 1 FOR UPDATE;

  -- Read file and store it in BLOB
  DBMS_LOB.OPEN(v_blob, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(v_blob, 'data_dir.est.pdf', DBMS_LOB.GETLENGTH(v_blob));
  DBMS_LOB.CLOSE(v_blob);
END;
/

SELECT id, dbms_lob.getlength(pdf) AS length FROM pdf_table;