How to query a CLOB column in Oracle

Question

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

User · Answer

I did run into another condition with HugeClob in my Oracle database. The dbms_lob.substr only allowed a value of 4000 in the function, ex:

dbms_lob.substr(column,4000,1)

so for my HughClob which was larger, I had to use two calls in select:

select dbms_lob.substr(column,4000,1) part1, 
       dbms_lob.substr(column,4000,4001) part2 from .....

I was calling from a Java app so I simply concatenated part1 and part2 and sent as a email.

User · Answer

For big CLOB selects also can be used   SELECT dbms lob substr  column name  dbms lob getlength column name   1  FROM foo

User · Answer

When getting the substring of a CLOB column and using a query tool that has size buffer restrictions sometimes you would need to set the BUFFER to a larger size  For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000   Running the DBMS LOB substr command you can also specify the amount of characters you want to return and the offset from which  So using DBMS LOB substr column  3000  might restrict it to a small enough amount for the buffer   See oracle documentation for more info on the substr command        DBMS LOB SUBSTR          lob loc     IN    CLOB   CHARACTER SET ANY CS         amount      IN    INTEGER    32767         offset      IN    INTEGER    1        RETURN VARCHAR2 CHARACTER SET lob loc CHARSET

User · Answer

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

User · Answer

Another option is to create a function and call that function everytime you need to select clob column     create or replace function clob to char func  clob column in CLOB   for how many bytes in NUMBER   from which byte in NUMBER  return VARCHAR2 is begin Return substrb dbms lob substr clob column                              for how many bytes                              from which byte               1              for how many bytes   end    and call that function as   SELECT tocharvalue  clob to char func tocharvalue  1  9999  FROM  SELECT clob column AS tocharvalue FROM table name

User · Answer

This works  select DBMS LOB substr myColumn  3000  from myTable

User · Answer

If you are using SQL Plus try the following     set long 8000  select

User · Answer

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

[sql] How to query a CLOB column in Oracle

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to clob

Examples related to ora-06502