How to query a CLOB column in Oracle

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?

Query xml using extract over CLOB column in Oracle

We are storing the xml in oracle database. The column type is CLOB. I could fetch data from the xml tag with below query. When i execute the query i am getting null. XML in the column: <input xmlns

indexing clob column in oracle using solr

I am unable to index the clob clumn using my solr. I tried using clob transformer , but it doesn’t work. ANy idea on how to index a clob column in oracle using solr indexing?

Querying oracle clob column

I have a table with a clob column. Searching based on the clob column content needs to be performed. However select * from aTable where aClobColumn = ‘value’; fails but select * from aTable where aC

How to change column varchar to clob in oracle

I have a column details designed as varchar in oracle db, this DB is really using now for customer and some rows has ready stored. Now I want change column details to Clob column, Is everybody Hav

How to improve performance while update column Clob in oracle?

I used the following codes to update column Clob in oracle, it seems to be okay and work properly, after performance testing, it reported that need consumed more than 200ms while the length of string

How to get size in bytes of a CLOB column in Oracle?

How do i get size in bytes of a CLOB column in Oracle? LENGTH and DBMS_LOB.getLength both return number of characters used in the CLOB, but i need to know how many bytes are used (i’m dealing with mul

Oracle DB sql clob column CONCAT performance

I am seeking advice on performance of concat operation for CLOB column. I have job which updates the clob column ‘N’ times (configurable) by executing the following query, every time we see performanc

how to update specific attribute in xml of clob column in oracle

I have a CLOB column, where I need to update the value of one of the attribute in the xml(CLOB). For example <discounts> <code>ZSP3M</code> <description>50% discount 3 months&

Update Clob column in Oracle

I have an table with name ABC which has CLOB data. I want to update the column to insert string in specific position in this Clob column. String to be inserted : <nv_pair> <name identifier=

Java: How to insert CLOB into oracle database

I need to write a xml file content into oracle database where the column is of CLOB datatype. How will I do that? Thanks.

Answers

If you are using SQL*Plus try the following…

set long 8000

select ...

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 DMBS_LOB.substr command you can also specify the amount of characters you want to return and the offset from which. So using DMBS_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;

This works

select DBMS_LOB.substr(myColumn, 3000) from myTable

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);

For big CLOB selects also can be used:

SELECT dbms_lob.substr( column_name, dbms_lob.getlength(column_name), 1) FROM foo