Passing Back Table Information from Oracle .NET Stored Procedures

[jehugaleahsa]

Hello:

I am working with Oracle .NET Stored Procedures. I would like to know
how to return the results of a SELECT statement. I have tried
returning a OracleRefCursor and a DataTable, but nothing seems to
work.

What data type must I return for this to be accepted as .NET stored
procedure?

Thanks a lot,
Travis

[sloan]

See this example.
http://www.oracle.com/technology/pub...efcursors.html

I'm not sure about the DataTable thing.
You might want to look at the EnterpriseLibra ry.Data block as well.

Its an abstract way to talk to databases, and there is an Oracle concrete
version.


<je**********@g mail.comwrote in message
news:11******** *************@x 40g2000prg.goog legroups.com...

Hello:

I am working with Oracle .NET Stored Procedures. I would like to know
how to return the results of a SELECT statement. I have tried
returning a OracleRefCursor and a DataTable, but nothing seems to
work.

What data type must I return for this to be accepted as .NET stored
procedure?

Thanks a lot,
Travis

[Jesse Houwing]

* je**********@gm ail.com wrote, On 30-7-2007 5:18:

Hello:

I am working with Oracle .NET Stored Procedures. I would like to know
how to return the results of a SELECT statement. I have tried
returning a OracleRefCursor and a DataTable, but nothing seems to
work.

What data type must I return for this to be accepted as .NET stored
procedure?

Thanks a lot,
Travis

See this example from the Microsoft KB:

http://support.microsoft.com/default...b;EN-US;310101

Jesse

[jehugaleahsa]

You are still not understanding my question. I am not trying to call a
stored procedure, not in the typical PL/SQL sense. Okay, it is
possible to upload a .NET assembly to an Oracle database. In doing so,
the database engine can execute the .NET assembly under a context. In
a sense, you are executing a .NET assembly just like you would any
other PL/SQL stored procedure.

This is not PL/SQL. I am not trying to get a ref cursor. I am trying
to get the results of a select command from a ".NET stored procedure".
Check out this link if you have no clue what I am talking about:
http://www.oracle.com/technology/pub...liams_sps.html.

If you take the time to look at the link, you will know exactly what I
am talking about. All the example links I have been shown have been
for working with ADO .NET or writing PL/SQL. I just want to get a
result set from a .NET stored procedure. And, yes, it *is*, really,
truly, a .NET stored procedure.

Please, someone,
Travis

[Jesse Houwing]

* je**********@gm ail.com wrote, On 30-7-2007 17:36:

You are still not understanding my question. I am not trying to call a
stored procedure, not in the typical PL/SQL sense. Okay, it is
possible to upload a .NET assembly to an Oracle database. In doing so,
the database engine can execute the .NET assembly under a context. In
a sense, you are executing a .NET assembly just like you would any
other PL/SQL stored procedure.

This is not PL/SQL. I am not trying to get a ref cursor. I am trying
to get the results of a select command from a ".NET stored procedure".
Check out this link if you have no clue what I am talking about:
http://www.oracle.com/technology/pub...liams_sps.html.

If you take the time to look at the link, you will know exactly what I
am talking about. All the example links I have been shown have been
for working with ADO .NET or writing PL/SQL. I just want to get a
result set from a .NET stored procedure. And, yes, it *is*, really,
truly, a .NET stored procedure.

Sorry about that.

I don't have any experience with that.

Jesse

[Eric]

I haven't done this myself because the Oracle server must be running
on a Windows platform. Most Oracle servers actually run on some flavor
of unix/linux, so you can't put run an assembly from the DB in that
case. Also, I've heard this is slow because Oracle didn't re-engineer
the back-end server to make it more efficient at running CLR code as
Microsoft did with SQL Server. I don't mean this as a slam against
Oracle, I'm just pointing out that it's not as efficient as you may
like.

Maybe the ADO.NET forum would be a good place to ask for help, or else
the Oracle ODP forum (are you using the ODP provider?):
http://www.oracle.com/technology/sof...net/index.html

Eric

[Eric]

