In ColdFusion MX, when returning a CLOB as an OUT parameter from a stored procedure (SP) it may be returned as an array of characters rather than a contiguous string. This occurs when specifying the CFPROCPARAM cfsqltype of the OUT parameter as CF_SQL_CLOB in the CFML that calls the SP when using CFMX 6.1 connecting to Oracle and using the DataDirect 3.3 JDBC Oracle driver.


If the SP remains the same on the database while just changing the cfsqltype to CF_SQL_VARCHAR in the CFML code, then the CLOB string is returned as expected.


An example of the solution CFML and the SP that it calls follows:

CFML

[cfparam name="URL.stringLength" default="32" />


[cfstoredproc procedure="LargeStringClobP" datasource="#dsn#">


[cfprocparam type="In" value="#URL.stringLength#" cfsqltype="CF_SQL_INTEGER" />


[cfprocparam type="Out" variable="largeString" cfsqltype="CF_SQL_VARCHAR" maxlength="32000" />


[/cfstoredproc>


[cfdump var="#largeString#"/>


Stored Procedure
CREATE OR REPLACE PROCEDURE LargeStringClobP(


pLength in integer,


pString out clob


)


IS


BEGIN


DBMS_LOB.CREATETEMPORARY (pString,FALSE,DBMS_LOB.CALL);


for idx IN 1 .. FLOOR(pLength/2)


loop


dbms_lob.writeappend(pString, 2, 'A.');


end loop;


if mod(pLength, 2) = 1


then


dbms_lob.writeappend(pString, 1, 'D');


end if;


END LargeStringClobP;


/