Reputation: 867
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?
Upvotes: 3
Views: 1161
Reputation: 231661
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.
Upvotes: 2