Oracle sys_refcursor from normal SQL

Question

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?

Have you simplified your example too much? You know that PL/SQL block doesn't return anything right? — Ben
– Ben, Commented May 20, 2012 at 21:18
Well I'm not a Oracle guy but my client sent this sample to me and told me I should be able to get the data from the DataOut variable. — Gunnar Steinn
– Gunnar Steinn, Commented May 21, 2012 at 10:55
Can I maybe change it to a return value? How would I do that? Thanks :) I'm really lost here! — Gunnar Steinn
– Gunnar Steinn, Commented May 21, 2012 at 10:55

Justin Cave · Accepted Answer · 2012-05-20 21:32:02Z

2

+100

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.

answered May 20, 2012 at 21:32

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gunnar Steinn Over a year ago

Great thanks. But could I solve this problem somehow, is there a way to get something from a sys_refcursor to .Net without named procedures. For example by selecting from it?

Justin Cave Over a year ago

@GunnarSteinn - No. A sys_refcursor is PL/SQL construct, not a SQL construct. It can only be declared in a PL/SQL block. And it can only be returned to the caller if the PL/SQL block returns something which is only possible with named PL/SQL blocks that can declare parameters and returns. You could, of course, construct whatever SQL statement you would construct in the PL/SQL block in your .Net application and just execute that SQL statement from .Net, bypassing the need for PL/SQL entirely.

Jon Heller · Accepted Answer · 2012-05-22 04:30:58Z

0

select cursor(select * from dual) from dual;

answered May 22, 2012 at 4:30

Jon Heller

37k6 gold badges84 silver badges150 bronze badges

Collectives™ on Stack Overflow

Oracle sys_refcursor from normal SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related