Does anyone have a good implementation or method for loading children of root objects in Oracle that are from ad hoc queries and not SPs?
Oracle does not allow the multiple SELECT calls delimited by ";" like SqlServer and thus having the parent make one call (unless you use SPs) to return multiple result sets isn't possible. How are those of you that are using Oracle making the child SELECTs for ad hoc queries?
Currently, we are making another call within FetchChildren to get the datareader to pass to the child object. This means 2 trips to the DB (one for parent and one for child) or more than that if multiple children exist for an object. Any better way of doing this?
Thanks,
Mike
I had to support SQL Server and Oracle for many years. Luckily we recently got to drop Oracle support! Yea!!
I coded BOs so that the Root fetched itself in one query and then I had the root call a method *I wrote* to FetchChildren. In that method I define a datareader and fill it with all the child data for a given child, pass it to that child and repeat the process for each child off the root. I just checked one BO and it has 5 child collections and a child BO - all are filled from the root FetchChildren method.
So this technique "goes to the database once per child table".
Joe
That's how ours are. FetchChildren loads up the child info and passes to children. We make one query per child collection, not per child (unless it's a singular). I guess that's about the only way to do it with Oracle. Thanks.
Are you using ODP.NET or just the regular Microsoft system.data.oracle?
If you are using ODP then you can pass a refCursor in/out of sql statement created on the fly. Here is the basic example: http://www.oracle.com/technology/oramag/oracle/06-jan/o16odpnet.html
You can alter this example to have multiple cursors, one for each child collection.
Hope that helps.
jack
Copyright (c) Marimer LLC