컬럼 값 조회

설명

LOB 타입 컬럼을 조회하면 컬럼이 참조하는 파일에 저장된 데이터를 출력한다. CAST 연산자, CLOB_TO_CHAR( ) 함수, BLOB_TO_BIT( ) 함수를 사용하여 명시적 타입 변환을 수행할 수 있다.

참고 사항
예제

//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%'; --Error  

SELECT * FROM doc_t ORDER BY content; -- Error