PL/SQL Procedure: How return a select statement?

ado.net entity-framework oracle plsql sql

Question

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!

1
2
1/24/2012 2:14:58 PM

Accepted Answer

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).

2
1/24/2012 2:22:03 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow