将ColdFusion数组作为绑定变量传递给Oracle Collection [英] Pass a ColdFusion Array to an Oracle Collection as a bind variable

问题描述

给出一个Oracle存储过程：

Given an Oracle stored procedure:

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称呼它？

How can I call this from 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]

推荐答案

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

Firstly, set up a data source that uses the Oracle JDBC drivers. Download the appropriate JAR file and place it in the coldfusion instance's lib directory and then, through the CFIDE administration panel, you can set up a data source like this:

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而不是使用< cfstoredproc> 或 new StoredProc（）。

Then you can invoke the stored procedure by dropping down to the raw Java rather than using <cfstoredproc> or 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> ）中使用：

As an aside, you can also pass an array to a query (and then get a result you can use in a <cfloop>) like this:

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();
}

这篇关于将ColdFusion数组作为绑定变量传递给Oracle Collection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！