Surely your SQL guy would provide you with a properly normalised DB for you to insert your data into?
Can't you just pass the values in as row entries? "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Heald, Timothy J To: CF-Talk Sent: Thu Apr 05 20:56:36 2007 Subject: Passing complex data into Oracle stored procedures I am adding a lot of additional functionality to an existing application (yuck). Basically taking a static survey application that generates a word document and turning it into a dynamic survey application that puts out multiple documents in multiple formats. I have several large forms that I am building dynamically. The number and names of fields won't be known until runtime, and are generated as you'd expect, using incremental or ID based variables. Here's the thing, currently these forms values are being handed into an oracle procedure in list form. I hate that :-). I know simply naming them all the same thing will make the list, but does that guarantee the order in which they will appear in the list? If not my referential integrity ill be shit. Now for whatever reason I am not allowed to just have a loop making multiple procedure calls on my action page, not looking to get into that. I would like to get away from handing the back end lists. I'm trying to look for a solution that can make me and our PL/SQL cats happy. Right now I can think of three ways to handle it, but I am not sure how I would/could accomplish them in CF. 1. Pass in arrays to the procedure. 2. Pass in a query or ref cursor to the procedure. 3. Pass in XML to the procedure. The SQL developer hasn't worked with 9i XML functionality and neither have I. He has looked into it and said he would essentially need to create his own string parser in order to properly write my objects into the DB, and the development and learning overhead would be more than what we are looking to invest right now. The ref cursor/query idea seems like one that would work if I wrote some Java. I cannot currently hand in a <cfprocparam of type query or whatever. Is there a way around that? I've seen passing in arrays spoken about, but I cannot seem to find a definitive example of how that would work. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274649 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
