When you get a LOB type column, the data stored in a file to which the column refers will be displayed. You can execute an explicit type change by using CAST operator, CLOB_TO_CHAR( ) function, and BLOB_TO_BIT( ) function.
-- displaying locator value when selecting CLOB and BLOB column in CSQL interpreter
SELECT doc_t.doc_id, content, image FROM doc_t, image_t WHERE doc_t.doc_id = image_t.doc_id;
doc_id content image
==================================================================
'doc-1' file:/home1/data1/ces_658/doc_t.00001282208855807171_7329 file:/ home1/data1/ces_318/image_t.00001282208855809474_7474
'doc-2' file:/home1/data1/ces_180/doc_t.00001282208854194135_5598 file:/
home1/data1/ces_519/image_t.00001282208854205773_1215
2 rows selected.
-- using string functions after coercing its type by CLOB_TO_CHAR( )
SELECT CLOB_TO_CHAR(content), SUBSTRING(CLOB_TO_CHAR(content), 10) FROM doc_t;
clob_to_char(content) substring( clob_to_char(content) from 10)
============================================
'This is a Dog' ' Dog'
'This is a Cat' ' Cat'
2 rows selected.
SELECT CLOB_TO_CHAR(content) FROM doc_t WHERE CLOB_TO_CHAR(content) LIKE '%Dog%';
clob_to_char(content)
======================
'This is a Dog'
SELECT CLOB_TO_CHAR(content) FROM doc_t ORDER BY CLOB_TO_CHAR(content)
clob_to_char(content)
======================
'This is a Cat'
'This is a Dog'
-- an error occurs when LOB column specified in WHERE/ORDER BY/GROUP BY clauses
SELECT * FROM doc_t WHERE content LIKE 'This%';
SELECT * FROM doc_t ORDER BY content;