Parent-child records and Oracle 9i

[Guest]

Hi

Is there an ADO.NET recommended way for returning parent-child data from
Oracle 9i stored procedures? As an example, I need to return Customer and
Account data. A Customer (the parent) has many Accounts (child).

There may be cases where I would need to return grand child and great grand
child data too.

Obviously, returning hierarchical XML would suite this problem nicely,
however, the XML support in Oracle 9i is terrible.

Thanks
Bikhod

 

[John Saunders]

Hi

Is there an ADO.NET recommended way for returning parent-child data from
Oracle 9i stored procedures? As an example, I need to return Customer and
Account data. A Customer (the parent) has many Accounts (child).

Surely this depends on what you plan to do with the data, but in SQL Server
I might do the following:

CREATE PROCEDURE CustomerAndAccounts
@CustomerID int AS
BEGIN
SELECT * FROM Customers WHERE ID = @CustomerID
SELECT * FROM Accounts WHERE CustomerID = @CustomerID
END

This would return two resultsets. If this procedure were the CommandText in
the SelectCommand for a DataAdapter and used in a Fill operation, it would
load two tables.

John Saunders

 

[David Browne]

Surely this depends on what you plan to do with the data, but in SQL
Server I might do the following:

CREATE PROCEDURE CustomerAndAccounts
@CustomerID int AS
BEGIN
SELECT * FROM Customers WHERE ID = @CustomerID
SELECT * FROM Accounts WHERE CustomerID = @CustomerID
END

This would return two resultsets. If this procedure were the CommandText
in the SelectCommand for a DataAdapter and used in a Fill operation, it
would load two tables.

In Oracle this procedure would look like this:


create or replace procedure CustomerAndAccounts(
in_CustomerID in customers.id%type,
out_customerRC out sys_refcursor,
out_accountsRC out sys_refcursor)

as
begin

open out_customerRC for
select * from customers where id = in_CustomerID;

open out_accountsRC for
select * from accounts where customer_id = in_CustomerID;

end;

 

[Guest]

John/David

Thank you both for your replies. This is what I had found too but I wasn't
sure if it is the recommended approach.

All I am trying to do is to retrieve a bunch of customers and their
corresponding accounts data, and preserve the hierarchical nature of the
data. Ideally, I would like to return the data in XML in the following format:

<CUSTOMERS>
<CUSTOMER>
<SURNAME/>
<DOB>
<ACCOUNTS>
<ACCOUNT>
<NAME/>
<BALANCE/>
</ACCOUNT>
</ACCOUNTS>
</CUSTOMER>

But Oracle 9i's support for returning XML seems very poor. Or am I wrong?

Thanks