fabricio.de.marchi

GEL to read Oracle CLOB field value

Discussion created by fabricio.de.marchi on Oct 12, 2010
Latest reply on Oct 12, 2010 by Dave
Hi All,

I am trying to read a CLOB field from an oracle database.

this is the script I am using:
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql"
xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<gel:setDataSource dbId="Niku"/>

<!-- Query to get host information -->
<sql:query var="get_host_info">
select value from app_niku.cmn_config where name = 'properties.xml'
</sql:query>

<!-- Loop through the SDLC Project Questions -->
<core:forEach items="${get_host_info.rowsByIndex}" trim="true" var="row_get_host_info">
<core:set value="${row_get_host_info[0]}" var="properties_xml" />
</core:forEach>

<core:set var="startToken" value='schedulerUrl="' />
<core:set var="endToken" value='"' />
<core:set var="firstPos" value="${properties_xml.indexOf(startToken)+startToken.length()}" />
<core:set var="secondPos" value="${firstPos+(properties_xml.substring(firstPos).indexOf(endToken))}"/>
<core:set var="hosta" value="${properties_xml.substring(firstPos, secondPos)}"/>

<sql:update>
insert into dell_app_niku.dell_debug_logs (debug_type, log_timestamp, error_message)
values ('TEST_GEL_SUBSTRING',TO_DATE('20/07/2919','DD/MM/YYYY'),'${properties_xml.substring(1,3999)}');
</sql:update>

</gel:script>
Right now the insert command errors out and the substring is empty, like that CLOB had no value.

The thing is if I put change the insert to this:
insert into dell_app_niku.dell_debug_logs (debug_type, log_timestamp, error_message)
values ('TEST_GEL_SUBSTRING',TO_DATE('20/07/2919','DD/MM/YYYY'),'${properties_xml.length()}');
It does show the size of that CLOB, something like 10500.

So, it seems to me that the CLOB have value but the substring can't get it.

I've checked and the 'properties_xml' value is of the "com.ca.clarity.jdbc.base.BaseClob" type and that class implements java.sql.Clob. When I open that class I see that it does have a substring and it should be working.

Anyone knows a way to get the CLOB value?

Thanks,

Fabricio

Outcomes