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

[Guest]

I'm looking to write a Oracle stored procedure where I would pass in 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: http://forums.oracle.com/forums/thread.jspa?threadID=1078772

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.

[wrkoch]

Let me know if you make any progress.  I'm fighting the same battle.  What I've done so far is to convert my array of struct into a delimited CLOB that looks like this:

prop1;prop2;prop3|prop1;prop2;prop3|prop1;prop2;prop3|prop1;prop2;prop3|

Then I wrote a stored proc to suck it up using a pipelined function.  It's not to bad but parsing the CLOB on the ORACLE side is somewhat time consuming.

I've also converted the array to XML and used dbms_xmlstore to convert but, on large arrays, it is very slow and the CLOB gets huge fast.

I was hoping to use the cf_sql_refcursor but I can't figure out how.

Warren