1

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.

Patti
  • 186
  • 3
  • 7
  • 1
    CF -> SQL Server via XML makes sense, not sure if it's viable for Oracle. Yes, none of the CF_SQL_Types will make any sense. If this is critical to you, you can build it in Java and let CF invoke the Java method responsible for calling the store procedure I guess. – Henry Jun 13 '10 at 18:42
  • cfsqltype="cf_sql_longvarchar" works for XML passed as a string, not as a CF XML type. This also works for Oracle. – Adrian J. Moreno Apr 16 '12 at 22:02
  • You can pass arrays using Java ([see answer here](http://stackoverflow.com/a/33160309/1509264)) and if you combine it with the [code snippet here](http://www.justskins.com/forums/stored-procedures-jdbc-116480.html) then it should be do-able in ColdFusion - however I've yet to work out how to use ColdFusion's data sources or whether it is possible to use any of the built in functions to do this. – MT0 Oct 20 '15 at 08:15

2 Answers2

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

jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
  • [Internet Archive link here](http://web.archive.org/web/20090820020421/http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10779/oci11oty.htm) - however the linked page is for C and appears to have nothing to do with ColdFusion. – MT0 Oct 19 '15 at 11:43
  • Yes. That is what Pro*C is. C. To my knowledge you cannot pass arrays as described in the question, using coldFusion. ColdFusion does support an OCI interface - C calls I believe. – jim mcnamara Oct 20 '15 at 02:46
0

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

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44