returning multiple cursors in Ora S.P.

[Netnoobie]

I've ben looking for something on this on the internet and can't seem to find anything. I'd really like to be able to use a stored procedure that returns a list of values. An example is to return all the products for a certain department id...where the dept has X number of products.

 

This is the first time I've ever really needed to use an Oracle stored proc like this (I'm a SQL Server developer in the past) so I'm drawing a blank. I can't really define all the ref cursors off the bat becuase I don't know how many there are.

 

So how can you do a fairly large query and return multiple values (returned in a DataSet would be great) for use? I can return one or two defined cursors, but this has me stumped.

 

Many Thanks,

Bryan

[iebidan]

OK; I had the same problem with this thing, this what I did to connect an execute procedures in Oracle, BTW, if you try to use the OracleClient included in the FW you will get an error so that's why I used OleDb

 

OleDbConnection Conn = new OleDbConnection("Provider=MSDAORA.3;User ID=apusucsaye;Password=xxxx;Data Source=vvvvv");
OleDbCommand obj= new OleDbCommand("P_INSERT_SP20", Conn); //You have to put the name of the procedure here
obj.CommandType=CommandType.StoredProcedure;

OleDbParameter myParm = new OleDbParameter("P_NOCOT",OleDbType.Integer),
myParm2=new OleDbParameter("P_VALORDATOVAR",OleDbType.VarChar),
myParm3=new OleDbParameter("P_VALCAMPO",OleDbType.VarChar),
myParm4=new OleDbParameter("P_CODRAMO",OleDbType.Integer); // The names like P_NOCOT are the names of the variables inside the procedure, they need to be the same as in the procedure
myParm.Direction=ParameterDirection.Input;
myParm2.Direction = ParameterDirection.Input;
myParm3.Direction = ParameterDirection.Input;
myParm4.Direction = ParameterDirection.Input;
myParm.Value = p_nocot;
myParm2.Value = p_valordatovar;
myParm3.Value = p_valcampo;
myParm4.Value = sRamo;
obj.Parameters.Add(myParm);
obj.Parameters.Add(myParm2);
obj.Parameters.Add(myParm3);
obj.Parameters.Add(myParm4);

OleDbParameter reg = new OleDbParameter("p_cod_error",OleDbType.Integer);
reg.Direction=ParameterDirection.ReturnValue;
obj.Parameters.Add(reg);

Conn.Open();
obj.ExecuteNonQuery();
Conn.Close();
obj.Dispose();
Conn.Dispose();

Edited January 13, 2004 by iebidan

[iebidan]

Hey the C# tag is not working :) how am I supposed to make my code be like C#??????

[quwiltw]

Use [ c s ] with no spaces.

[Netnoobie]

Thanks for the example. I am using the OracleClient so I may ned to look at that too.

 

But in your example I don't see how you are returning a dynamic amount of cursors. Am I just missing it? There may be 10 or 100 products for a dept id and I need to return all the products without knowing in advance.

 

Thanks for the help, I really appreciate it.

[iebidan]

to return anything use this

OleDbParameter reg = new OleDbParameter("p_cod_error",OleDbType.Integer);
reg.Direction=ParameterDirection.ReturnValue;
obj.Parameters.Add(reg);

Add a Parameter for a return value, this works pretty fine, and when I used the OracleClient I had many problems with it, that0's why I use the OleDb Client

[iebidan]

Look mom!!!!! it's working!!!!!!! :) thxs Edited January 13, 2004 by iebidan