TO_LOB

TO_LOB is an Oracle SQL function that is used to convert LONG or LONG RAW values to LOB values. It is typically used during data migration where it becomes necessary to change data types for efficient data management.

TO_LOB(source)Where “source” is a LONG value to be converted to a LOB value.

source: This parameter is the LONG value that will be converted into a LOB value. It represents the original data that is to be transformed. Any additional LONG database columns or LONG PL/SQL variables can act as the source. It accepts any LONG value and converts it into a LOB datatype.

Example

CREATE TABLE images(
  img_id NUMBER,
  img BFILE
);

INSERT INTO images VALUES(1, BFILENAME('IMAGES_DIR', 'img1.jpg'));

UPDATE images SET img = TO_LOB(img) WHERE img_id = 1;

Output

The SELECT img FROM images WHERE img_id = 1; query returns:

CLOB

Explanation

The TO_LOB function is used to convert a BFILE or BLOB datatype to a CLOB or NCLOB. In the example, an image stored as a BFILE is converted into CLOB format using the TO_LOB function.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.