What is the best way to retrieve data from an oracle table using odp.net and pl/sql stored procedures?

Question

I need to retrieve data from an oracle table using a pl/sql stored procedure and odp.net. What is the standard way of doing this?

Answer 1

PL/SQL has the ability to return sets of data using Ref Cursors, which are basically pointers. Here is a simple function which returns a sub-set of employees based on department:

SQL> create or replace function get_emps_by_dept (dno number)
  2      return sys_refcursor
  3  is
  4      rc sys_refcursor;
  5  begin
  6      open rc for select * from emp where deptno = dno;
  7      return rc;
  8  end;
  9  /

Function created.

SQL>

Here's how in works in SQL*Plus:

SQL> var rc refcursor
SQL> exec :rc := get_emps_by_dept(50)

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      8085 TRICHLER   PLUMBER         8061 08-APR-10       3500                    50
      8060 VERREYNNE  PLUMBER         8061 08-APR-08       4000                    50
      8061 FEUERSTEIN PLUMBER         7839 27-FEB-10       4500                    50
      8100 PODER      PLUMBER         8061                 3750                    50

SQL> 

With regards to .Net, there is a OracleRefCursor class amongst the Oracle.DataAccess.Types . A certain amount of plumbing is required, but the excellent Mark A Williams has written a good article on this topic, which you can find on the OTN site.