Geek asks and answers

Oracle sys_refcursor from plain SQL

I have (simplified) Oracle SQL:

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 populate a DataTable in .Net from the data returned in the DataOut parameter.

I tried different things but cannot access the DataOut cursor. How can I call it?

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 SQL, but I cannot create functions or procedures. Is it possible?

+3
source share
2 answers

PL/SQL , , PL/SQL . , PL/SQL- (.. ). PL/SQL-, , PL/SQL, .

+2
select cursor(select * from dual) from dual;
0

More articles:


All Articles