How do I return multiple rows from an Oracle stored procedure/function to a web service?
Posted on 2010-09-07
I have successfully built an Oracle stored function which takes a single input parameter and returns a single value. I've then used JDeveloper to create a web service to call this function and successfully got this to run through a browser.
I now need to enhance this simple function to return a record set. Essentially the function will be passed a couple of values, which it will use to identify one or more rows in a table and I need to return these rows. I understand there are different ways of coding an Oracle function to do this (ref cursors, arrays etc), but what I need to know is what method to use to ensure the rows are retrieved correctly when called from a web service. For example, I don't believe ref cursors are a supported data type in a web service.
The web service itself returns its values in xml format, but how do I build the function to return the individual rows from the function such that the web service can return these rows as xml?
I'm new to JDeveloper and web services so please make things simple for me to understand ;-)
Hopefully there is a way to achieve this.