On Jul 30, 5:52 pm, Eric <englere_...@ya hoo.comwrote:

Maybe the ADO.NET forum would be a good place to ask for help, or else
the Oracle ODP forum (are you using the ODP provider?):http://www.oracle.com/technology/sof...net/index.html

I got my bookmarks mixed up, the ODP forum is here:
http://forums.oracle.com/forums/foru...ID=146&start=0

Eric

[Mr. Arnold]

<je**********@g mail.comwrote in message
news:11******** *************@m 37g2000prh.goog legroups.com...

You are still not understanding my question. I am not trying to call a
stored procedure, not in the typical PL/SQL sense. Okay, it is
possible to upload a .NET assembly to an Oracle database. In doing so,
the database engine can execute the .NET assembly under a context. In
a sense, you are executing a .NET assembly just like you would any
other PL/SQL stored procedure.

This is not PL/SQL. I am not trying to get a ref cursor. I am trying
to get the results of a select command from a ".NET stored procedure".
Check out this link if you have no clue what I am talking about:
http://www.oracle.com/technology/pub...liams_sps.html.

If you take the time to look at the link, you will know exactly what I
am talking about. All the example links I have been shown have been
for working with ADO .NET or writing PL/SQL. I just want to get a
result set from a .NET stored procedure. And, yes, it *is*, really,
truly, a .NET stored procedure.

So? Even in the example you are providing, it's returning a result set. I
don't care if it's MS SQL in-line code or Oracle in-line code, MS SQL
Server, Oracle Proc, or this so called .NET Stored Procedure using a Select
statement, they are all returning a RESULT SET whether it be one row or
multiple rows.

You put the RDR into a loop until it hits EOF and read the data in the
result set, row by row.

CountryName = rdr.GetString(0 ); // in the example is CountryName field on
the row.

Or CountryName = rdr.GetString(" CountryName"); // or something like that.

In either of above the cases, you must use a DATAREADER to read the rows and
address the fields on the rows of a returned result set.

[jehugaleahsa]

There is no question about how one actually reads the data with an
OracleDataReade r, Mr. Arnold.

The question is how one retrieves a result set from a (not so-
called) .NET Stored Procedure. As of now, even the ODP.NET forum has
not returned an answer.

As far as I am concerned, it should be a rather simple question.
However, it seems the majority of the developer community is unaware
of .NET Stored Procedures.

I am completely disinterested in how to process the results. I am more
interested on how one returns the results from a .NET Stored
Procedure. I really can't make the question any more plain.

The link I provided above explains which facilities I am attempting to
use. The question would be a rather a simple one if you had experience
in the topic.

I prefer that you not guess what I am asking. If you don't know, don't
reply. I am looking for help from someone with experience in .NET
Stored Procedures.

I am hoping that the ODP .NET forum can be more helpful.

Thank you,
Travis

[Mr. Arnold]

>

I prefer that you not guess what I am asking. If you don't know, don't
reply. I am looking for help from someone with experience in .NET
Stored Procedures.

My God, it doesn't take a rocket scientist to figure this out that a result
set is returned automatically out of the Stored Procedure, and is accessible
via datareader.

http://download.oracle.com/docs/cd/B.../extenBoth.htm

I don't know how you can make up some stuff that's not there, and you are
missing the boat. If there was something else other than what you're seeing
about how that result set is being returned, then you should have found it
log ago. In that one example that is in that link above, the result set was
one row with one field in the row of the result set.

It could have been like this too.

SELECT DEPTNO FROM EMP WHERE EMPNO :0";

Then a whole bunch of rows would have been retuned in the result set with
each row in the result set having a DeptNo.

I don't know where you think that returned result set is going to be, other
than, dead in your face in your example and the example above that has been
provided to you.

OracleDataReade r rdr = cmd.ExecuteRead er();

The retuned result set is in the RDR. Now, if you need to go beyond that,
then dump the rows of the result set into an arraylist or strong typed
collection and return either one of them, by using the datareader.

Sometimes, one has to think outside the box. :)

I am going to give up on you now has your head is 10 bricks hard.