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/articles/mastering_dotnet_oracle/williams_refcursors.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.

 

[jehugaleahsa]

See this example.http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle...

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.









- Show quoted text -

No. I am already familiar with PL/SQL and ADO .NET. The problem is
that I am now working with .NET stored procedures. It is a way to run
C# code on the database, as opposed to a separate machine.

I am trying to figure out how to return the results of a select
statement from one of these .NET stored procedures. It is easy enough
returning a string or some primitive, but I can't figure out what I
have to do to return a result set.

Can someone who knows what I am talking about please help me.

Thanks,
Travis

 

[Mr. Arnold]

No. I am already familiar with PL/SQL and ADO .NET. The problem is
that I am now working with .NET stored procedures. It is a way to run
C# code on the database, as opposed to a separate machine.

I am trying to figure out how to return the results of a select
statement from one of these .NET stored procedures. It is easy enough
returning a string or some primitive, but I can't figure out what I
have to do to return a result set.

Can someone who knows what I am talking about please help me.

It's not a .Net Stored Procedure. It's an Oracle Stored Procedure for lack
of better words, like MS SQL Server has Stored Procedures.

The Oracle namespace has a datareader just like the MS SQL Server namespace
has a datareader. You use the datareader to read the result set from an
Oracle Stored Procedure.

The link shows SQL Server, but Oracle is about the same, which you should be
able to find examples of using the Oracle datareader out there on Google.

http://www.akadia.com/services/dotnet_data_reader.html

 

[Jesse Houwing]

* (e-mail address removed) 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.aspx?scid=kb;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/articles/mastering_dotnet_oracle/williams_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]

* (e-mail address removed) 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/articles/mastering_dotnet_oracle/williams_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/software/tech/windows/odpnet/index.html

Eric

 

[Eric]

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/software/tech/windows/odpnet/index.html

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

Eric

 

[Mr. Arnold]

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/articles/mastering_dotnet_oracle/williams_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/B19306_01/win.102/b14307/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]

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.

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 GetCountryName stored
procedure is 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# stored procedure like 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);
}
}
}

(The above is typed in from memory.) The key is the SqlContext and it's
Pipe method. While I haven't tried this, I think that it causes the C#
stored procedure to return the Oracle result set to the caller, just
like when you do the same SELECT statement from a PL/SQL stored procedure.

I used SqlContext above because I don't want to bother registering at
the Oracle site to download their Oracle Database Extensions for .NET in
order to get at the presumably extant OracleContext class.

This is interesting: I had no idea that Oracle had integrated with the
CLR as SQL Server did!

I'd be very interested in knowing if the above is correct or not.

 

[G.Doten]

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/B19306_01/win.102/b14307/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

 

[jehugaleahsa]

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

 

[jehugaleahsa]

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?

That is right. .NET Stored Procedures only support returning database-
mapped types. You can certainly write a method *inside* the stored
procedure that returns an ArrayList. But, in order for a method to be
a .NET Stored Procedure that can be called like any other stored
procedure, you have to provide a static method with a return type that
is supported.

If your method does not have a valid return type and parameters, the
method will be overlooked. All attempts to put something that would
return a result set have failed, including List<T>, DataTable,
OracleRefCursor, etc, etc.

Complex types like an ArrayList are not supported. I have been trying
to find an example out there somewhere. It would seem .NET Stored
Procedures are limited to returning primitive types.

It leaves me with two options: implement a PL/SQL procedure to get my
data (which is easy enough); or put more code into the stored
procedure so it doesn't have to return anything.

It would seem .NET Stored Procedures are a fairly new topic. Even on
the Oracle .NET forums I was getting responses where people confused
".NET stored procedure" with "how to call a stored procedure in .NET".
However, I know that MS SQL has been supporting a similar technology
for years. I don't think most people use them for performance/security/
education/sanity reasons.

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.

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

Thanks for you time,
Travis

 

[Mr. Arnold]

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]

That is right. .NET Stored Procedures only support returning database-
mapped types. You can certainly write a method *inside* the stored
procedure that returns an ArrayList. But, in order for a method to be
a .NET Stored Procedure that can be called like any other stored
procedure, you have to provide a static method with a return type that
is supported.

And C# stored procedures aren't limited to just certain supported return
types. They are highly limited as to what they can call out to. An
interesting way to see this is if you have VS8 create a database project
(SQL Server is fine if you don't have Oracle). Then right click the
project's references node and look at the list of .NET assemblies. There
are very few you can add to the project, meaning that C# procs are
highly limited in their functionality.

I imagine this is mostly for security concerns, and maybe people
attempting to write full-fledged apps within a C# proc. I actually was
going to try that one day. "How do I run this new app?" "Well, go into
osql and type 'exec a_sql_rtl_app'!"

It leaves me with two options: implement a PL/SQL procedure to get my
data (which is easy enough); or put more code into the stored
procedure so it doesn't have to return anything.

Are there no other uses of SqlPipe (OraclePipe) that might allow you to
get the data back that you need? I've not really used that class so
don't know it's full capabilities.

 

[G.Doten]

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.