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?
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 ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source Step by Step ColdFusion http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
