I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value.
I tried using DBMS_LOB.substr(column) and i get the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
How can i query the CLOB column?
If it's a CLOB why can't we to_char the column and then search normally ?
Create a table
CREATE TABLE MY_TABLE(Id integer PRIMARY KEY, Name varchar2(20), message clob);
Create few records in this table
INSERT INTO MY_TABLE VALUES(1,'Tom','Hi This is Row one');
INSERT INTO MY_TABLE VALUES(2,'Lucy', 'Hi This is Row two');
INSERT INTO MY_TABLE VALUES(3,'Frank', 'Hi This is Row three');
INSERT INTO MY_TABLE VALUES(4,'Jane', 'Hi This is Row four');
INSERT INTO MY_TABLE VALUES(5,'Robert', 'Hi This is Row five');
COMMIT;
Search in the clob column
SELECT * FROM MY_TABLE where to_char(message) like '%e%';
Results
ID NAME MESSAGE
===============================
1 Tom Hi This is Row one
3 Frank Hi This is Row three
5 Robert Hi This is Row five
To add to the answer.
declare
v_result clob;
begin
---- some operation on v_result
dbms_lob.substr( v_result, 4000 ,length(v_result) - 3999 );
end;
/
In dbms_lob.substr
first parameter is clob which you want to extract .
Second parameter is how much length of clob you want to extract.
Third parameter is from which word you want to extract .
In above example i know my clob size is more than 50000 , so i want last 4000 character .