Retrieving long text (CLOB) using CFQuery

Question

I am using CFQuery to retrieve the CLOB field from Oracle DB. If the CLOB filed contains the Data less than ~ 8000, then I can see <CFQuery > retrieved the value (<cfdump> the o/p), however If the value in CLOB field size is more than 8000 chars, then its not retrieving the value. in <cfdump> i can see the query retrieved as 'empty String' though the value exists in Oracle DB.

I am using the Oracle Driver in CFadim console, enabled 'Enable long text retrieval (CLOB).' and 'Enable binary large object retrieval (BLOB). '

Set 'Long Text Buffer (chr)' and 'Blob Buffer(bytes) ' values to 6400000

Any suggestions to retrieve the full text?

Answer 1

Did you enable CLOB fields in the datasource configuration? If not make sure you do.

1. Go to the coldfusion administrator
2. select datasources
3. edit your datasource
4. click on advanced settings
5. check the "enable clob"
6. save datasource

You shouldn't need to restart CF for this.

Answer 2

Don't know if you find the solution for your problem but for me it's a driver issue: then you need to update the jdbc driver. Look at this technote Updated DataDirect JDBC drivers (version 3.4)