Getting CLOB data from ColdFusion 8

Question

I am trying to retrieve CLOB data from our Oracle database. the code is the following:

<cfstoredproc datasource="#request.site.datasource#" procedure="GETPAGESWITHMETADATA" result="myResults">
    <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="in" value="News">
    <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="in" value="News Pages">
    <cfprocparam cfsqltype="CF_SQL_CLOB" type="out" variable="XML">
    <cfprocresult name="rs1">
</cfstoredproc>
<cfdump var="#myResults#">
<cfoutput>#XML#</cfoutput>
<cfcatch type="any">
    <cfdump var="#cfcatch#">
</cfcatch>
</cftry>

Basically, the output of the stored procedure is:

select dbms_xmlquery.getxml(queryCtx) INTO XML from dual;

I checked the data sources on the server and the "Enable long text retrieval (CLOB)." option is checked for every data source.

Surprisingly, instead of getting the XML result on screen, I get a very short string: [C@74897f5e

It looks like a handle id instead of the content itself.

How can I retrieve the complete content of the XML?

For reference, the data source is using macromedia drivers with TNS name: Driver class: macromedia.jdbc.MacromediaDriver

Just checking - you are expected this var in the "OUT" var clled "XML" right? try putting it in a dataset instead... I have terrible luck with out variables getting them right. I have to tweak the SP itself for order and such - the variable name is ignored inside the SP — Mark A Kruger, Jun 15 '12 at 16:43
I cannot use a dataset. The whole purpose of this is to return structured data in order to avoid multiple calls to the database. — E. Jaep, Jun 15 '12 at 18:35
The xml is a out parameter and the call works properly in sql developer — E. Jaep, Jun 15 '12 at 18:35
I'm aware that it's proper... and you don't need multiple calls... just do something Like create table #blah (xml text) then insert into #blah (xml) ... the select xml from #blah - so that the driver returns your clob AS a dataset... once in CF you can simply do parseXML(resultname.xml). — Mark A Kruger, Jun 16 '12 at 15:24
@MarkAKruger Hi. It definitely looks like something interesting. Would you have any sample code of a Stored Proc doing this? — E. Jaep, Jun 18 '12 at 12:01

score 1 · Accepted Answer · answered Jun 20 '12 at 14:50

As @MarkAKruger suggested, returning a table from the procedure solved the issue. The following PL/SQL code did the trick:

create or replace
PACKAGE PCK_Commonspot
AS
type t_clob IS record (metadata CLOB) ;
type t_clob_tab IS TABLE OF t_clob;
FUNCTION GetPagesWithMetadataAsRS(FormName varchar2, CategoryName varchar2)
    RETURN t_clob_tab pipelined;
END PCK_Commonspot;

The package body contains the following code:

FUNCTION GetPagesWithMetadataAsRS(FormName varchar2, CategoryName varchar2)
    RETURN t_clob_tab pipelined
IS
    r t_clob;
 BEGIN
    GETPAGESWITHMETADATA(FormName, CategoryName, r.metadata) ;
    pipe row(r) ;
    RETURN;
END;

The function GETPAGESWITHMETADATA is the one returning a CLOB into r.metadata Here, the trick is around returning a piped table.

It becomes super nice on the ColdFusion side because the call is really simple:

<cfquery name="Test" datasource="myDS" maxrows="1">
    SELECT * FROM TABLE(PCK_Commonspot.GetPagesWithMetadataAsRS('abc','def'))
</cfquery>
<cfset XML = Xmlparse(Test.Metadata)>

Thanks Mark!

score 0 · Answer 2 · answered Jun 15 '12 at 16:43

0

Check your datasource settings in the CF Administrator. Under the advanced settings, there are check boxes to allow BLOB and CLOB data to be returned. If they are unchecked you potentially could get truncated data returned in your queries

answered Jun 15 '12 at 16:43

E Paiz

661
4
9
19

OP says he has "Enable long text retrieval (CLOB)" on. – ale Jun 15 '12 at 17:20

score 0 · Answer 3 · answered Sep 04 '12 at 06:10

Even though Jaepetto has already answered his question, I thought I'd contribute another answer for posterity.

I was having similar problems returning CLOB data into CF8 from Oracle 11g. The original solution (that wasn't working) was roughly:

<cfquery name="GetDoc" DATASOURCE=myDS>
SELECT CLOBDATA FROM FILES WHERE FILES.FILEID = #FileID#
</cfquery>

This query would complete successfully, but GetDoc.CLOBDATA would always be an empty string. It turned out that retrieving CLOB data using cfquery would always do this, but if I wrapped it up in a stored proc then it worked. I assume this is some quirk of the JDBC drivers we're using.

Anyway, the CF guts of my solution was as follows. Note the use of CF_SQL_LONGVARCHAR instead of CF_SQL_CLOB - using CF_SQL_CLOB gave me the weird handle ID value that Jaepetto was seeing.

<cfstoredproc PROCEDURE="GETCLOB" DATASOURCE=myDS >
<CFPROCPARAM    TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER" DBVARNAME="pFileID" value="#fileID#"/> 
<CFPROCPARAM    TYPE="OUT" CFSQLTYPE="CF_SQL_LONGVARCHAR" DBVARNAME="pClob" VARIABLE="vClob" /> 
</cfstoredproc>

<!--- Dump the clob to the local filesystem --->
<cfscript>
fstream = CreateObject("java", "java.io.FileOutputStream").init(filepath, JavaCast("boolean","true"));
outStream = CreateObject("java", "java.io.BufferedOutputStream").init(fstream);

outStream.write(#toBinary(vClob)#);
outStream.flush();
outStream.close();
</cfscript>

Getting CLOB data from ColdFusion 8

3 Answers3

Linked