Search code examples
c#.netoracle-databaseplsqlsys-refcursor

Oracle sys_refcursor from normal SQL

I have a (simplified) Oracle SQL like this:

declare
  xd number;
  xm number;
  DataOut sys_refcursor;
begin
  xd := to_number(to_char(sysdate, 'dd'));
  xm := to_number(to_char(sysdate, 'mm'));

  open DataOut for
  select * from dual;  
end;

And I want to be able to fill a DataTable in .Net from the data returned in the DataOut parameter.

I have been trying various things but can't seem to access the DataOut cursor. How would I call this?

OracleCommand c = new OracleCommand();
c.CommandType = CommandType.Text;
c.CommandText = SQL;

OracleParameter param = new OracleParameter();
param.Direction = ParameterDirection.Output;
param.OracleType = OracleType.Cursor;
param.ParameterName = "DataOut";
c.Parameters.Add(param);

c.Connection = (OracleConnection) this.GetConnection();

OracleString rowNum = "";
c.ExecuteOracleNonQuery(out rowNum);
// or c.ExecuteReader()
// or use OracleDataAdapter

DataTable returnTable = /* magic goes here */

I can edit the SQL but I'm not able to create functions or procedures. Is this possible?

Solution

  • An anonymous PL/SQL block does not return anything so you won't be able to use the cursor you open in the anonymous PL/SQL block in your client application. In order to return the data to the client application, you would need to use a named PL/SQL block (i.e. a stored procedure or a stored function). If you are not allowed to create named PL/SQL blocks, you won't be able to return a cursor you open in PL/SQL to your client application.