Accessing Oracle 9i Stored Procedures Using ADO.NET

[Oriol Tomàs]

I'm working with Visual Basic .Net and Oracle 9i.

I use stored procedures that return ref cursors to query data from my
database.

This isn't a portable solution because ref cursors is a particular Oracle
type.

Do you know a better solution?

Regards.

Oriol Tomàs

 

[David Browne]

I'm working with Visual Basic .Net and Oracle 9i.

I use stored procedures that return ref cursors to query data from my
database.

This isn't a portable solution because ref cursors is a particular Oracle
type.

Do you know a better solution?

Well for SQL Server, stored procedures can "magically" return result sets to
clients, which Oracle stored procedures cannot do. So you've got a dillema.
One option is to never return result sets from stored procedures. Only use
scalar values. If you plan on porting beyond Oracle and Sql Server then
this is your best option. In that case you probably shouldn't use stored
procedures at all.

However if you are just targeting Sql Server and Oracle, then you don't have
to give up this highly usefull feature. The trick is just to establish a
convention where the sql server stored procedure and the Oracle stored
procedure differ only by the presense of a ref cursor output parameter at
the end of the parameter list. Then in your application if you're
retreiving a result set from a stored procedure using ExecuteReader or a
DataAdapter, add an extra ref cursor out parameter to the command at
runtime.

David

 

[Frans Bouma [C# MVP]]

I'm working with Visual Basic .Net and Oracle 9i.

I use stored procedures that return ref cursors to query data from my
database.

This isn't a portable solution because ref cursors is a particular Oracle
type.

Do you know a better solution?

Stored procedures aren't scalable anyway to another type of database system,
so they are specific for a specific database type. You can abstract away the
call code to a retrieval stored procedure so that code will return the
resultset(s) in a dataset for example. Your code then uses that dataset for
further processing. This makes your own code unaware of HOW the dataset is
filled: by the adapter.fill() or by binding the refcursor parameter to the
dataset. So some small routines per database type is what you need.

Frans.

 

[Guest]

Check Out MSKB

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/msdnorsps.asp

Thanks
Nilesh