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

Question

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]

I know it could be done by stringifying the array and then destringifying it at the other end but then you have two conversions and you have to modify the stored procedure. But mostly because that's not what the question has been asked about - its specifically about passing an array to a collection - something that can be [done in Java](http://stackoverflow.com/a/37161584/1509264) but I have not found a simple/native way in ColdFusion. — MT0, May 12 '16 at 18:40

score 3 · Accepted Answer · edited Mar 26 '18 at 14:19

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

According to the question, the datasource and stored procedure already exist. — Dan Bracuk, May 12 '16 at 15:14
@DanBracuk Yes the datasource already exists, but it won't work if the datasource uses the Adobe Oracle driver that ships with ColdFusion - the point is that you have to use Oracle's Oracle driver. Regarding your second point, I never redefine the procedure in the answer. — MT0, May 12 '16 at 18:22
@MT0 - I realize this is just an example, but do not forget to [close](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close%28%29) all statement objects, to ensure the resources are properly released. Typically, the [connection](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close%28%29) is closed as well (*after* all statements). However, CF DSN's typically use connection pooling and I do not recall whether or not it is necessary when using ServiceFactory, as it is a bit of a black box. I would think "yes", but you may want to verify that. — Leigh, May 12 '16 at 19:38
@Leigh Agreed - looking at [this question](http://stackoverflow.com/q/4938517/1509264) it appears that "closing" a pooled connection will return it to the pool for re-use so it would be necessary to do. I've updated the code with a `try/finally` block to handle closing everything (the `catch` block is not needed unless you want to handle the exception inline rather than through the normal CF exception handling). — MT0, May 13 '16 at 09:23
A lot more digging into the internals - the Oracle driver is JDBC thin driver whereas the Adobe/Macromedia driver that ships with ColdFusion is a DataDirect driver. — MT0, Jun 05 '17 at 13:08
As far as I can tell, you can put anything in the "Driver Name" field in CF Admin - it needn't be "Other" (you've already specified "other") on the previous screen. — David Faber, Mar 26 '18 at 02:21
When I tried to run the above code (with the Oracle thin client JDBC driver) I got the following error: `Missing IN or OUT parameter at index:: 2`. This happened at `statement.executeQuery();`. I don't see any obvious errors so I'm not quite sure why this is happening. — David Faber, Mar 26 '18 at 14:39

David Faber · Answer 2 · 2018-03-26T17:17:52.783

2

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>

edited Mar 26 '18 at 17:17

answered Mar 26 '18 at 14:52

David Faber

12,277
2
29
40

1

Interesting. I also wonder if arrays are really per connection, or you just need "some connection" to get the metadata stuff. Too bad the interface is so complicated or you might be able to create a "Array" with CF code :) (Also, saw something in the comments about closing/returning connections to the pool, so might want to add that). – SOS Mar 26 '18 at 16:50
I'm sure you need "some connection" to the same Oracle SID so that you have the correct array type. But I'm guessing it needn't be to the same DSN. – David Faber Mar 26 '18 at 16:56
Yeah, I was just wondering if you could do it say once, when the application started or if you needed to re-open a connection every time you run the proc. – SOS Mar 26 '18 at 17:16
It looks like it would have to be done once for each unique array. I don't see a way around that. Also, looking into the connection pool issue - it seems that the methods used might use a pooled connection automatically if one exists? The connection pool is at the Java level - not the CF level. – David Faber Mar 26 '18 at 17:19
1

Yeah, from what I read it would use a pooled connection if it's enabled, but the comments mentioned you must still `close()` it. If pooling's enabled, that returns the connection to the pool. Otherwise, it physically closes the connection. – SOS Mar 26 '18 at 17:40
1

Thanks for this - I got it to work on CF11 but not on (the very old) CF9 so it looks like I'm stuck with dropping down to Java (so it works on both versions) until both servers get upgraded to the latest version. – MT0 Mar 27 '18 at 15:03
Interesting - `CF_SQL_ARRAY` and `CF_SQL_STRUCT` have been around for a while (they're not well-documented, though) so it's odd the above doesn't work on CF9. But I suppose not surprising! – David Faber Mar 27 '18 at 16:35

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

2 Answers2