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

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

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.

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" <gdo...@gmail.c omwrote:

Mr. Arnold wrote:

<jehugalea...@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
storedprocedur e, 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/SQLstoredproced ure.

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 ".NETstoredproc edure".
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 .NETstoredproce dure. And, yes, it *is*, really,
truly, a .NETstoredproce dure.

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 .NETStoredProce dureusing 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 GetCountryNames toredprocedurei s 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 = StoredProcedure s.GetCountryNam e(countryId);

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

public class StoredProcedure s
public static void ReturnDataReade r()
{
using (OracleConnecti on conn = new OracleConnectio n(...))
{
conn.Open();
OracleCommand cmd = conn.CreateComm and();
cmd.CommandText = "SELECT Foo FROM Table1";
OracleDataReade r reader = new OracleDataReade r(CommandBehavi or.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.DataAcce ss.Client;
using Oracle.DataAcce ss.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.Arraylis t GetDeptNos(uint empno)
{
System.Arraylis t deptnos = new System.Arraylis t();

// Check for context connection
OracleConnectio n conn = new OracleConnectio n();
if( OracleConnectio n.IsAvailable == true )
{
conn.Connection String = "context connection=true ";
}
else
{
//set connection string for a normal client connection
conn.Connection String = "user id=scott;passwo rd=tiger;" +
"data source=oracle";
}

conn.Open();
// Create and execute a command
OracleCommand cmd = conn.CreateComm and();
cmd.CommandText = "SELECT DEPTNO FROM EMP WHERE EMPNO :0";
cmd.Parameters. Add(":0",Oracle DbType.Int32,em pno,
System.Data.Par ameterDirection .Input);
OracleDataReade r rdr = cmd.ExecuteRead er();
while (reader.Read())
{
MyStruct ms = new MyStruct();
ms.deptno = (uint)rdr.GetIn t32(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-