Iterating Oracle collections of objects with out exploding them
- by Scott Bailey
I'm using Oracle object data types to represent a timespan or period. And I've got to do a bunch of operations that involve working with collections of periods. Iterating over collections in SQL is significantly faster than in PL/SQL.
CREATE TYPE PERIOD AS OBJECT (
beginning DATE,
ending DATE,
... some member functions...);
CREATE TYPE PERIOD_TABLE AS TABLE OF PERIOD;
-- sample usage
SELECT <<period object>>.contains(period2)
FROM TABLE(period_table1) t
The problem is that the TABLE() function explodes the objects into scalar values, and I really need the objects instead. I could use the scalar values to recreate the objects but this would incur the overhead of re-instantiating the objects. And the period is designed to be subclassed so there would be additional difficulty trying to figure out what to initialize it as.
Is there another way to do this that doesn't destroy my objects?
