I want to create a stored procedure on ORACLE database server and my problem is that, I don't know how can I return a select statement .
Here is the logic, which should within the procedure:
Input paramters: filter1 (int), filter2 (string)
with cte as
(
select val1, val2, stddev(val3) from tab1 where parameter1 = filter1 and paramter = filter1 group by val 1, val2
)
SELECT cte.*,
round(some calculation) as final_results FROM cte
Afterwards I want to use this procedure in a MS asp.net application, with help of the MS ADO.net and MS Entity Framework 4.2.
Lot of thanks, for your response!
In Oracle we have to use Ref Cursors to acheive this. The very latest version of ODP .Net supports Ref Cursor binding for Entity Framework 4.x. Find out more.
Of course if you're not using Oracle 11gR2 you're probably out of luck, and you'll need to use one of the other suggestions (such as Pipelined functions).
To return the result of a SELECT in Oracle you would use a "pipelined table function".
Please refer to the manual for a description and an example:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/tuning.htm#i53109
Here are some more examples from other sites:
http://www.oracle-developer.net/display.php?id=207
http://www.oracle-base.com/articles/misc/PipelinedTableFunctions.php
http://psoug.org/reference/pipelined.html