将ColdFusion数组作为绑定变量传递给Oracle集合

Question

将ColdFusion数组作为绑定变量传递给Oracle集合

3

给定一个Oracle存储过程：

CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/

CREATE PROCEDURE test_proc(
  list   IN  stringlist,
  output OUT VARCHAR2
)
AS
BEGIN
  IF list IS NULL OR list IS EMPTY THEN
    RETURN;
  END IF;
  output := list(1);
  FOR i IN 2 .. list.COUNT LOOP
    output := output || ',' || list(i);
  END LOOP;
END;
/

我该如何在ColdFusion中调用这个呢？

<cfscript>
  arr = [ 'A', 'B', 'C' ];

  sp = new StoredProc(
    dataSource = "orcl",
    procedure  = "test_proc",
    result     = "NA",
    parameters = [
      { cfsqltype = "CF_SQL_ARRAY",  type="in",   value = arr },
      { cfsqltype = "CF_SQL_VARCHAR", type="out", variable = "out" }
    ]
  ).execute();

  // WriteDump( sp.getProcOutVariables().out );
</cfscript>

失败原因：

Error Executing Database Query
Fail to convert to internal representation: [A, B, C]

- MT0

为什么参数不能是一个列表？ - Dan Bracuk

我知道可以通过将数组转换为字符串，然后在另一端进行反序列化来完成，但这样你就需要进行两次转换，并且必须修改存储过程。但主要是因为问题的重点不在于此 - 具体而言，它是关于将数组传递给集合 - 这是在Java中可以完成的事情，但我在ColdFusion中没有找到一个简单/本地的方法。 - MT0

2个回答

2

我试着操作这个问题，发现一旦 Oracle 数组被正确创建（包括建立连接，如上所述，并从原始数组创建适当类型的 Oracle 数组（在本例中为 STRINGLIST），那么就可以使用 <cfstoredproc>（并且，我认为，<cfquery>）并带有一个 CF_SQL_ARRAY 类型的参数来执行存储过程（或查询）：

<cfset the_datasource = "oratest" />
<cfset the_array = javaCast("string[]", ["A","B","C"]) />
<cfset return_value = "" />
<cftry>
    <cfset the_connection = createObject("java", "coldfusion.server.ServiceFactory")
        .getDataSourceService()
        .getDataSource("#the_datasource#")
        .getConnection()
        .getPhysicalConnection()
    />
    <!---
    <cfset type_desc = createObject("java", "oracle.sql.ArrayDescriptor").createDescriptor("STRINGLIST", the_connection) />
    <cfset oracle_array = createObject("java", "oracle.sql.ARRAY").init(type_desc, the_connection, the_array) />
    --->
    <!--- oracle.SQL.ARRAY is deprecated; use this instead: --->
    <cfset oracle_array = the_connection.createOracleArray("STRINGLIST", the_array) />
    <cfset the_connection.close() />

    <cfstoredproc procedure="test_proc" datasource="#the_datasource#">
        <cfprocparam cfsqltype="CF_SQL_ARRAY" type="in" value="#oracle_array#" />
        <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="return_value" />
    </cfstoredproc>

<cfcatch>
    <cfdump var="#cfcatch#" />
</cfcatch>
</cftry>

<cfdump var="#return_value#" />

请注意，在上面的代码中，我只打开了一个连接以创建Oracle数组。我还没有想出是否可以尝试使用现有连接或在调用<cfstoredproc>时重用连接。

希望这能帮到你。

编辑：

要将数组传递给查询，您可以简单地执行以下操作：

<cfquery name="get_table" datasource="#the_datasource#">
    SELECT * FROM TABLE( <cfqueryparam cfsqltype="CF_SQL_ARRAY" value="#oracle_array#" /> )
</cfquery>

- David Faber

1

有趣。我也想知道数组是否真的是每个连接，或者你只需要“一些连接”来获取元数据。太糟糕了，界面如此复杂，否则你可能可以用CF代码创建一个“数组” :) （另外，在评论中看到有关关闭/返回连接到池中的内容，因此可能需要添加）。 - SOS

我相信你需要“一些连接”到同一个Oracle SID，以便你拥有正确的数组类型。但我猜想它不必连接到同一个DSN。 - David Faber

是的，我只是想知道你是否可以在应用程序启动时执行一次，或者每次运行过程时是否需要重新打开连接。 - SOS

1

根据我所读的，如果启用了连接池，它将使用池化连接，但评论中提到您仍然必须close()它。如果启用了连接池，则会将连接返回到池中。否则，它将物理关闭连接。 - SOS

1

谢谢您，我已经在CF11上使其工作了，但是在（非常老的）CF9上无法运行，所以看起来我只能降级到Java（以便在两个版本上都可以运行），直到两个服务器升级到最新版本。 - MT0

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MT0 · Accepted Answer

首先，建立一个使用Oracle JDBC驱动程序的数据源。下载适当的JAR文件并将其放置在ColdFusion实例的lib目录中，然后通过CFIDE管理面板，您可以设置如下的数据源：

CF Data Source Name: orcl
JDBC URL:            jdbc:oracle:thin:@localhost:1521:orcl
Driver Class:        oracle.jdbc.OracleDriver
Driver Name:         Other

<注意：驱动程序名称为“Other”，而不是“Oracle”-后者将使用Adobe的Oracle驱动程序而不是指定的Oracle驱动程序。> 然后，您可以通过回到原始Java来调用存储过程，而不是使用或new StoredProc()。

<cfscript>
array       = JavaCast( "string[]", [ 'A', 'B', 'C' ] );
try {
  connection  = createObject( 'java', 'coldfusion.server.ServiceFactory' )
                  .getDataSourceService()
                  .getDataSource( 'orcl' )
                  .getConnection()
                  .getPhysicalConnection();
  description = createObject( 'java', 'oracle.sql.ArrayDescriptor' )
                  .createDescriptor( 'STRINGLIST', connection );
  oracleArray = createObject( 'java', 'oracle.sql.ARRAY' )
                  .init( description, connection, array );

  statement   = connection.prepareCall( '{call test_proc( :input, :output )}' );
  statement.setARRAYAtName( "input", oracleArray );
  stringType  = createObject( 'java', 'java.sql.Types' ).VARCHAR;
  statement.registerOutParameter( "output", stringType );
  statement.executeQuery();

  returnValue = statement.getString( "output" );
}
finally
{
  if ( isDefined( "statement" ) )
    statement.close();
  if ( isDefined( "connection" ) )
    connection.close();
}
</cfscript>

另外，你也可以像这样将数组传递给查询（然后得到一个可以在 <cfloop> 中使用的结果）：

try {
  // set-up connection, etc. as above
  statement   = connection.prepareStatement( 'SELECT * FROM TABLE( :input )' );
  statement.setARRAYAtName( "input", oracleArray );
  resultSet   = statement.executeQuery();
  queryResult = createObject( 'java', 'coldfusion.sql.QueryTable' )
                .init( resultSet )
                .FirstTable();
}
finally
{
  if ( isDefined( "resultSet" ) )
    resultSet.close();
  if ( isDefined( "statement" ) )
    statement.close();
  if ( isDefined( "connection" ) )
    connection.close();
}