Passing an array of structures to an Oracle stored procedure (CFMX)

Question

I'm looking to write a Oracle stored procedure where I would pass in (from ColdFusion) an array of structures and loop over each iteration to insert the bits and pieces within the structures to the DB.,

I haven't written this type of procedure / package before. I am planning to do an sp / package similar to what is sketched out in the second reply to this thread: How to pass a array of object to oracle stored procedure

Assuming I do, how can I call the procedure from ColdFusion (I'm using MX) and pass in my array? As far as I can see, none of the CF_SQL_Types make sense.

Answer 1

The only way I have passed arrays of any data type to an Oracle SP is to use the OCI interface and collections. If there is a way to do it in straight Pro*C I have not found it.

OCI is a more 'verbose' interface. Here is a the base documentation.

http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10779/oci11oty.htm#421737

Answer 2

Instead of an array of structs, I'd recommend just converting the data to XML and passing that to Oracle.