从ColdFusion 8获取CLOB数据 [英] Getting CLOB data from ColdFusion 8

问题描述

我正在尝试从Oracle数据库中检索CLOB数据。代码如下：

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;

我检查了服务器上的数据源和启用长文本检索（CLOB）。

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

令人惊讶的是，没有在屏幕上显示XML结果，而是得到了一个很短的字符串：
[C @ 74897f5e

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

它看起来像是一个手柄ID，而不是内容本身。

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

如何检索完整的内容XML？

How can I retrieve the complete content of the XML?

作为参考，数据源使用具有TNS名称的Macromedia驱动程序：
驱动程序类：macromedia.jdbc.MacromediaDriver

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

推荐答案

按照@MarkAKruger的建议，从过程中返回表即可解决此问题。
以下PL / SQL代码可以达到目的：

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;

函数GETPAGESWITHMETADATA是将CLOB返回到r.metadata
的一个技巧

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

由于调用非常简单，因此在ColdFusion方面变得非常好：

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)>

谢谢马克！

这篇关于从ColdFusion 8获取CLOB数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！