MS-SQL and Oracle StoredProc

[K]

In MS-SQL, when I want to use stored proc to query data out from the server
and process it in ADO.NET DataAdapter / DataReader stuff. I don't have to
bind the "out" parameter for getting the output.

However, for Oracle DBRS, I need to create an additional "Cursor" output
parameter, and I've to bind one more additional parameter. It makes my data
access layer inconsistent. I've to seperate them. Does it has a easier way
to solve this problem without duplication of my "query" codes ?

 

[Marc Scheuner [MVP ADSI]]

In MS-SQL, when I want to use stored proc to query data out from the server

and process it in ADO.NET DataAdapter / DataReader stuff. I don't have to
bind the "out" parameter for getting the output.

However, for Oracle DBRS, I need to create an additional "Cursor" output
parameter, and I've to bind one more additional parameter. It makes my data
access layer inconsistent. I've to seperate them. Does it has a easier way
to solve this problem without duplication of my "query" codes ?

That's just how the two systems handle returning a result set from a
stored proc - not much you can do about that.

What you *can* do is design the data access layer so that you always
return a DataSet - in SQL Server, you basically get that "for free"
fro the stored proc, in the Oracle case, you might need to do some
extra work to build and fill the dataset.

To the app using your data access layer, the two would look and feel
the same, however. That's probably the best you can do - there will
always be certain system-specific differences at the database layer...

Marc

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

 

[David Browne]

In MS-SQL, when I want to use stored proc to query data out from the server
and process it in ADO.NET DataAdapter / DataReader stuff. I don't have to
bind the "out" parameter for getting the output.

However, for Oracle DBRS, I need to create an additional "Cursor" output
parameter, and I've to bind one more additional parameter. It makes my data
access layer inconsistent. I've to seperate them. Does it has a easier way
to solve this problem without duplication of my "query" codes ?

By convention I just add the ref cursor parameter at the end. The
application binds parameters as it would for SQL Server. Then, when filling
a dataset from a stored procedure if the application hasn't bound a ref
cursor parameter, then DAL adds one at the end.

David