Create_CLOB_BLOB1.htm

CLOB  :  single-byte character data.
NCLOB : multi-byte or single-byte fixed length character data that corresponds to the national character set.
BLOB : "** The BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. " The data is consisting of RAW binary data, such as bitmap images.
BFILE : The data is stored in an operating system file. Only a reference to the
file is stored in the database. This is the example for External LOB.
 

**~ http://www-css.fnal.gov/dsg/external/oracle_dcm/v11.2.0.2/server.112/e17110/statviews_1130.htm
Script:

CREATE TABLE testlob1 (
key NUMBER,
description varchar2(50),
test_clob1 CLOB,
test_blob1 BLOB);
 

Insert Script:

INSERT INTO testlob1 (key,description, test_clob1, test_blob1)
VALUES (1001,'CLOB BLOB large Object1', 'This item belongs to John Doe', HEXTORAW('15Ad34Bc'));
INSERT INTO testlob1 (key,description, test_clob1, test_blob1)
VALUES (1002,'CLOB BLOB large Object2', 'This object belongs to Long John', utl_raw.cast_to_raw('SampleString'));
Note this error

It is not possible to display the value of BLOB/CLOB column using SELECT command in
SQL*PLUS.
This will extract string form blob

select key, description, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(test_blob1, 1000,1)) test
from testlob1 where key= 1002;
Using extract values from  clob/blob

select key, description,
DBMS_LOB.substr(test_clob1, 1000) clob1,
DBMS_LOB.substr(test_blob1, 1000) blob1
from testlob1 where key= 1002;
Using IN operator:

select key, description,
DBMS_LOB.substr(test_clob1, 1000) clob1,
DBMS_LOB.substr(test_blob1, 1000) blob1
from testlob1 where key in( 1001, 1002)
The above results were reproduced in the next document using PHP script.