How to force Oracle to return result sets

Question

The SQL Server procedure can return result sets. I have an emp table (emp__id, emp__name, ...) . In the procedure below, a list of employees matching the specified name will be listed.

CREATE OR REPLACE PROCEDURE get_employee_by_name ( @name VARCHAR(100) )
AS
SELECT emp_id, emp_name
FROM emp
WHERE emp_name = @name;

So, in the client code, I use ADO.NET to get the data.

SQLDataAdapter adapter = new SQLDataAdapter("get_employee_by_name", cnString);
SQLDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable("employee");

adapter.Fill(dt);

How can I code equivalently in PL / SQL?

Answer 1

Use the Ref cursor for the stored procedure:
http://www.oradev.com/ref_cursor.jsp

For the client part, use the Oracle Data Provider. You can download it from Oracle, and the syntax is similar to SQLDataAdapter. Something like that:

OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = new OracleCommand("get_employee_by_name", Connection);
OracleParameter prm = da.SelectCommand.Parameters.Add("pName", OracleDbType.VarChar2);
prm.Direction = ParameterDirection.Input;
prm.Value = "MyName";
prm = da.SelectCommand.Parameters.Add("pResult", OracleDbType.RefCursor);
prm.Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
da.Fill(dt);