passing a datatable as a field from Oracle to .NET

Question

In a .NET Datatable, the columns are Object types, which can include a datatable as a valid type for a column. So you can create a fairly complex structure:

CompanyID (Integer) | CompanyName (String) | OrderRecords (DataTable)
---------------------------------------------------------------------------
1                   | Acme Corp.           | DataTable of Orders

When calling an Oracle stored procedure, is there any way to return such a structure. I tried using the inline views, but it wouldn't let me. Example:

refCursor IS ref CURSOR;

PROCEDURE GETCOMPANYLIST (
  CompanyCursor OUT refCursor
)
AS
BEGIN
Open CompanyCursor For
SELECT COMPANYID, COMPANYNAME, 
    (SELECT * FROM ORDERS WHERE CompanyID = CompanyID) OrderRecords
    WHERE IsActive = 'T';
END GETCOMPANYLIST;

This doesn't work, but is there any way to do what I am trying to do here? Currently, I have to get back the List of Companies in one call to the database, then loop through all the records and make individual calls to get each list of Orders.

[Added valid answer - until now there was no answer actually answering the question]

Answer 1

While the type is Object as you say, .Net does try to use more strongly-typed values. The columns have an associated DataType property that only supports a smaller set of Types. See this link for the full list:
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype.aspx

Since a datatable is xml under the hood you probably could build a table like that, but .Net won't pass it to a database. Instead, you should look into using a DataSet. A DataSet is just a collection of DataTables, but it also supports a schema that can map relationships like you need.

Answer 2

I needed to do something similar once, and ended up returning them all as OUT REF CURSOR's, one for each result set. This could be loaded into a DataSet if preferred.

Answer 3

I asked this question a relatively long time ago (a couple months ago), then when I didn't get a response that worked for me I went back to sending multiple data tables and "reassembling" them on the application side the way it needed to be handled. However, I found an answer to this question and had to share it with the community here. Oracle's support for XML since version 9 is absolutely incredible and in particular the DBMS_XMLQuery package can be used to solve the problem I listed above by parsing and sending the select statements back as an XML dataset that can be easily converted on the Application side by .NET effortlessly. Here is the example code from a test stored proc I recently wrote:

SELECT COMPANYID, COMPANYNAME, 
   DBMS_XMLQuery.GetXML(
   'SELECT * FROM ORDERS WHERE CompanyID = ' 
   ||  COMPANY.COMPANYID )  
    OrderRecords
   FROM COMPANY
   WHERE lower(COMPANYNAME) LIKE '%shawn%';

This will Return something like the following (Just pull OrderRecords in as a string and covert to DataTable:

CompanyID | CompanyName      | OrderRecords 
---------------------------------------------------------------------------
1051      | Shawns Company  | XML String Returned, listed below

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <ORDERID>500020</ORDERID>
      <NOTES>Test Note</NOTES>
      <!-- Additional fields cut out  Not necessary for example -->
   </ROW>
   <ROW num="2">
      <ORDERID>500021</ORDERID>
      <NOTES>Test Note 2</NOTES>
      <!-- Additional fields cut out  Not necessary for example -->
   </ROW>
   <ROW num="3">
      <ORDERID>500280</ORDERID>
      <NOTES>notes test</NOTES>
      <!-- Additional fields cut out  Not necessary for example -->
   </ROW>
   <!-- Additional Rows cut out  Not necessary for example -->
</ROWSET>

There is a lot more to the DBMS_XMLQuery package besides this one very cool function. It is definitely worth checking out.