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 EnterpriseLibrary.Data block as well.

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


<je**********@gmail.comwrote in message
news:11*********************@x40g2000prg.googlegro ups.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**********@gmail.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**********@gmail.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 <[e-mail]:

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**********@gmail.comwrote in message
news:11*********************@m37g2000prh.googlegro ups.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
OracleDataReader, 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.

OracleDataReader rdr = cmd.ExecuteReader();

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.

[G.Doten]

Mr. Arnold wrote:

>>
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.

OracleDataReader rdr = cmd.ExecuteReader();

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.

Keep in mind that the GetDeptNo code at that URL is running within
Oracle itself. The C# stored procedure GetDeptNo returns only a uint; it
doesn't return a result set. The uint that is returned from the C#
stored procedure is analagous to a conventional stored procedure having
a "unit output" parameter.

So the client (presumably data access layer) code that calls the
GetDeptNo stored procedure from a regular old assembly has no access to
the DataReader or any of the other database objects that the GetDeptNo
C# stored procedure uses to get its work done.

You actually write these C# stored procedures almost identically as you
would from regular C# code, which is pretty cool in terms of its geek
factor. The only difference is that the assembly housing the C# stored
procedure is fired up by Oracle itself and executed. I've had a number
of debates with colleagues on the usefulness of C# stored procedures and
our concensus is that this ability is really marketing by Microsoft. My
contacts tell me the SQL Server people didn't want this tight an
integration into the CLR, but were essentially forced to implement it by
management. It is apparently quite a bit slower than a conventional
stored procedure that does a SELECT. But don't take my word on that last
bit, I've not tested this myself and I'm just quoting heresay.

In these debates I've had, the best use of the SQL Server (and Oracle)
embracing the CLR is that you can define your own C# struct to be an
actual database type. I can see tons of uses for that that plain old
UDTs just can't come close to (like share the type, with all its bells
and whistles, in data access code instead of having to use a SqlInt type
or whatever).

HTH

--
-glenn-

[jehugaleahsa]

On Jul 31, 6:34 am, "G.Doten" <[e-mail]:

Mr. Arnold wrote:

<[e-mail] in message
news:11*********************@m37g2000prh.googlegro ups.com...

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

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 ".NETstoredprocedure".
Check out this link if you have no clue what I am talking about:
http://www.oracle.com/technology/pub..._dotnet_oracle....

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 .NETor writing PL/SQL. I just want to get a
result set from a .NETstoredprocedure. And, yes, it *is*, really,
truly, a .NETstoredprocedure.

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 .NETStoredProcedureusing 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.

I don't think that the sample pointed to above on the Oracle site is
returning a result set. It looks to me like the GetCountryNamestoredprocedureis returning a one column row called CountryName. And it
doesn't return it in a conventional SQL result set or whatever. To call
the example from a regular C# method (a data layer method, say) it would
be something like this:

string countryName = StoredProcedures.GetCountryName(countryId);

jehugaleahsa, to return a result set from a C#storedprocedurelike the
GetCountryName one have you tried the contect object to return the reader?

public class StoredProcedures
public static void ReturnDataReader()
{
using (OracleConnection conn = new OracleConnection(...))
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT Foo FROM Table1";
OracleDataReader reader = new OracleDataReader(CommandBehavior.Close);
SqlContext.Pipe.Send(reader);

}
}
}

I will look into this. It would be very nice if you found the answer.
Thank you for you time and effort.

Mr. Arnold, I keep reading that same example. It only returns a single
value. The problem is that when I attempt to return multiple rows,
Oracle no longer considers the method a valid .NET Stored Procedure. I
am not trying to be rude with you; it is just I don't think you are
understanding my question. I appreciate the time you are putting into
my question; however, I don't think we are understanding each other. I
am sorry if I am irritating you.

Thanks,
Travis

[Mr. Arnold]

So you're telling me that you cannot return an Arraylist of objects that
were derived from a row of the result set in another example that was in
that example link that I modified?

I can't run this myself, because I don't have Oracle on this machine. But I
don't see why you can't do it to return the results of the result set or
something close to it.

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

struct MyStruct
{
public int32 deptno;
}

public class CLRLibrary1
{
// .NET Stored Function returning the DEPTNO of the employee whose
// EMPNO is 'empno'
public static System.Arraylist GetDeptNos(uint empno)
{
System.Arraylist deptnos = new System.Arraylist();

// Check for context connection
OracleConnection conn = new OracleConnection();
if( OracleConnection.IsAvailable == true )
{
conn.ConnectionString = "context connection=true";
}
else
{
//set connection string for a normal client connection
conn.ConnectionString = "user id=scott;password=tiger;" +
"data source=oracle";
}

conn.Open();
// Create and execute a command
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT DEPTNO FROM EMP WHERE EMPNO :0";
cmd.Parameters.Add(":0",OracleDbType.Int32,empno,
System.Data.ParameterDirection.Input);
OracleDataReader rdr = cmd.ExecuteReader();
while (reader.Read())
{
MyStruct ms = new MyStruct();
ms.deptno = (uint)rdr.GetInt32(0);
deptnos.add(ms)
}

rdr.Close();
cmd.Dispose();
conn.Close();
return deptnos;
} // GetDeptNos
} // CLRLibrary1

[Mr. Arnold]

<snipped>

Personally, I am not sure if .NET Stored Procedures buy me anything,
over a slightly easier language to create them in.

It's worthless snake-oil. And myself, I would be bothered with it due to its
lack of flexibilities.

The only thing I would possibly use it for is to come back with Oracle
Date/Time or Next SEQ Number for a table's primacy key using DUAL, if that.

[G.Doten]

Mr. Arnold wrote:

>

>Personally, I am not sure if .NET Stored Procedures buy me anything,
over a slightly easier language to create them in.

It's worthless snake-oil. And myself, I would be bothered with it due to
its lack of flexibilities.

I mostly agree. Except that I really like being able to use a create a
struct than I can use code at the data access layer as well as in a
conventional stored procedure (i.e., regular old table). That's pretty
damn cool.

--
-glenn-