Support getClob()

[ComLock]

I have a column which is a CLOB.

In the sql lib query result I only get a reference to the CLOB not the actual characters.

This article hints at using getClob() to get the actual characters: https://docs.oracle.com/cd/A97335_02/apps.102/a83724/oralob2.htm

I could not find anything in the code https://github.com/enonic/lib-sql/search?utf8=%E2%9C%93&q=getClob&type=

I sorta need this (or other ways of achieving the same result) to finish up a certain project...

[ComLock]

Perhaps this: SELECT CAST(ClobColumnName AS VARCHAR(50)) AS ClobColumnName ;

Found here: https://stackoverflow.com/questions/15377158/how-to-show-clob-type-in-a-select-in-sql-server

[ComLock]

The reason I need the CLOB in the first place is the column exceeds the limits.

So if I do CAST(ClobColumnName AS VARCHAR(4000)) I may loose some data. So getClob() would be better.

As a workaround I could "split" the column into 2 or more. Which would allow me 8K, 12K and so on. But It may slow the query, which is already really slow.

ORACLE Datatype Limits https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm#i287903

[ComLock]

No luck with the workaround so far:

ERROR com.enonic.xp.resource.ResourceProblemException: Exception thrown while attempting to traverse the result set at com.enonic.xp.web.impl.exception.ExceptionMapperImpl.map(ExceptionMapperImpl.java:32) at com.enonic.xp.trace.Tracer.trace(Tracer.java:56) at com.enonic.xp.resource.ResourceProblemException$Builder.build(ResourceProblemException.java:131) at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1351) Caused by: java.sql.SQLException: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6407, maximum: 4000) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

I guess if I cast to varchar 2400 (6407-4000=2407) I might be in luck.

Or change the buffer size if possible.

[ComLock]

Trying

select dbms_lob.substr(ClobColumnName, 4000, 1) as part1,
dbms_lob.substr(ClobColumnName, 4000, 4001) as part2

now.

[ComLock]

That worked :) So I have a workaround.