Return Value from a Stored Procedure using DAAB

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
The ExecuteScalar method may be helpful to you.

Good luck!

Tor Bådshaug
tor.badshaug [//at\\] bekk.no.
 
Matt said:
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.
Click to expand...

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_proc(:arg1, :arg2); end;"

David
 
David said:
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_proc(:arg1, :arg2); end;"

David
Click to expand...
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
 
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?
Click to expand...

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
 
Back
Top