Return Value from a Stored Procedure using DAAB

[Guest]

I'm using a Microsoft Data Access Application Block to run a stored procedure
in Oracle that returns a value, and am confused. Most of the overloaded
methods of ExecuteNonQuery specifically say they can't deal with Output or
Return values.

Can do I use a DAAB to return a value from a stored procedure, and if so, how?

Thanks,

 

[Tor Bådshaug]

The ExecuteScalar method may be helpful to you.

Good luck!

Tor Bådshaug
tor.badshaug [//at\\] bekk.no.

 

[David Browne]

The ExecuteScalar method may be helpful to you.

ExecuteScalar is for result sets, not output parameters.

David

 

[David Browne]

I'm using a Microsoft Data Access Application Block to run a stored
procedure
in Oracle that returns a value, and am confused. Most of the overloaded
methods of ExecuteNonQuery specifically say they can't deal with Output or
Return values.

First.

The real "Microsofr Data Access Application Block" only runs against Sql
Server, so you must be using one of the many variants floating around.

Second.

Oracle stored procedures don't have return values, they have IN, OUT and
INOUT parameters. The DAAB should be able to handle output parameters.

Third.

If you're still stuck, write your own PL/SQL wrapper block and use
CommandType.Text. That way you you can handle the parameter binding
manually, and not leave it up to the helper code. EG:

"begin my_package.my_procarg1, :arg2); end;"

David

 

[mageos]

First.

The real "Microsofr Data Access Application Block" only runs against Sql
Server, so you must be using one of the many variants floating around.

Second.

Oracle stored procedures don't have return values, they have IN, OUT and
INOUT parameters. The DAAB should be able to handle output parameters.

Third.

If you're still stuck, write your own PL/SQL wrapper block and use
CommandType.Text. That way you you can handle the parameter binding
manually, and not leave it up to the helper code. EG:

"begin my_package.my_procarg1, :arg2); end;"

David

I am not familiar with Microsoft Data Access Application Blocks but I
know that with the standard ADO.Net classes from framework v.1.1 the
SqlCommand object has a parameter collection and those parameters can be
marked as in, out and bidirectional. I know that with SQL Server stored
procedures you can use these parameters to get out parameters. I hope
this helps.

Mageos

 

[Marc Scheuner [MVP ADSI]]

I'm using a Microsoft Data Access Application Block to run a stored procedure

in Oracle that returns a value, and am confused. Most of the overloaded
methods of ExecuteNonQuery specifically say they can't deal with Output or
Return values.
Can do I use a DAAB to return a value from a stored procedure, and if so, how?

Yes - at least for MS SQL Server (not sure about Oracle - it's been
too long since I've used it).

What I've noticed, though, is that some of the overloads seem to work,
while others don't.

This will work (in my experience):
SqlHelper.ExecuteNonQuery(SqlConnection oConn, CommandType.StoredProc,
"stored Proc Name", SqlParameters[] params);

Will *NOT* work (output params are "undefined" upon return from stored
proc):

SqlHelper.ExecuteNonQuery(SqlConnection oConn, "stored Proc Name",
object[] params);

Not entirely sure why - they seem almost identical to me, but they're
NOT....

Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch