I understand that Oracle SPs only return 1 record. Has anyone found a workaround for this and used the SPs in ColdFusion? I found the following response from an Oracle Forum discussing how to get multiple rows from a stored procedure. Does anyone know if this would work and can this be called from CF or are there any other solutions?
http://www.oracle.com/forums/message.jsp?id=483524 This is much more difficult than it ought to be, but once you get the kinks worked out, it is pretty sweet. Here's what you do: 1. Create a package and package body that will contain your stored procedure(s). 2. In the package, add this line: TYPE row_cursor is REF CURSOR; Your procedures will return row_cursors. Actually row_cursor is a type you are defining. You could use another name if you wanted to. 3. Create a FUNCTION in the package and package body that returns row_cursor: (In package) FUNCTION MY_STORED_PROCEDURE(nParam IN NUMBER) return row_cursor; (in package body) FUNCTION MY_STORED_PROCEDURE(nParam IN NUMBER) return row_cursor IS c_return row_cursor; BEGIN OPEN c_return FOR SELECT * from whatever where ID = nParam; return c_return; END; Thanks, Issac ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
