I need to search in a text column larger than 4KB. So, I changed the type of text column from VARCHAR2 to CLOB....
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
But I found that I can't search in CLOB columns. Is thare any way to do this? It will be better if I don't have to move this column to another table because all application Java EJB CMP are already created.
CLOBs require that you use the DBMS_LOB package to perform substr/instr type searches. In order to use do any kind of searching within the column, you must first get the locator for the CLOB column, the use the DBMS_LOB.SUBSTR or DBMS_LOB.INSTR function to search and/or pull part of the text from the string.
declare src_lob CLOB; pos INTEGER := 1; buf VARCHAR2(32000); BEGIN SELECT c_lob INTO src_lob FROM lob_table WHERE key_value = 21; buf := DBMS_LOB.SUBSTR(src_lob, 200, pos); -- and so on.... END;
Let's expand the concept with another example and review the details: First, we assume we have a table named pet_table defined as:
CREATE TABLE pet_table (pet_name varchar2(30) PRIMARY KEY, pet_info CLOB, pet_breed varchar2(30), pet_owner varchar2(50) );
Any LOB type column (CLOB, BLOB, NCLOB) cannot be retrieved and searched directly as LOB data columns simply store a pointer that must be used to obtain the actual column data. So, our next step is to SELECT from the pet_table and retrieve the row for the pet and retrieve the lob pointer (also called the lob locator). Then using that locator, we will execute the DBMS_LOB.SUBSTR function to pull back the first 200 characters of the actual lob data (stored at the location pointed to by the locator). DBMS_LOB.SUBSTR takes 3 parameters: 1) the lob locator pointing to the CLOB data value to retrieve 2) an integer indicating the amount of data to retrieve from the CLOB and 3) the starting position where SUBSTR should begin.
Our PL/SQL block can then be written as follows:
DECLARE src_lob_locator CLOB; pedigree_info VARCHAR2(200); BEGIN SELECT pet_info INTO src_lob_locator FROM pet_table WHERE pet_name = 'FIDO' ; pedigree_info := DBMS_LOB.SUBSTR(src_lob_locator, 200, 1); /* Put any code that you need to process the returned information here */ END;
SELECT substr(pet_info, 1, 200) FROM pet_table WHERE pet_name = 'FIDO';
INSTR works in a similar way as SUBSTR in that it needs to have a lob locator, except that using INSTR, you can find a specific pattern within the CLOB instead of just pulling back a specified amount of characters. The Oracle8i Supplied PL/SQL Packages Reference under DBMS_LOB has details and examples on using INSTR.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.