CODEWITHSUNDEEP
oracle-databasecoldfusion
MT0
MT0

Reputation: 167972

Pass a ColdFusion Array to an Oracle Collection as a bind variable

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;
/

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>

Fails with:

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

Upvotes: 3

Views: 745

Answers (2)

David Faber
David Faber

Reputation: 12485

I was playing around with this and I found that once the Oracle array is properly created (which involves creating a connection, as above, and creating an Oracle array of the appropriate type (in this case, STRINGLIST) from the original array, then one can use <cfstoredproc> (and, I assume, <cfquery>) with a parameter of type CF_SQL_ARRAY in order to execute the stored procedure (or query):

<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#" />

Note that in the above code I open a connection to the database only to create the Oracle array. I haven't figured out yet whether I can try to use an existing connection or re-use the connection in the call to <cfstoredproc>.

Hope this helps.

EDIT:

To pass the array to a query, you can simply do something like the following:

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

Upvotes: 2

MT0
MT0

Reputation: 167972

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

(Note: the driver name is "Other" not "Oracle" - which would use adobe's Oracle driver not the specified Oracle driver.)

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>

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

Upvotes: 3

Related Questions