with beta 2 it took us some messing



We have the following code working (names changed to protect the innocent):



			OleDbConnection odbConn = new OleDbConnection

("Provider=MSDAORA;Data Source=?;User ID=?;Password=?");

			OleDbCommand odbCmd = new OleDbCommand();

			OleDbDataAdapter odbDA = new OleDbDataAdapter();

			DataSet ds = new DataSet(); 



			//this syntax was nicked from a msdn kb article on 

how to retun ref cursors with ado

			odbCmd = new OleDbCommand("{call package.procedure

(?, {resultset 0, hc})}", odbConn);



            		// don't tell it it's a stored procedure

            		//odbCmd.CommandType = 

CommandType.StoredProcedure;   



			odbCmd.Parameters.Add(new OleDbParameter("N_PK", 

OleDbType.Integer));

			odbCmd.Parameters["N_PK"].Value = 4868;



            		odbDA = new OleDbDataAdapter(odbCmd);

			ds = new DataSet(); 

			odbDA.Fill(ds, "test");

			





We are using the ms provider and have a stored procedure (not really 

called package.procedure) with a numeric parameter for the PK value and an 

in out ref cursor.



It moans when you attempt to define the command type as StoredProcedure 

(may be a bug?) hence the ?'s.



The "{resultset 0, io_cursor}" syntax was taken from an msdn article 

describing use of ref cursors with ado.





Hope this helps

Mark

Mark,



Thanks for that code, it's also what I needed.



But would you (or anyone else) know what the complete syntax and its 

meaning is for that "{call package.procedure(?, {resultset 0, 

io_cursor})}" call is, or where I can find it?



It works fine for a procedure with one numeric parameter and a ref 

cursor, but I can't get it to work with a procedure that takes a numeric 

parameter, a varchar2 parameter and a ref cursor.  I tried adding a 

question mark "?, " to the line, but that results in an "ORA-03113: 

end-of-file on communication channel" error.



Thanks in advance,

--

Martijn Coppoolse





----- Original Message -----

From: "Mark Porter" <mark.porter@m...>

To: "ASP+" <aspx@p...>

Sent: Monday 06 August 2001 16:19

Subject: [aspx] Re: Execute A Oracle Stored Procedure which returns a 

recordset





> with beta 2 it took us some messing

>

> We have the following code working (names changed to protect the 

innocent):

>

> OleDbConnection odbConn =3D new OleDbConnection

> ("Provider=MSDAORA;Data Source=?;User ID=?;Password=?");

> OleDbCommand odbCmd = new OleDbCommand();

> OleDbDataAdapter odbDA = new OleDbDataAdapter();

> DataSet ds =3D new DataSet();

>

> //this syntax was nicked from a msdn kb article on

> how to retun ref cursors with ado

> odbCmd = new OleDbCommand("{call package.procedure

> (?, {resultset 0, hc})}", odbConn);

>

>             // don't tell it it's a stored procedure

>             //odbCmd.CommandType 

> CommandType.StoredProcedure;

>

> odbCmd.Parameters.Add(new OleDbParameter("N_PK",

> OleDbType.Integer));

> odbCmd.Parameters["N_PK"].Value = 4868;

>

>             odbDA = new OleDbDataAdapter(odbCmd);

> ds = new DataSet();

> odbDA.Fill(ds, "test");

>

>

>

> We are using the ms provider and have a stored procedure (not really

> called package.procedure) with a numeric parameter for the PK value 

and an in out ref cursor.

>

> It moans when you attempt to define the command type as StoredProcedure

> (may be a bug?) hence the ?'s.

>

> The "{resultset 0, io_cursor}" syntax was taken from an msdn article

> describing use of ref cursors with ado.

>

>

> Hope this helps

> Mark